（头哥）多表查询与子查询

目录

第1关：查询每个学生的选修的课程信息

第2关：查询选修了“数据结构”课程的学生名单

第3关：查询“数据结构”课程的学生成绩单

第4关：查询每门课程的选课人数

第5关：查询没有选课的学生信息

第6关：查询学生所学课程平均分超过80分的学生信息

第7关：找出每个学生超过他所有的选修课程的平均成绩的课程

第8关：查询“程序设计基础”课程成绩最高的学生信息

第9关：查询每个同学的总学分

---

第1关：查询每个学生的选修的课程信息

任务描述：

本关任务：查询每个学生的选修的课程信息，显示sno、sn、cn、ct，并按ct降序排列

分析：sno、sn在学生表（s）中，cn、ct在课程表（c）中，因此我们要将这两张表连接起来，但由于这两张表无直接联系，因此我们需要通过选修表（sc）将s表和c表连接起来。我们可以使用内连接，将这三张表（s、sc、c）连接起来，s与sc的连接条件为：s.sno=sc.sno，sc与c的连接条件为：sc.cno=c.cno

内连接：

select 字段1, 字段2,... from 表1 inner join 表2 on 连接条件;

select 字段1, 字段2,... from 表1, 表2 where 连接条件;

题解： 

select s.sno, s.sn, c.cn, c.ct from s 
 inner join sc on s.sno = sc.sno 
 inner join c on sc.cno = c.cno 
 order by c.ct desc;

第2关：查询选修了“数据结构”课程的学生名单

本关任务：查询选修了“数据结构”课程的学生名单，显示sno、sn，按学号升序排序

分析：sno、sn在学生表（s）中，题目要求查询选修了“数据结构”的学生名单，因此我们要查询楚“数据结构”的课程号（使用子查询），再通过课程号找到选修了“数据结构”的学生学号（在sc表中查询选修课程为“数据结构”课程号的学生），从而查询出选修了“数据结构”课程的学生名单

题解：

 select s.sno,s.sn from s inner join sc on s.sno = sc.sno 
 where sc.cno = (select cno from c where cn = '数据结构') 
 order by s.sno;

第3关：查询“数据结构”课程的学生成绩单

本关任务：查询“数据结构”课程的学生成绩单，显示sno、sn、score，按成绩降序排列

分析：本关与第2关类似，先查询出“数据结构”的课程号，即可通过课程号查询出学生的学号、分数、姓名

题解：

 select s.sno, s.sn, sc.score from s inner join sc on s.sno=sc.sno 
 where sc.cno = (select cno from c where cn = '数据结构') 
 order by sc.score desc;

第4关：查询每门课程的选课人数

本关任务：查询每门课程的选课人数，以中文显示课程号、课程名称、选课人数，没有学生选的课程也要显示，按课程号升序排列。

分析：统计选课人数，则需要使用聚合函数(count())，而任务要求查询每门课程的选课人数，则需要使用分组查询，分组的依据为课程号，且题目还要求没有学生选的课程也要显示，因此我们不能使用内连接来查询，而要使用外连接

外连接：

左外连接：select 字段 from 表1 left outer join 表2 on 连接条件;

右外连接：select 字段 from 表1 right outer join 表2 on 连接条件;

题解：

select c.cno as '课程号',c.cn as '课程名称',count(s.sno) as '选课人数' 
from c left outer join sc on c.cno=sc.cno 
left outer join s on s.sno=sc.sno 
group by c.cno;

第5关：查询没有选课的学生信息

本关任务：查询没有选课的学生信息，显示sno、sn，按学号升序排列

分析：若学生没有选课，则选修表（sc）中cno为空或是sc中无该学生选课信息，因此，我们可以使用左外连接查询出这些没有选课的学生

题解：

select s.sno,s.sn from s left outer join sc on s.sno=sc.sno 
 where sc.cno is null; 

第6关：查询学生所学课程平均分超过80分的学生信息

本关任务：查询学生所学课程平均分超过80分的学生信息，以中文显示学号、姓名、平均成绩。按学号升序排列

分析：所学课程平均分需要使用聚合函数（avg()），而任务要求查询学生所学课程平均分超过80分的学生信息，因此需要使用分组查询，分组条件为学号，由于平均分在分组之后计算出，因此需要使用having 来查询平均分超过80分的学生

题解：

select s.sno as '学号', s.sn as '姓名', avg(sc.score) as '平均成绩' 
from s inner join sc on s.sno=sc.sno group by sc.sno 
having avg(sc.score)>=80 
order by s.sno;

第7关：找出每个学生超过他所有的选修课程的平均成绩的课程

本关任务：找出每个学生超过他所有的选修课程的平均成绩的课程，显示sno、sn、cn、score，按sno和cn升序排列

分析：要找出每个学生超过他所有的选修课程的平均成绩的课程，我们首先要求出学生所选修课程的平均成绩，再找出其超出平均成绩的课程，因此我们可以使用自连接来查询学生超过他所有的选修课程的平均成绩的课程，再通过课程号查询该学生其他信息

题解：

select s.sno,s.sn,c.cn,sc.score 
from sc inner join s on sc.sno=s.sno 
inner join c on sc.cno=c.cno 
where sc.score >= (select avg(score) from sc as b where b.sno=sc.sno) order by s.sno,c.cn;

第8关：查询“程序设计基础”课程成绩最高的学生信息

本关任务：查询“程序设计基础”课程成绩最高的学生信息，显示sno、sn、score

分析：我们首先要找到“程序设计基础”课程中成绩最高的学生学号，使用聚合函数（max()）找出选修课程为“程序设计基础”中成绩最高的学号，再查询学生信息

题解：

 select s.sno,s.sn,sc.score from s 
 inner join sc on s.sno=sc.sno 
 where sc.score = (select max(score) from sc inner join c on sc.cno=c.cno where c.cn='程序设计基础');

第9关：查询每个同学的总学分

本关任务：查询每个同学的总学分，成绩大于等于60才能拿到学分，以中文显示学号、姓名、总学分，以学号升序排列

分析：

要查询每个同学的总学分，其中也可能会有学分为0的情况，因此我们要使用外连接，由于只有成绩大于等于60才能拿到学分，因此我们在查询总学分时，要使用子查询，使用聚合函数（sum()），并通过条件 sc.score>=60查出总学分，由于要查询每个同学的总学分，因此需要使用分组查询，分组条件为学号

题解：

 select s.sno as '学号', s.sn as '姓名', 
  (select sum(c.credit) from sc inner join c on sc.cno=c.cno 
  where sc.score>=60 and sc.sno=s.sno) as '总学分' 
 from s left outer join sc on s.sno=sc.sno 
 left outer join c on sc.cno=c.cno 
 group by s.sno;