查询选修了王平老师所讲授所有课程的学生的学号和成绩

有选课表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 = '王平' 
    )
)