几道经典的SQL面试题讲解①

本篇节选自书籍《对比Excel，轻松学习SQL数据分析》一书，主要讲解数据分析面试中常见的几道SQL面试题。

1 查询每个班学生数

现在有一张全校学生信息表stu_table，这张表存储了每位学生的id、name(姓名)、class(班级)、sex(性别)以及一些其他信息，现在我们想知道每个班有多少学生，该怎么实现呢？

stu_table表如下所示：

id	name	class	sex
4	张文华	二班	男
3	李思雨	一班	女
1	王小凤	一班	女
7	李智瑞	三班	男
6	徐文杰	二班	男
8	徐雨秋	三班	男
5	张青云	二班	女
9	孙皓然	三班	男
10	李春山	三班	男
2	刘诗迪	一班	女

自己先想一下代码怎么写，然后再参考我的代码。

select 
    class
    ,count(id) as stu_num
from
    demo.stu_table
group by
    class

解题思路：

我们是要获取每个班的学生数，首先需要对班级进行分组，使用的是group by；然后再对每个组内的学生进行计数聚合运算，使用的count。最后运行结果如下：

class	stu_num
二班	3
一班	3
三班	4

2 查询每个班男女学生数

还是前面的全校学生信息表stu_table，现在我们想知道每个班男生女生分别有多少个？

自己先想一下代码怎么写，然后再参考我的代码。

select 
    class
    ,sex
    ,count(id) as stu_num
from
    demo.stu_table
group by
    class
    ,sex

解题思路：

与第一题不同的是，不仅需要每个班级的信息，还需要每个班级里面男女生分别的信息，主要考察的就是按照多列分组聚合的知识，直接在group by后面指明要分组的多列即可，且列与列之间用逗号分隔开。最后运行结果如下：

class	sex	stu_num
二班	男	2
一班	女	3
三班	男	4
二班	女	1

3 姓张的同学有多少个

还是前面的全校学生信息表stu_table，现在我们想知道这张表中姓张的同学有多少个？

自己先想一下代码怎么写，然后再参考我的代码。

select 
    count(id) as stu_num
from
    demo.stu_table
where name like "张%"

解题思路：

我们是要获取姓张的同学有多少个，首先需要思考的是怎么去判断同学是否姓张，假设我们表里面存储的姓名都是先姓后名的形式，那就可以用到字符串匹配函数like；知道怎么判断同学是否姓张，接下来就是把这些同学筛选出来，使用的是where条件；最后针对筛选出来的同学进行计数，使用的是count。最后运行结果如下：

stu_num
2

4 筛选出id第3-5的同学

还是前面的全校学生信息表stu_table，现在我们要获取id从小到大排序以后第3-5位的同学的信息。

自己先想一下代码怎么写，然后再参考我的代码。

select 
    *
from
    demo.stu_table
order by id asc
limit 2,3

解题思路：

我们要获取id从小到大排序以后第3-5位的同学，因为不确定id是否连续，所以我们没法直接用where条件来筛选id。我们先对id进行升序排列，然后再利用limit进行筛选。最后运行结果如下：

id	name	class	sex
3	李思雨	一班	女
4	张文华	二班	男
5	张青云	二班	女

5 筛选出挂科的同学

现在有一张学生成绩表score_table，这张表存储了每位学生的id、name(姓名)、class(班级)、score(成绩)，现在我们想要把挂科(成绩小于60)的同学信息筛选出来。

score_table表如下所示：

id	name	class	score
1	王小凤	一班	88
2	刘诗迪	一班	70
3	李思雨	一班	92
4	张文华	二班	55
5	张青云	二班	77
6	徐文杰	二班	77
7	李智瑞	三班	56
8	徐雨秋	三班	91
9	孙皓然	三班	93
10	李春山	三班	57

自己先想一下代码怎么写，然后再参考我的代码。

select 
    *
from
    demo.score_table
where score < 60

解题思路：

我们要获取挂科同学的信息，只需要加一个where条件用来限定挂科这个条件即可。最后运行结果如下：

id	name	class	score
4	张文华	二班	55
7	李智瑞	三班	56
10	李春山	三班	57

6 筛选姓张的且挂科的同学

我们现在需要根据学生成绩表score_table查找出姓张的且挂科的同学的信息。

自己先想一下代码怎么写，然后再参考我的代码。

select 
    *
from
    demo.score_table
where score < 60
    and name like "张%"

解题思路：

这里面主要是用到了多条件筛选，多个条件之间用and进行关联即可。最后运行结果如下：

id	name	class	score
4	张文华	二班	55

