pta mysql训练题集(321-340)

10-321 查询“谭浩强”教师任课的课程号，选修其课程的学生的学号和成绩，结果中包括该老师没有被选修的课程

select b.cno cno,c.sno sno,c.score
from teachers as a
left join teaching as b on b.tno=a.tno
left join sc as c on b.cno=c.cno 
where a.tname='谭浩强'

10-322 列出所有学生的选课情况（包括学号，姓名，课号，成绩），结果中包括没有选课的学生

select students.sno,sname,cno,score
from students
left join sc on students.sno = sc.sno;

10-323 查询没有选课的学生学号和姓名

select sno,sname
from students
where sno not in (select sno from sc)

10-324 查询信息学院学生所选修的课程号和成绩

select cno,score
from sc
where sno in
(
select sno
from students
where sdept = '信息学院'
)