前提是已在数据库mis中建过student,course,sc表并插入了一些数据。(在前面已介绍过)
(1)查询“cs”系男学的选课情况
select sc.* from sc,student
where sc.sno=student.sno
and sdept='cs' and ssex='男';
(2)查询选修了“数据库”课程的学生姓名
select sname from sc,course,student where sc.cno=course.cno and sc.sno=student.sno and cname='数据库' ;
(3)查询选修了“数据库”课程,并且成绩90分以上的学生人数、平均成绩
select count(sno),avg(grade) from sc,course where sc.cno=course.cno and cname='数据库' and grade>=90;
(4)统计各系选课的学生人数、课程门数
select sdept,count(distinct student.sno),count(distinct cno)
from sc,student where sc.sno=student.sno
group by sdept;
(5)统计各系男女生选课的学生人数、课程门数
select sdept,ssex,count(distinct student.sno),count(distinct cno)
from sc,student where sc.sno=student.sno
group by sdept,ssex;
(6)查询选修课超过二门的学生学号、姓名
select student.sno,sname,count(cno)
from sc,student where sc.sno=student.sno
group by student.sno,sname
having count(cno)>2;