SQL语句面试经典50题

发表于 | 更新于 | 分类于 | 评论数： | 阅读次数： | 文章字数： 4,423 | 阅读时长： 18分钟

SQL语句面试经典50题

2019/12/03 19:36 今天更新到这

2019/12/04 22:05 项目中完成了表单中的上传下载接口与前端对接。今天加班到八点，话说加班可以调休。希望过年调几天。

2019/12/05 01:43 今天研究了一下Flowable 工作流框架，明天继续。夜深了，休息。

2019/12/06 13:23 刚才在b站看了一个程序员老哥被裁的纪念品上面写着：感谢有你，一路同行，怀念我们一起奋斗的时光。

0.学习目标

  • 了解SQL的作用
  • 掌握SQL语句的编写

1. 为什么要练习sql语句？

做为一个后端开发人员，sql语句的编写是至关重要的。在实习的第一天到公司，我的leader戴哥就是让我练习sql语句。尽管用mybatis plus，但是sql语句可以处理复杂的逻辑。

会写sql的程序员，才是真的的crud boy。

练习sql语句，主要看了猴子和小番茄的知乎博文分析。

这里有sql面试的50题，帮助大家更进一步的熟悉SQL. SQL是数据分析师的必备基础技能，希望大家跟我一起来打怪升级，最后成为某一领域的数据科学家。

常见的SQL面试题：

经典50题 - 知乎 https://zhuanlan.zhihu.com/p/38354000

SQL面试必会50题 - 知乎 https://zhuanlan.zhihu.com/p/43289968

2.练习题分析阶段

常见的SQL面试题：经典50题

已知有如下4张表：

学生表：student(学号,学生姓名,出生年月,性别)

成绩表：score(学号,课程号,成绩)

课程表：course(课程号,课程名称,教师号)

教师表：teacher(教师号,教师姓名)

1.学生表
Student(s_id,s_name,s_birth,s_sex) —学生编号,学生姓名, 出生年月,学生性别
2.课程表
Course(c_id,c_name,t_id) – —课程编号, 课程名称, 教师编号
3.教师表
Teacher(t_id,t_name) —教师编号,教师姓名
4.成绩表
Score(s_id,c_id,s_score) —学生编号,课程编号,分数

根据以上信息按照下面要求写出对应的SQL语句。

ps：这些题考察SQL的编写能力，对于这类型的题目，需要你先把4张表之间的关联关系搞清楚了，最好的办法是自己在草稿纸上画出关联图，然后再编写对应的SQL语句就比较容易了。下图是我画的这4张表的关系图，可以看出它们之间是通过哪些外键关联起来的：

img

3.准备阶段

3.1.创建数据库和表

为了演示题目的运行过程，我们先按下面语句在客户端navicat中创建数据库和表。

img

img 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
-- 建表
-- 学生表
CREATE TABLE `Student`(
	`s_id` VARCHAR(20),
	`s_name` VARCHAR(20) NOT NULL DEFAULT '',
	`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
	`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
	PRIMARY KEY(`s_id`)
);
-- 课程表
CREATE TABLE `Course`(
	`c_id`  VARCHAR(20),
	`c_name` VARCHAR(20) NOT NULL DEFAULT '',
	`t_id` VARCHAR(20) NOT NULL,
	PRIMARY KEY(`c_id`)
);
-- 教师表
CREATE TABLE `Teacher`(
	`t_id` VARCHAR(20),
	`t_name` VARCHAR(20) NOT NULL DEFAULT '',
	PRIMARY KEY(`t_id`)
);
-- 成绩表
CREATE TABLE `Score`(
	`s_id` VARCHAR(20),
	`c_id`  VARCHAR(20),
	`s_score` INT(3),
	PRIMARY KEY(`s_id`,`c_id`)
);
-- 插入学生表测试数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
-- 课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

-- 教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

-- 成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

Student表：

image-20191212102315915

Course表：

image-20191212102510760

Teacher表：

Teacher

Score表：

Score

4.面试题

为了方便学习，我将50道面试题进行了分类练习

4.1.练习：简单查询

img

1.查询姓“猴”的学生名单

img

image-20191212105344854

image-20191212105601321

