练习26-30：多表关联查询，子查询
四张表信息
26. 查询平均成绩大于等于85的所有学生的学号，姓名和平均成绩
第一步：锁定表为学生表和成绩表
第二步：查出平均成绩大于等于85的学生
SELECT
sid,
AVG(score) AS avg_score
FROM sc
GROUP BY sid
HAVING avg_score >= 85;
第三步：关联学生表，获取到学生的学号和姓名，然后从结果中选出想要的字段
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;
27. 查询课程名称为【数学】，且分数低于60的学生姓名和分数
第一步：锁定表，学生表、成绩表、课程表
第二步：从课程表查出【数学】的课程编号
SELECT * FROM course WHERE course.`Cname` = '数学';
第三步：关联成绩表，查询【数学】分数低于60分的学生
SELECT
*
FROM (SELECT * FROM course WHERE course.`Cname` = '数学' ) a
JOIN sc b ON a.cid = b.cid;
第四步：关联学生表，获取学生的姓名，并过滤掉成绩低于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;
第五步：筛选出需要的字段
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;
28. 查询所有学生的课程及分数情况（存在学生没有成绩，没选课的情况）
第一步：锁定表，学生表，成绩表
第二步：学生表左连接成绩表（因为学生表中的所有记录都需要出现在结果中）
SELECT
a.*,
b.score
FROM student a
LEFT JOIN sc b
ON a.sid = b.sid;
29. 查询课程成绩在70分以上的姓名、课程名称和分数
第一步：锁定表，成绩表、学生表、课程表
第二步：从成绩表筛选出所有成绩大于70的记录
SELECT * FROM sc WHERE score > 70;
第三步：关联学生表，获取学生的姓名，关联课程表，获取课程的名称，然后选出想要的字段
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;
30. 查询存在不及格的课程
第一步：从成绩表中过滤出哪些记录的分数小于60分，然后去重获取到cid
SELECT
DISTINCT cid
FROM sc
WHERE score < 60;
第二步：从课程表获取课程信息
SELECT
*
FROM course
WHERE cid IN (SELECT DISTINCT cid FROM sc WHERE score < 60);
