练习26-30：多表关联查询，子查询

四张表信息

26. 查询平均成绩大于等于85的所有学生的学号，姓名和平均成绩

第一步：锁定表为学生表和成绩表
第二步：查出平均成绩大于等于85的学生

 SELECT 
 sid,
 AVG(score) AS avg_score
 FROM sc
 GROUP BY sid
 HAVING avg_score >= 85;
1
2
3
4
5
6

第三步：关联学生表，获取到学生的学号和姓名，然后从结果中选出想要的字段

 SELECT
 a.sid,b.`Sname`,a.avg_score
 FROM(
 SELECT 
 sid,
 AVG(score) AS avg_score
 FROM sc
 GROUP BY sid
 HAVING avg_score >= 85) a
 LEFT JOIN student b
 ON a.sid = b.sid;
1
2
3
4
5
6
7
8
9
10
11

27. 查询课程名称为【数学】，且分数低于60的学生姓名和分数

第一步：锁定表，学生表、成绩表、课程表
第二步：从课程表查出【数学】的课程编号

 SELECT * FROM course WHERE course.`Cname` = '数学';
1

第三步：关联成绩表，查询【数学】分数低于60分的学生

SELECT
 *
 FROM (SELECT * FROM course WHERE course.`Cname` = '数学' ) a
JOIN sc b ON a.cid = b.cid;
1
2
3
4

第四步：关联学生表，获取学生的姓名，并过滤掉成绩低于60分的

SELECT
 *
 FROM (SELECT * FROM course WHERE course.`Cname` = '数学' ) a
JOIN sc b ON a.cid = b.cid
JOIN student c ON b.sid = c.sid
WHERE b.`score` < 60;
1
2
3
4
5
6

第五步：筛选出需要的字段

SELECT
 c.sid,
 c.sname,
 a.cname,
 b.score
 FROM (SELECT * FROM course WHERE course.`Cname` = '数学' ) a
JOIN sc b ON a.cid = b.cid
JOIN student c ON b.sid = c.sid
WHERE b.`score` < 60;
1
2
3
4
5
6
7
8
9

28. 查询所有学生的课程及分数情况（存在学生没有成绩，没选课的情况）

第一步：锁定表，学生表，成绩表
第二步：学生表左连接成绩表（因为学生表中的所有记录都需要出现在结果中）

 SELECT 
 a.*,
 b.score
 FROM student a
 LEFT JOIN sc b
 ON a.sid = b.sid;
1
2
3
4
5
6

29. 查询课程成绩在70分以上的姓名、课程名称和分数

第一步：锁定表，成绩表、学生表、课程表
第二步：从成绩表筛选出所有成绩大于70的记录

 SELECT * FROM sc WHERE score > 70;
1

第三步：关联学生表，获取学生的姓名，关联课程表，获取课程的名称，然后选出想要的字段

 SELECT 
 b.sname,
 c.cname,
 a.score
 FROM (SELECT * FROM sc WHERE score > 70) a
 JOIN student b
 ON a.sid = b.sid
 JOIN course c
 ON a.cid = c.cid;
1
2
3
4
5
6
7
8
9

30. 查询存在不及格的课程

第一步：从成绩表中过滤出哪些记录的分数小于60分，然后去重获取到cid

 SELECT 
 DISTINCT cid
 FROM sc
 WHERE score < 60;
1
2
3
4

第二步：从课程表获取课程信息

SELECT
 * 
 FROM course 
 WHERE cid IN (SELECT DISTINCT cid FROM sc WHERE score < 60);
 
1
2
3
4
5