image-20191212105632018

2.查询姓“孟”老师的个数

1
2
3
SELECT COUNT(*) AS 姓“孟”老师的个数
FROM teacher
WHERE t_name LIKE '孟%'

image-20191212105917024

4.2.练习：汇总、分组、分组条件查询

4.2.1.练习：汇总

img

3. 面试题：查询课程编号为“0002”的总成绩

1
2
3
4
5
6
7
8
9
10
11
/*
分析思路
select 查询结果 [总成绩:汇总函数sum]
from 从哪张表中查找数据[成绩表score]
where 查询条件 [课程号是0002]
*/

SELECT sum(s_score)
FROM score

WHERE c_id ='0002'

image-20191212110022584

4. 查询选了课程的学生人数

1
2
3
4
5
6
7
8
/*
这个题目翻译成大白话就是：查询有多少人选了课程
select 学号，成绩表里学号有重复值需要去掉
from 从课程表查找score;
*/
SELECT COUNT(DISTINCT c_id) AS 学生人数

FROM score

image-20191212110055542

4.2.2 练习：分组

img

5.查询各科成绩最高和最低的分

以如下的形式显示：课程号，最高分，最低分 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
/*
分析思路
select 查询结果 [课程ID：是课程号的别名,最高分：max(成绩) ,最低分：min(成绩)]
from 从哪张表中查找数据 [成绩表score]
where 查询条件 [没有]
group by 分组 [各科成绩：也就是每门课程的成绩，需要按课程号分组];
*/
/*
上述题的拆分：课程id为0002的最高分与最低分

SELECT MAX(s_score) AS 最高分, MIN(s_score) AS 最低分
FROM score
WHERE c_id = '0002'
*/

SELECT
	c_id,MAX(s_score) AS 最高分,MIN(s_score) AS 最低分
FROM score
GROUP BY c_id

image-20191212110934135

6.查询每门课程被选修的学生数

— 通过对成绩表的课程id进行分组，然后对该学号进行计数

1
2
3
4
5
6
7
8
9
10
11
/*
分析思路
select 查询结果 [课程号，选修该课程的学生数：汇总函数count]
from 从哪张表中查找数据 [成绩表score]
where 查询条件 [没有]
group by 分组 [每门课程：按课程号分组];
*/
SELECT c_id,COUNT(c_id)
FROM
score
GROUP BY c_id

image-20191212111222219

7.查询男生、女生人数

  • 在学生表中对性别进行分组 计数

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    /*
    分析思路
    select 查询结果 [性别，对应性别的人数：汇总函数count]
    from 从哪张表中查找数据 [性别在学生表中，所以查找的是学生表student]
    where 查询条件 [没有]
    group by 分组 [男生、女生人数：按性别分组]
    having 对分组结果指定条件 [没有]
    order by 对查询结果排序[没有];
    */


    SELECT s_sex,COUNT(s_sex) AS 个数
    FROM student
    GROUP BY s_sex

image-20191212111302108

4.2.3 分组结果的条件

img

8.查询平均成绩大于60分学生的学号和平均成绩

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
/* 
题目翻译成大白话：
平均成绩：展开来说就是计算每个学生的平均成绩
这里涉及到“每个”就是要分组了
平均成绩大于60分，就是对分组结果指定条件

分析思路
select 查询结果 [学号，平均成绩：汇总函数avg(成绩)]
from 从哪张表中查找数据 [成绩在成绩表中，所以查找的是成绩表score]
where 查询条件 [没有]
group by 分组 [平均成绩：先按学号分组，再计算平均成绩]
having 对分组结果指定条件 [平均成绩大于60分]
*/

-- 在成绩表中对学号进行分组求平均成绩  having条件是平均成绩>60分
select s_id,AVG(s_score)
FROM score
GROUP BY s_id
HAVING AVG(s_score)>60
ORDER BY AVG(s_score) DESC

image-20191212111352401

