本篇节选自书籍《对比Excel，轻松学习SQL数据分析》一书，主要讲解数据分析面试中常见的30道SQL面试题。
1 查询每个班学生数
现在有一张全校学生信息表stu_table，这张表存储了每位学生的id、name(姓名)、class(班级)、sex(性别)以及一些其他信息，现在我们想知道每个班有多少学生，该怎么实现呢？
stu_table表如下所示：
id | name | class | sex |
|---|---|---|---|
4 | 张文华 | 二班 | 男 |
3 | 李思雨 | 一班 | 女 |
1 | 王小凤 | 一班 | 女 |
7 | 李智瑞 | 三班 | 男 |
6 | 徐文杰 | 二班 | 男 |
8 | 徐雨秋 | 三班 | 男 |
5 | 张青云 | 二班 | 女 |
9 | 孙皓然 | 三班 | 男 |
10 | 李春山 | 三班 | 男 |
2 | 刘诗迪 | 一班 | 女 |
自己先想一下代码怎么写，然后再参考我的代码。
代码语言：javascript复制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，现在我们想知道每个班男生女生分别有多少个？
自己先想一下代码怎么写，然后再参考我的代码。
代码语言：javascript复制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，现在我们想知道这张表中姓张的同学有多少个？
自己先想一下代码怎么写，然后再参考我的代码。
代码语言：javascript复制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位的同学的信息。
自己先想一下代码怎么写，然后再参考我的代码。
代码语言：javascript复制select
*
from
demo.stu_table
order by id asc
limit 4,6
解题思路：
我们要获取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 |
自己先想一下代码怎么写，然后再参考我的代码。
代码语言：javascript复制select
*
from
demo.score_table
where score < 60
解题思路：
我们要获取挂科同学的信息，只需要加一个where条件用来限定挂科这个条件即可。最后运行结果如下：
id | name | class | score |
|---|---|---|---|
4 | 张文华 | 二班 | 55 |
7 | 李智瑞 | 三班 | 56 |
10 | 李春山 | 三班 | 57 |
6 筛选姓张的且挂科的同学
我们现在需要根据学生成绩表score_table查找出姓张的且挂科的同学的信息。
自己先想一下代码怎么写，然后再参考我的代码。
代码语言：javascript复制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 |
自己先想一下代码怎么写，然后再参考我的代码。
代码语言：javascript复制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 |
自己先想一下代码怎么写，然后再参考我的代码。
代码语言：javascript复制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 |
自己先想一下代码怎么写，然后再参考我的代码。
代码语言：javascript复制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 | 孙皓然 | 一班 | 一年级 |
自己先想一下代码怎么写，然后再参考我的代码。
代码语言：javascript复制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 |
|---|---|
一年级 | 一班 |
一年级 | 二班 |
二年级 | 一班 |
二年级 | 二班 |
