SQL语句练习题3 多表查询

【一句话概括本文】

本文旨在通过mysql实验的方式完成8-9道多表查询语言题。

【本文特！色！】

1.实验验证。实践是检验整理唯一的标准，而非作者一拍脑门写出的结果。

2.sql型配色。颜色已经蕴含了信息。

3.配图。干巴巴的文字没有说服力。一图胜千言。

4.目录。利用目录快速到达你有疑问的题目。这真的是个很方便的功能！

一定要去找一下，手机电脑都可以用目录功能。

5.难点标识。你觉得难并不奇怪，作者已经帮你试过了，真的有难度。

【题目】

有以下四个表

1）department 表（院系编号、院系名称），deptNo是主键。

2）student表（学号、姓名、年龄、性别、院系），sno是主键，deptNo是外键。

3）course表（课程号、课程名、先修课、学分、院系编号），cno是主键。

4）SC表（学号、课程号、成绩）选课表，学号和课程号联合做主键。

详细内容请回顾练习题1：

SQL语句练习—创建表和索引-优快云博客

多表联合查询

1. 检索每个学生的总学分，输出学号、姓名、院系和总学分，按院系排序。
2. 检索计算机系所开课程的课程号和课程名。
3. 检索缺考2门以上的学生姓名。
4. 检索选修C语言且成绩不及格的计算机系的学生的姓名与学号。
5. 求计算机系所开课程的每门课程的学生平均成绩，按平均成绩从高到低排序输出。
6. 检索所有学生及其所学课程的信息，即使没有选课，也要列出学生的信息。
7. 查询选修3门以上课程且平均成绩大于80的学生的学号
8. 查询选修3门以上课程且平均成绩大于80的学生的学号、姓名、所在系名。
9. 检索王丽同学所学课程的课程号和课程名。

【重要题干补充（必看）】

部分题目需要先插入数据再查询，否则查询结果为空集无法验证查询是否正确。具体直接利用目录功能跳转到有卡壳的题目。

【解析】

1.检索每个学生的总学分，输出学号、姓名、院系和总学分，按院系排序。

（多表查询的第一题非常难，是个巨大的飞跃，比系列文章前面两节的建表语句、单表查询难很多，需要多个步骤才能得出结果。初级要求需要理解：等值连接，高级要求需要理解：外连接。此外，还要与前面两章所学融会贯通。后面的题比这道题简单很多）

【初级要求】忽略学生表、课程表不存在的值（NULL值）

第一步，使用等值连接进行观察

SELECT * from student,course,sc where student.sno=sc.sno and sc.cno=course.cno

运行结果：

第二步，按照题目要求使得等值连接增加where子句和order by子句

运行结果：

Select student.sno, student.sname,student.deptno,SUM(credit)from student,course,sc where student.sno=sc.sno and sc.cno=course.cno GROUP BY sno ORDER BY student.deptno

第三步，改进院系号码为院系名称，增加等值连接即可

Select student.sno, student.sname,department.deptname,SUM(credit)

from student,course,sc,department

where student.sno=sc.sno and sc.cno=course.cno AND student.deptno=department.deptno

GROUP BY sno ORDER BY student.deptno

运行结果：

2.检索计算机系所开课程的课程号和课程名。

Select * from department,course 

where department.deptname='计算机系' and department.deptno=course.deptno

运行结果：

【改进】只保留课程号和课程名

Select course.cno,course.cname from department,course 

where department.deptname='计算机系' and department.deptno=course.deptno

【更简单的写法】不想打一大堆department，给重命名一下（注意不是起别名，因为原来的department不能用了，被覆盖了。并且as可以省略，写出来只是方便自己看）

Select course.cno,course.cname from department AS dpt,course 

where dpt.deptname='计算机系' and dpt.deptno=course.deptno

注意后面的department必须全部都改成dpt，因为原来的department不能用了，被覆盖了

3.检索缺考2门以上的学生姓名。

由于之前的数据没有人缺考2门以上，所以我们手动添加一个缺考2门以上的学生。

#人为制造一个缺考3门的学生

INSERT INTO sc (Sno, Cno, Grade) VALUES ('20210011', 'C02', NULL);

INSERT INTO sc (Sno, Cno, Grade) VALUES ('20210011', 'C03', NULL);

INSERT INTO sc (Sno, Cno, Grade) VALUES ('20210011', 'C04', NULL);

SELECT *FROM sc;

运行结果：

第一步，先不管姓名，只注重找到结果（比如学号）

分析：缺考2门以上，也就是按照sno分组，group by sno，grade有2个以上的null值

下面这个写法是错误的：

Select *  from sc where grade is null group by sno having count(grade)>2

原因：使用 * 在这里是不合适的，因为正在对 sno 进行分组。把*改成sno后，结果还是空集。这是因为什么呢？我们来仔细解释一下。

【更详细的解释】

*改成sno前

SELECT sno FROM sc WHERE grade IS NULL GROUP BY sno HAVING COUNT(*) > 2;

这个查询语句做了以下几件事情：

SELECT sno：选择 sc 表中的 sno（学号）列。

FROM sc：指定查询的数据来源是 sc 表。

WHERE grade IS NULL：筛选出 grade（成绩）列为 NULL 的记录。

GROUP BY sno：将结果按照 sno 进行分组。

HAVING COUNT(*) > 2：对每个分组应用条件，只保留那些分组中记录数超过2的学号。这里的 COUNT(*) 计算每个分组中的总记录数，无论 grade 是否为 NULL。

*改成sno后

SELECT sno FROM sc WHERE grade IS NULL GROUP BY sno HAVING COUNT(grade) > 2;