9.查询至少选修两门课程的学生学号

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
/* 
翻译成大白话：
第1步，需要先计算出每个学生选修的课程数据，需要按学号分组
第2步，至少选修两门课程：也就是每个学生选修课程数目>=2，对分组结果指定条件

分析思路
select 查询结果 [学号,每个学生选修课程数目：汇总函数count]
from 从哪张表中查找数据 [课程的学生学号：课程表score]
where 查询条件 [至少选修两门课程：需要先计算出每个学生选修了多少门课，需要用分组，所以这里没有where子句]
group by 分组 [每个学生选修课程数目：按课程号分组，然后用汇总函数count计算出选修了多少门课]
having 对分组结果指定条件 [至少选修两门课程：每个学生选修课程数目>=2]
*/

SELECT s_id,COUNT(c_id) as 选修课程数目
FROM score
GROUP BY c_id
HAVING COUNT(c_id)>=2

image-20191212130502309

10.查询同名同性学生名单并统计同名人数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
/* 
翻译成大白话，问题解析：
1）查找出姓名相同的学生有谁，每个姓名相同学生的人数
查询结果：姓名,人数
条件：怎么算姓名相同？按姓名分组后人数大于等于2，因为同名的人数大于等于2
分析思路
select 查询结果 [姓名,人数：汇总函数count(*)]
from 从哪张表中查找数据 [学生表student]
where 查询条件 [没有]
group by 分组 [姓名相同：按姓名分组]
having 对分组结果指定条件 [姓名相同：count(*)>=2]
order by 对查询结果排序[没有];
*/

SELECT s_name,COUNT(s_name)
FROM student
GROUP BY s_name
HAVING COUNT(s_name)>=2

image-20191212131157051

11.查询不及格的课程并按课程号从大到小排列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
/* 
分析思路
select 查询结果 [课程号]
from 从哪张表中查找数据 [成绩表score]
where 查询条件 [不及格：成绩 <60]
group by 分组 [没有]
having 对分组结果指定条件 [没有]
order by 对查询结果排序[课程号从大到小排列：降序desc];
*/

SELECT c_id
FROM score
WHERE s_score<60
GROUP BY c_id
ORDER BY c_id

image-20191212131322831

12.查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
/* 
分析思路
select 查询结果 [课程号,平均成绩：汇总函数avg(成绩)]
from 从哪张表中查找数据 [成绩表score]
where 查询条件 [没有]
group by 分组 [每门课程：按课程号分组]
having 对分组结果指定条件 [没有]
order by 对查询结果排序[按平均成绩升序排序:asc，平均成绩相同时，按课程号降序排列:desc];
*/

SELECT c_id,AVG(s_score) as 平均成绩
FROM score
GROUP BY c_id
ORDER BY AVG(s_score) ASC,c_id DESC

image-20191212131432805

13.检索课程编号为“0004”且分数小于60的学生学号，结果按按分数降序排列

1
2
3
4
5
6
7
8
9
10
11
12
13
/* 
分析思路
select 查询结果 []
from 从哪张表中查找数据 [成绩表score]
where 查询条件 [课程编号为“04”且分数小于60]
group by 分组 [没有]
having 对分组结果指定条件 []
order by 对查询结果排序[查询结果按按分数降序排列];
*/
select s_id,s_score
FROM score
WHERE c_id = "0004" AND s_score >=60
ORDER BY s_score DESC

image-20191212131500424

14.统计每门课程的学生选修人数(超过2人的课程才统计)

要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
/* 
分析思路
select 查询结果 [要求输出课程号和选修人数]
from 从哪张表中查找数据 []
where 查询条件 []
group by 分组 [每门课程：按课程号分组]
having 对分组结果指定条件 [学生选修人数(超过2人的课程才统计)：每门课程学生人数>2]
order by 对查询结果排序[查询结果按人数降序排序，若人数相同，按课程号升序排序];
*/

SELECT c_id,COUNT(score.s_id) as '选修人数'
FROM score
GROUP BY c_id
HAVING COUNT(score.s_id) > 2
ORDER BY COUNT(score.s_id) DESC,c_id ASC

image-20191212134936442

