SQL面试必会50题
前言
本篇跟着知乎文章SQL面试必会50题 - 知乎的顺序记录SQL刷题过程。
参考资料:
在每道题的标题后面会有一些符号:
- 无任何符号:没有思路 or 解题过程中参考了一些题解思路
- ✔:Acess!
- ❗:在编写代码的过程中遇到一些语法上的问题,或者是一些巧妙的解题思路,需要留意
- 每题的题号后会增加重点或超级重点的标识,需要特别留意
题目数据依赖
本节将详细说明刷题所依赖的数据内容。
表结构和关联关系
题目所依赖的表结构和关系如上图所示👆
总共包含四张表:学生表、成绩表、课程表、教师表。
四张表的关联字段:
- 学生表(Student) - 成绩表(Score):
Student.s_id = Score.s_id
【通过学号
关联】 - 成绩表(Score) - 课程表(Course):
Score.c_id = Course.c_id
【通过课程编号
关联】 - 课程表(Course) - 教师表(Teacher):
Course.t_id = Teacher.t_id
【通过教师号
关联】
建表和测试数据插入语句
建库
数据库属性如下:
- 数据库名:50_SQL_Practice_Exercises
- 字符集:utf8
- 排序规则:utf8_unicode_ci
建表
学生表:
1 | -- 学生表 |
课程表:
1 | -- 课程表 |
教师表:
1 | -- 教师表 |
成绩表:
1 | -- 成绩表 |
测试数据
学生表测试数据:
1 | -- 学生表测试数据 |
课程表测试数据:
1 | -- 课程表测试数据 |
教师表测试数据:
1 | -- 教师表测试数据 |
成绩表测试数据:
1 | -- 成绩表测试数据 |
题目
第一题【重点】
题目描述:查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号
题解:
1 | SELECT table_A.s_id FROM |
思路:构建一个临时表,表中包含学生学号、01课程成绩、02课程成绩,通过在此表中创建简单的查询语句就可以实现
- 首先在成绩表中拿到01课程的信息:
SELECT s_id, c_id, s_score FROM score WHERE c_id = '01'
- 然后在成绩表中拿到02课程的信息:
SELECT s_id, c_id, s_score FROM score WHERE c_id = '02'
- 通过
INNER JOIN
将两个表做内连接,连接字段为学生学号- 接下来就是简单查询的步骤,查询字段为
s_id
,查询条件为:table_A.s_score > table_B.s_score
第二题【重点】✔❗
题目描述:查询平均成绩大于60分的学生的学号和平均成绩
题解:
1 | SELECT s_id, AVG(s_score) AS avg_score FROM score |
主要考察
GROUP BY
的使用注意❗:一般在查找字段中使用了函数,如果该字段在过滤条件中也使用到了,那么通常要给该字段增加别名,否则在过滤条件中又使用函数,会使得查找效率降低。
第三题❗
题目描述:查询所有学生的学号、姓名、选课数、总成绩
题解:
1 | SELECT table_A.s_id, table_A.s_name, COUNT(table_B.c_id), SUM(CASE WHEN table_B.s_score IS NULL THEN 0 ELSE table_B.s_score END) |
思路:构建一个临时表,表中包含学生学号、学生姓名、课程号、课程成绩,通过在此表中创建简单的查询语句就可以实现
注意❗:
- table_A(学生表)和table_B(成绩表)需要使用左连接
LEFT JOIN
而非内连接INNER JOIN
,因为有可能学生没有任何成绩,那么通过内连接查询的话不会显示该学生。- 为了更严谨,
GROUP BY
中的字段应该是在查询字段中出现的,否则有可能报错,所以这里GROUP BY
同时使用table_A.s_id
和table_A.s_name
,其效果和仅使用其中一个字段是一样的。- 如果学生没有成绩的话,则直接在查询中使用
SUM(table_B.s_score)
会出现成绩为Null
的情况,为了避免,可以使用条件语句设置为0。
第四题✔
题目描述:查询姓“猴”的老师的个数
题解:
1 | SELECT COUNT(*) FROM teacher WHERE t_name LIKE '猴%'; |
简单查询
第五题【重点】❗
题目描述:查询没学过“张三”老师课的学生的学号、姓名
题解:
1 | SELECT s_id, s_name FROM student |
思路:一步一步执行子查询
- 先从教师表(teacher)中找到姓名为“张三”老师的教师号
t_id
- 再从课程表(course)中找到“张三”老师教的课的课程编号
c_id
- 接下来从成绩表(score )中找到学过“张三”老师教的课的学生的学号
s_id
- 最后从全体学生中排除学过该课的学生
注意❗:
正确的思路是先拿到学过“张三”老师课的学生,然后从全体学生中排除(针对此题的情况),因为判断学生是否学过课程是根据成绩表查询的,如果对应课程有成绩,则说明学生学过课程。但是不可以使用
SELECT * FROM score WHERE c_id != '指定课程号'
,因为一个学生可能学过好多们课,那这种查询方式只能把学过指定课程号的记录给删除,该学生的其他记录还在,没有办法起到筛选的作用。
第六题【重点】✔
题目描述:查询学过“张三”老师所教的所有课的同学的学号、姓名
题解一(嵌套子查询):
1 | SELECT s_id, s_name FROM student |
思路:可以按照第五题的思路逐步写嵌套子查询:
- 先从教师表(teacher)中找到姓名为“张三”老师的教师号
t_id
- 再从课程表(course)中找到“张三”老师教的课的课程编号
c_id
- 接下来从成绩表(score )中找到学过“张三”老师教的课的学生的学号
s_id
- 最后全体学生中查找学过该课的学生
题解二(表连接查询):
1 | SELECT student.s_id, student.s_name |
思路:可以将嵌套子查询的语句更改为表连接查询。
第七题【重点】❗
题目描述:查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名
题解:
1 | SELECT s_id, s_name FROM student |
思路:先查找学过“01”课程的学生编号,再查找学过“02”课程的学生编号,将两个表做内连接得到“临时编号集”,最后从学生表中查询在“临时编号集”中的学生。
注意❗:
切记不可以这样写:
SELECT s_id FROM score WHERE c_id = '01' AND c_id = '02';
解释:一个字段不可能存在
c_id
既为01
也为02
的情况。
第八题✔
题目描述:查询课程编号为“02”的总成绩
题解:
1 | SELECT SUM(s_score) FROM score WHERE c_id = '02'; |
简单查询
第九题❗
题目描述:查询所有课程成绩小于60分的学生的学号、姓名
题解:
1 | SELECT student.s_id, student.s_name FROM |
思路:
- 首先从成绩表(score )中查找所有成绩小于60分的学号
s_id
,以及小于60分的课程数量,通过GROUP BY s_id
返回结果- 再从成绩表(score )中查找每一位同学选过的课程的数量,通过
GROUP BY s_id
返回结果- 对比两个课程数是否一致,通过
WHERE
条件判断注意❗:
另外一种思路不可取:先找到所选课程最高分大于等于60分的所有同学的集合,再从学生表中排除(
NOT IN
)这个该集合,剩下的就是要查询的结果。为什么不可取?因为有同学没有选择任何课程(即成绩表(score )中没有该同学的信息),那么这种方式会筛选出这样的同学,这并非预期结果。
后记
To be continued…