7 查询销冠获得次数

我们有一张表month_table记录了每月的销售冠军信息，这张表存储了每月销冠的id、name(姓名)、month_num(月份)，现在需要获取销冠次数超过2次的人以及其对应的做销冠次数。

month_table表如下所示：

id	name	month_num
E002	王小凤	1
E001	张文华	2
E003	孙皓然	3
E001	张文华	4
E002	王小凤	5
E001	张文华	6
E004	李智瑞	7
E002	王小凤	8
E003	孙皓然	9

自己先想一下代码怎么写，然后再参考我的代码。

select 
    id
    ,name
    ,count(month_num) num
from 
    demo.month_table
group by 
    id
    ,name
having 
    count(month_num) > 2

解题思路：

我们要获取销冠次数超过2次的人以及其对应的做销冠次数，首先需要获取每个人做销冠的次数，对id进行group by，然后在组内对month_num进行计数即可；然后再对分组聚合后的结果利用having进行条件筛选。最后结果如下：

id	name	num
E002	王小凤	3
E001	张文华	3

8 获取每个部门一整年业绩提升幅度

现在有一个月份销售额记录表sale_table，这个表记录了每年每月的销售额，现在我们想看下今年(2019年)，月销售额最高涨幅是多少？

sale_table表如下所示：

year_num	month_num	sales
2019	1	2854
2019	2	4772
2019	3	3542
2019	4	1336
2019	5	3544
2018	1	2293
2018	2	2559
2018	3	2597
2018	4	2363

自己先想一下代码怎么写，然后再参考我的代码。

select 
    max(sales) as max_sales
 ,min(sales) as min_sales
    ,max(sales)-min(sales) as cha
    ,(max(sales)-min(sales))/min(sales) as growth
from 
    demo.sale_table
where 
    year_num = 2019

解题思路：

我们要获取今年的最大月涨幅，首先需要通过where条件把今年的每月数据销售额筛选出来；然后再在今年的月销售额里面寻找最大和最小的销售额，对两者进行做差，就是我们想要的结果。最后运行结果如下：

max_sales	min_sales	cha	growth
4772	1336	3436	2.5719

9 查找每科成绩大于70的学生

我们有一张学生科目成绩表score_info_table，这张表记录了每一位同学每一科目的成绩，每一位同学的每科成绩是一行，现在我们想要通过这张表获取到每科成绩都大于70分的学生。

score_info_table表如下所示：

id	name	subject	score
1	王小凤	语文	88
2	张文华	数学	70
3	徐雨秋	英语	92
1	王小凤	语文	55
2	张文华	数学	77
3	徐雨秋	英语	77
1	王小凤	语文	72
2	张文华	数学	91
3	徐雨秋	英语	93

自己先想一下代码怎么写，然后再参考我的代码。

select 
 id
    ,name
 from
    demo.score_info_table
 group by
    id
    ,name
 having 
    min(score) > 70    

解题思路：

我们是要获取每科成绩大于70的学生，只要能够保证最小成绩是大于70分的，就说明这位同学每科成绩都大于70分.所以第一步就是先获取每位同学的最小成绩，先对name进行group by分组，再在组内求最小值，然后将最小成绩大于70分的同学通过having筛选出来即可。最后运行结果如下：

id	name
3	徐雨秋

10 删除重复值

现在有一个学生信息表stu_info_table，这张表存储了每位学生id、name(姓名)、class(班级)、grade(年级)，现在我们想获取这个学校所有年级以及所有班级的信息,即哪些年级有哪些班级，该怎么获取？

stu_info_table表如下所示：

id	name	class	grade
1	王小凤	一班	一年级
2	刘诗迪	一班	二年级
3	李思雨	一班	一年级
4	张文华	二班	二年级
5	张青云	二班	一年级
6	徐文杰	二班	二年级
7	李智瑞	一班	一年级
8	徐雨秋	二班	二年级
9	孙皓然	一班	一年级

自己先想一下代码怎么写，然后再参考我的代码。

select
    grade
    ,class
from
    demo.stu_info_table
group by 
    grade
    ,class
order by
    grade

解题思路：

stu_table表中id列是主键，即不重复的，但是class和grade是重复的，多个id会属于同一个class和grade。我们只要class和grade信息，所以是需要对这两列进行去重，去重我们除了用distinct以外，还可以用group by。最后运行结果如下：

grade	class
一年级	一班
一年级	二班
二年级	一班
二年级	二班

●解一道反常的Pandas题●12000+字超详细 SQL 语法速成！
后台回复“入群”即可加入小z干货交流群