学生信息表S(sno,sname)
课程信息表C(cno,cname)
选课信息表SC(sno,cno,grade)
1、列出既学过‘math’,又学过‘english’的所有学生学号、姓名
Select S.sno,S.sname from s,
(select SC.sno from SC,C where SC.cno=C.cno and C.cname in(‘math’,’english’) group by sno having count(DISTINCT C.cno)=2)X
where S.sno=X.sno;
2、列出有两门以上含两门不及格课程的学生学号、姓名和平均成绩
Select S.sno,S.sname,avg(SC.grade) as avggrade from S,SC,
(select sno from SC where grade<60 group by sno having count(DISTINCT cno)>=2)X
where S.sno=X.sno and SC.sno=X.sno group by S.sno;
3、列出‘math’课程成绩比‘english’课程成绩高的所有学生的姓名
Select S.sname from S,
(Select sno,grade from SC where cno in(Select cno from c where cname=’math’))A,
(Select sno,grade from SC where cno in(Select cno from c where cname=’english’))B,
where S.sno=A.sno and S.sno=B.sno and A.grade>B.grade;