测试派 Give testers a home.

#6 查询出语文最高分

子查询

select max(score) from sc where Cno=(select Cno from course where Cname='语文');

联表查询

select max(score) from sc join course c using (Cno) where Cname='语文';

#7 查询没有参与语文考试的学生信息

子查询

select * from student where Sno not in (select distinct Sno from sc where Cno=(select Cno from course where Cname='语文'));

联表查询

select * from student where Sno not in (select Sno from student join sc using (Sno) join course c using (Cno) where Cname='语文');

#8 查询语数外三门成绩的平均分
select Cname, avg(score) from sc join course c using (Cno) group by Cno;

#9 查询报名孤独求败老师课程的学生信息

方法一：子查询

select * from student where Sno in (select Sno from sc where Cno in (select Cno from course where Tno in (select Tno from teacher where Tname='孤独求败')));

方法二：联表查询

select * from student where Sno in (select Sno from course join sc using (Cno) join teacher using (Tno) where Tname='孤独求败');

#10 没有报名孤独求败老师课程的学生信息

方法一：子查询

select * from student where Sno not in (select Sno from sc where Cno in (select Cno from course where Tno in (select Tno from teacher where Tname='孤独求败')));

方法二：联表查询

select * from student where Sno not in (select Sno from course join sc using (Cno) join teacher using (Tno) where Tname='孤独求败');

2 查询无崖子授课信息

select * from course c join teacher t using (Tno) where t.Tname='无崖子';
如果两张表合并后不想要重复的列，可以用using去去重的吧。