--第二题2. 每个班共有多少个学员
select count(c.classname) cnum,c.classname from classes c
inner join students stu on c.classid = stu.classid
group by c.classname
union
select 0 cnum,a.classname from
(select stu.stuname,c.classname from classes c
left join students stu on c.classid = stu.classid
where stu.stuname is null) a
--3. 查询学员的全部信息，包括学生所在班级名称，
--专业名称， 并按学员ID排序
select stu.stuid,stu.stuname,c.classname,m.majorname
from students stu
left join classes c on stu.classid = c.classid
left join major m on stu.majorid = m.majorid
order by stu.stuid
--4. 查询学员姓名中包含‘小’字的学员信息
select * from students where stuname like '%女%'
--5. 查询出生日期在1988年到1990年之间的学员信息
select * from students where birthday
between to_date('2000-01-01','yyyy-mm-dd')
and to_date('2002-12-30','yyyy-mm-dd')
--7. 查询参加课程’Java程序设计与训练’考试的学员学号和姓名
select stu.stuname,co.courename,sc.score
from course co
inner join stucourse sc
on co.courseid = sc.courseid
inner join students stu on sc.stuid = stu.stuid
AND co.courename = 'JAVASE'
select stu.stuname,co.courename,sc.score
from course co
inner join stucourse sc
on co.courseid = sc.courseid AND co.courename = 'JAVASE'
inner join students stu on sc.stuid = stu.stuid
select stu.stuname,co.courename,sc.score
from course co
inner join stucourse sc
on co.courseid = sc.courseid
inner join students stu on sc.stuid = stu.stuid
WHERE co.courename = 'JAVASE'
--8. 查询未参加某门课程考试的人员名单
select co.courename,sc.score,stu.stuname from course co
left join stucourse sc on co.courseid = sc.courseid
left join students stu on stu.stuid = sc.stuid
where co.courename = 'ORACLE' AND sc.score is null
--9. 查询未参加某门课程考试的人数
select count(co.courename),'ORACLE' COURSENAME from course co
left join stucourse sc on co.courseid = sc.courseid
left join students stu on stu.stuid = sc.stuid
where co.courename = 'ORACLE' AND sc.score is null
--10. 查询所有学员某一门成绩信息
select stu.stuname,co.courename,sc.score
from students stu
left join stucourse sc on stu.stuid = sc.stuid
left join course co on sc.courseid = co.courseid
where co.courename = 'JAVAEE'
--12. 查询某班某科学员的总成绩、平均成绩、大于80分的平均成绩
select sum(sc.score) 总成绩,avg(sc.score) 平均成绩,
from students stu
left join stucourse sc on stu.stuid = sc.stuid
left join course co on sc.courseid = co.courseid
left join classes cl on cl.classid = stu.classid
where co.courename = 'JAVAEE'
and cl.classname = '班级2'
select avg(sc.score) 大于80分的平均成绩
from students stu
left join stucourse sc on stu.stuid = sc.stuid
left join course co on sc.courseid = co.courseid
left join classes cl on cl.classid = stu.classid
where co.courename = 'JAVAEE'
and cl.classname = '班级2'
and sc.score > 80