15.查询两门以上不及格课程的同学的学号及其平均成绩

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
/*
分析思路
先分解题目：
1）[两门以上][不及格课程]限制条件
2）[同学的学号及其平均成绩]，也就是每个学生的平均成绩，显示学号，平均成绩
分析过程：
第1步：得到每个学生的平均成绩，显示学号，平均成绩
第2步：再加上限制条件：
1）不及格课程
2）两门以上[不及格课程]：课程数目>2


/* 
第1步：得到每个学生的平均成绩，显示学号，平均成绩
select 查询结果 [学号,平均成绩：汇总函数avg(成绩)]
from 从哪张表中查找数据 [涉及到成绩：成绩表score]
where 查询条件 [没有]
group by 分组 [每个学生的平均：按学号分组]
having 对分组结果指定条件 [没有]
order by 对查询结果排序[没有];
*/
select 学号, avg(成绩) as 平均成绩
from score
group by 学号;


/* 
第2步：再加上限制条件：
1）不及格课程
2）两门以上[不及格课程]
select 查询结果 [学号,平均成绩：汇总函数avg(成绩)]
from 从哪张表中查找数据 [涉及到成绩：成绩表score]
where 查询条件 [限制条件：不及格课程，平均成绩<60]
group by 分组 [每个学生的平均：按学号分组]
having 对分组结果指定条件 [限制条件：课程数目>2,汇总函数count(课程号)>2]
order by 对查询结果排序[没有];
*/


SELECT s_id,COUNT(s_score),avg(s_score) AS 平均成绩
FROM score
WHERE s_score<60
GROUP BY s_id
HAVING COUNT(s_score)>=2

image-20191212140305267

4.3.复杂查询

16.查询所有课程成绩小于60分学生的学号、姓名

这道题目知乎猴子解法是存在问题的。我这边提供我的理解吧。

第一种做法：将学号分组 在每个学号组 找成绩最高值<60的学号

1
2
3
4
5
6
7

SELECT score.s_id,student.s_name
FROM score
INNER JOIN student
ON score.s_id = student.s_id
GROUP BY s_id
HAVING MAX(s_score) <60

image-20191215011833878

第二种解法：

step1: 查询出学生课程数的统计量

step2: 查询出学生课程成绩<60分对应课程数的统计量

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT A.s_id,S.s_name
FROM

(SELECT s_id,COUNT(c_id) AS cnt
FROM score
GROUP BY score.s_id) AS A

INNER JOIN

(SELECT s_id,COUNT(c_id) AS cnt
FROM score
WHERE s_score<60
GROUP BY s_id) AS B

ON A.s_id = B.s_id

INNER JOIN student AS S
ON A.s_id = S.s_id

WHERE A.cnt = B.cnt

image-20191216100937996

17、查询没有学全所有课的学生的学号、姓名

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 第一种解法：
-- 首先学号分组  做having条件：每个学号统计出不同课程数量  小于 课程表中不同课程数量
-- 上面结果为没有学全所有课程的学号
-- 然后在学生表中  WHERE s_id IN ()条件  查找出学号、姓名。（这一步也可以用 INNER JOIN ON。）

SELECT s_id,s_name FROM student
WHERE s_id IN
(
SELECT s_id
FROM score
GROUP BY s_id
HAVING COUNT(DISTINCT c_id) < (SELECT COUNT(DISTINCT c_id) FROM course)
)


-- 这种解法后来发现score表会存在一些学生没有成绩。比如说王菊同学一门课程成绩都不存在。
-- 但是上述做法没有把王菊同学查找出来。

-- 第二种解法：

18、查询出只选修了两门课程的全部学生的学号和姓名

1990年出生的学生名单

查询各科成绩前两名的记录

分组取每组最大值、最小值，每组最大的N条（top N）记录。

4.4 sql面试题：topN问题

4.5 多表查询

img

查询所有学生的学号、姓名、选课数、总成绩

查询平均成绩大于85的所有学生的学号、姓名和平均成绩

查询学生的选课情况：学号，姓名，课程号，课程名称

查询出每门课程的及格人数和不及格人数

使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称

查询课程编号为0003且课程成绩在80分以上的学生的学号和姓名|

4.6 sql面试题：行列如何互换？

下面是学生的成绩表（表名score，列名：学号、课程号、成绩）

img

使用sql实现将该表行转列为下面的表结构

img

【面试题类型总结】这类题目属于行列如何互换，解题思路如下：

坚持原创技术分享，您的支持将鼓励我继续创作！
//