这个查询语句也做了以下几件事情：

SELECT sno、FROM sc 和 GROUP BY sno 与第一个查询相同。

WHERE grade IS NULL：同样筛选出 grade 为 NULL 的记录。

HAVING COUNT(grade) > 2：这是与第一个查询不同的地方。这里的 COUNT(grade) 只计算每个分组中 grade 列非 NULL 值的数量。由于 WHERE 子句已经筛选出 grade 为 NULL 的记录，理论上 COUNT(grade) 应该始终为0。因此，这个查询不会返回任何结果，因为不存在任何分组中 grade 列非 NULL 值的数量超过2的情况。

正确的写法是：

Select sno  from sc where grade is null group by sno having COUNT(*)>2

第二步，从学号到姓名

Select sc.sno,sname  from sc,student where grade is NULL AND sc.Sno=student.sno group by sno having COUNT(*)>2

运行结果：

4.检索选修C语言且成绩不及格的计算机系的学生的姓名与学号。

补充必要的数据：

#补充6个学生选修C语言

INSERT INTO sc (Sno, Cno, Grade) VALUES ('20210001', 'C05', 88);

INSERT INTO sc (Sno, Cno, Grade) VALUES ('20210002', 'C05', 57);

INSERT INTO sc (Sno, Cno, Grade) VALUES ('20210003', 'C05', 55);

INSERT INTO sc (Sno, Cno, Grade) VALUES ('20210004', 'C05', 59);

INSERT INTO sc (Sno, Cno, Grade) VALUES ('20210005', 'C05', 80);

INSERT INTO sc (Sno, Cno, Grade) VALUES ('20210006', 'C05', NULL);

SELECT*FROM sc;

#进行查询

SELECT s.sname,s.sno FROM sc,student AS s,department AS d, course AS c 

WHERE s.sno=sc.sno AND s.deptNo=d.deptno AND sc.cno=c.cno

AND d.deptName='计算机系' AND c.cname='C语言'

AND sc.Grade<60

运行结果：

5.求计算机系所开课程的每门课程的学生平均成绩，按平均成绩从高到低排序输出。

5补.求计算机系的学生每门课程平均成绩

注意：下面这个不是最简单的写法，只是拷贝了第4题的等值连接框架，实现起来更方便。

SELECT  s.sname,s.sno,AVG(grade) FROM sc,student AS s,department AS d, course AS c 

WHERE s.sno=sc.sno AND s.deptNo=d.deptno AND sc.cno=c.cno

AND d.deptName='计算机系'

GROUP BY sno

6.检索所有学生及其所学课程的信息，即使没有选课，也要列出学生的信息。

#插入12号和13号两个新生，ta们没有选任何课

INSERT INTO student (Sno, Sname, Sage, Ssex, deptNo) VALUES ('20210012', '魏选课', 20, '女', 3);

INSERT INTO student (Sno, Sname, Sage, Ssex, deptNo) VALUES ('20210013', '梅选课', 20, '男', 2);

SELECT*FROM student;

SELECT*FROM sc;

运行结果：

下面做题

【初级要求】之前我们一直用的等值连接

SELECT  s.Sno, s.Sname, c.Cno, c.Cname FROM sc,student AS s,department AS d, course AS c 

WHERE s.sno=sc.sno AND s.deptNo=d.deptno AND sc.cno=c.cno

运行结果：

19条数据，比sc表中少2条。因为魏选课和梅选课两人无选课不在表中。

【高级要求】使用外连接。

（注：外连接会保留悬浮元组，所谓悬浮元组就是连接项为空而只能悬浮的元组）

SELECT student.Sno, student.Sname,c.Cno,c.cname

FROM student   

LEFT OUTER JOIN SC ON (Student.Sno=SC.Sno)

LEFT OUTER JOIN course AS c ON (c.Cno=sc.cno)

7.查询选修3门以上课程且平均成绩大于80的学生的学号

Select后面的内容根据需要自己调整，这里为了方便大家看select了更多信息，同时完成了第8题。

SELECT s.sno,sname,deptname,avg(grade)

FROM sc,student AS s,department AS d, course AS c 

WHERE s.sno=sc.sno AND s.deptNo=d.deptno AND sc.cno=c.cno

GROUP BY s.sno

HAVING  AVG(grade)>80 AND COUNT(grade)>3

8.查询选修3门以上课程且平均成绩大于80的学生的学号、姓名、所在系名。

第7题中已经完成啦！

9.检索王丽同学所学课程的课程号和课程名。

SELECT c.cno,cname FROM student AS s

LEFT JOIN sc ON(s.sno=sc.Sno)

LEFT JOIN course c ON(c.Cno=sc.Cno)

WHERE  sname='王丽'

【补充思考】

请大家重做第1题，达到高级要求：

【高级要求】保留学生表、课程表不存在的值（NULL值）

恭喜您又进步了，本文约5400字！学完本文，您掌握了8-9个多表查询语句，特别是等值连接（包含表的重命名）和外连接（join）的使用。

非常非常非常非常非常感谢您的观看！如您发现任何错误，欢迎在评论区批评指教！

由于笔者学识有限，必然存在疏漏错误，请各位读者海涵！

您的点赞、评论是作者更新的最大动力~

动动手指，让全世界有更多好文章吧！

配套文档资源链接可以免费下载--已经上传到csdn（永久免费）

文章来源：csdn系列文章，sql数据库练习。

系列文章推荐：SQL语句练习题2 单表查询-优快云博客

【致谢】

第5题作者对题意理解错误，已经修改。

感谢评论区@fogshan和私信@哈巴哈巴 指出错误

感谢@哈巴哈巴 提供配图