有选课表sc ={sno学号,cno课程号,tno教师号,score 成绩}
教师表t = {tno教师号,tn姓名}
授课表tc = {tno教师号,cno课程号}
题目: 查询选修了王平老师所讲授所有课程的学生的学号和成绩
1)首先选出那些选修了王平老师课程的学生.
select sno from sc
inner join t on sc.tno=t.tno
where t.tn='王平'
2)选出王平老师教授的所有课程数
select count(tc.cno) from tc
inner join t on t.tno = tc.tno
where t.tn='王平'
3) 在(1)中那些选修了王平老师的课程的学生这个大范围中,找出选修了王平课程数=王平教授的所有课程数的那些学生的学号
select sno from sc
inner join t on sc.tno=t.tno
where t.tn='王平' group by sc.sno
having count(sc.cNo)=
(
select count(tc.cno) from tc
inner join c on c.cno = tc.cno
inner join t on t.tno = tc.tno
where t.tn = '王平'
)
4)在整个选课表sc中,只要sc.sno在(3)得到的范围里面,就查询他们的学号和成绩.
select sc.sno,sc.score from sc
where
(
select sno from sc
inner join t on sc.tno=t.tno
where t.tn='王平' group by sc.sno
having count(sc.cNo)=
(
select count(tc.cno) from tc
inner join c on c.cno = tc.cno
inner join t on t.tno = tc.tno
where t.tn = '王平'
)
)