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

此文主要转载于知乎猴子老师的经典SQL50题，对文章中的一些错误做订正修改，欢迎大家来探讨~

推荐阅读：SQL50题总结 ； 在线sql编辑测试网站：sqlzoo

已知有如下4张表：

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

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

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

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

 

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

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

 

一、创建数据库和表

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

（如何你还不懂什么是数据库，什么是客户端navicat，可以先学习这个：从零学会SQL：入门）

1.创建表

1）创建学生表（student）

按下图在客户端navicat里创建学生表

学生表的“学号”列设置为主键约束，下图是每一列设置的数据类型和约束

创建完表，点击“保存”

2）创建成绩表（score）

同样的步骤，创建"成绩表“。“课程表的“学号”和“课程号”一起设置为主键约束（联合主键），“成绩”这一列设置为数值类型（float，浮点数值）

3）创建课程表（course）

课程表的“课程号”设置为主键约束

4）教师表（teacher）

教师表的“教师号”列设置为主键约束，

教师姓名这一列设置约束为“null”（红框的地方不勾选），表示这一列允许包含空值（null）

2.向表中添加数据

1）向学生表里添加数据

添加数据的sql

insert into student(学号,姓名,出生日期,性别) 
values('0001' , '猴子' , '1989-01-01' , '男');

insert into student(学号,姓名,出生日期,性别) 
values('0002' , '猴子' , '1990-12-21' , '女');

insert into student(学号,姓名,出生日期,性别) 
values('0003' , '马云' , '1991-12-21' , '男');

insert into student(学号,姓名,出生日期,性别) 
values('0004' , '王思聪' , '1990-05-20' , '男');

2）成绩表（score）

添加数据的sql

insert into score(学号,课程号,成绩) 
values('0001' , '0001' , 80);

insert into score(学号,课程号,成绩) 
values('0001' , '0002' , 90);

insert into score(学号,课程号,成绩) 
values('0001' , '0003' , 99);

insert into score(学号,课程号,成绩) 
values('0002' , '0002' , 60);

insert into score(学号,课程号,成绩) 
values('0002' , '0003' , 80);

insert into score(学号,课程号,成绩) 
values('0003' , '0001' , 80);

insert into score(学号,课程号,成绩) 
values('0003' , '0002' , 80);

insert into score(学号,课程号,成绩) 
values('0003' , '0003' , 80);

3）课程表

添加数据的sql

insert into course(课程号,课程名称,教师号)
values('0001' , '语文' , '0002');

insert into course(课程号,课程名称,教师号)
values('0002' , '数学' , '0001');

insert into course(课程号,课程名称,教师号)
values('0003' , '英语' , '0003');

4）教师表里添加数据

添加数据的sql

-- 教师表：添加数据
insert into teacher(教师号,教师姓名) 
values('0001' , '孟扎扎');

insert into teacher(教师号,教师姓名) 
values('0002' , '马化腾');

-- 这里的教师姓名是空值（null）
insert into teacher(教师号,教师姓名) 
values('0003' , null);

-- 这里的教师姓名是空字符串（''）
insert into teacher(教师号,教师姓名) 
values('0004' , '');

三、50道面试题

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

1.简单查询

查询姓“猴”的学生名单

查询姓“孟”老师的个数

select count(教师号)
from teacher
where 教师姓名 like '孟%';

 

2.汇总分析

查询课程编号为“0002”的总成绩

select sum(成绩)
from score
where 课程号 = '0002';

查询选了课程的学生人数

select count(distinct 学号) as 学生人数 
from score;

查询各科成绩最高和最低的分， 以如下的形式显示：课程号，最高分，最低分

select 课程号,max(成绩) as 最高分,min(成绩) as 最低分
from score
group by 课程号;

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

select 课程号, count(学号)
from score
group by 课程号;

查询男生、女生人数

select 性别,count(*)
from student
group by 性别;

 

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

select 学号, avg(成绩)
from score
group by 学号
having avg(成绩)>60;

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

select 学号, count(课程号) as 选修课程数目
from score
group by 学号
having count(课程号)>=2;

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

select 姓名,count(*) as 人数
from student
group by 姓名
having count(*)>=2;

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

select 课程号
from score 
where 成绩<60
order by 课程号 desc;

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

select 课程号, avg(成绩) as 平均成绩
from score
group by 课程号
order by 平均成绩 asc,课程号 desc;

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

select 学号
from score
where 课程号='04' and 成绩 <60
order by 成绩 desc;

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

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

select 课程号, count(学号) as '选修人数'
from score
group by 课程号
having count(学号)>2
order by count(学号) desc,课程号 asc;

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

select 学号, avg(成绩) as 平均成绩
from score
where 学号 in
(select 学号 from score where 成绩<60 group by 学号 having count(*)>2)
group by 学号;

 

3.复杂查询

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

# 第一种思路(不准确)：嵌套子查询，将所有成绩不及格转化为max(成绩)<60
# 存在的问题：存在student表中的学号由于未选课而不存在于score表，
# 导致漏选(此类学生应也属于题意范围内)，故尝试第二种思路
select 学号,姓名
from student
where 学号 in 
(select 学号 from score
group by 学号
having max(成绩)<60);

# 第二种思路(正确)：嵌套子查询，将所有成绩不及格转化为not min(成绩)>=60
select 学号,姓名
from student
where 学号 not in 
(select 学号 from score
group by 学号
having min(成绩)>=60);

# 第三种思路(正确)：嵌套子查询，将所有成绩不及格转化为not 任意成绩>=60
select s.学号, s.姓名
from student s
where s.学号 not in 
(select sc.学号 from score sc
where sc.学号=s.学号 and 成绩>=60);

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

# 第一种思路(同上，不准确)：嵌套子查询
select 学号, 姓名
from student
where 学号 in
(select 学号 from score
group by 学号
having count(*) < (select count(课程号) from course));

# 由于student表中存在学生在score里没有记录，因此上述子查询是不会返回该学生编号
# 解决办法：使用not in，或者连接查询
select 学号, 姓名
from student s
where 学号 in 
(select 学号 from score sc 
where sc.学号=s.学号 
group by 学号
having count(*) < (select count(课程号) from course));

# 第三种思路：
select 学号, 姓名
from student s
where (select count(*) from score sc where sc.学号=s.学号)
< (select count(课程号) from course));

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

select 学号, 姓名
from student
where 学号 in
(select 学号 from score
group by 学号
having count(课程号)=2);

1990年出生的学生名单

select 学号,姓名 
from student 
where year(出生日期)=1990; 

查询成绩表中每个课程的最大成绩/最小成绩及对应课程和学生

这类问题其实就是常见的：分组取每组最大值、最小值，每组最大的N条（top N）记录。

# 注意可能存在学科最大成绩对应多个学生
# 第一种思路：先查询到每个课程的最高成绩，再通过join连表查询
select 学号, 课程号, 成绩
from score sc, (select 课程号, max(成绩) as 最大成绩 from score group by 课程号) temp
where sc.成绩=temp.最大成绩 and sc.课程号=temp.课程号;

# 第二种思路：子查询
select *
from score sc1
where sc1.成绩=
(select max(成绩) from score sc2 where sc2.课程号=sc1.课程号);
# 或者使用having
select *
from score sc1
where sc1.成绩=
(select max(成绩) from score sc2 
group by sc2.课程号 having sc2.课程号=sc1.课程号);

此处补充group by 和 having 的理解：

（1）GROUP BY 是分组查询, 一般 GROUP BY 是和聚合函数配合使用

分组函数：avg([distinct]字段名)、count({*[distinct]字段名})、max([distinct]字段名)、min([distinct]字段名)  sum([distinct]字段名)

having 分组函数允许使用别名

注意：select 后的字段，必须要么包含在group by中，要么包含在having 后的聚合函数里。

group by 有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面（重要）。例如：

select A,B from table group by A  （错误！）

select A,count(B) as 数量 from table group by A （正确！）

（2）HAVING使用

where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。

having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件显示特定的组，也可以使用多个分组标准进行分组。

having 子句被限制子已经在SELECT语句中定义的列和聚合表达式上。通常，你需要通过在HAVING子句中重复聚合函数表达式来引用聚合值，就如你在SELECT语句中做的那样。例如：

SELECT A, COUNT(B) FROM TABLE GROUP BY A HAVING COUNT(B)>2 （正确！）

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

# Top N问题解法：使用窗口函数rank/dense_rank/row_number()
select *
from (select *, dense_rank() over (partition by 课程号 order by 成绩 desc) as ranking 
from score) temp
where temp.ranking < 3;

 

4.多表查询

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

select s.学号, s.姓名, count(sc.课程号) as 选课数, sum(sc.成绩) as 总成绩
from student as s left join score as sc
on s.学号 = sc.学号
group by s.学号;

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

select s.学号, s.姓名, avg(sc.成绩) as 平均成绩
from student as s left join score as sc
on s.学号 = sc.学号
group by s.学号
having avg(sc.成绩)>85;

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

select s.学号, s.姓名, c.课程号, c.课程名称
from student s inner join score sc on s.学号=sc.学号
inner join course c on sc.课程号=c.课程号;

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

-- 考察case表达式
select 课程号,
sum(case when 成绩>=60 then 1 
	 else 0 
    end) as 及格人数,
sum(case when 成绩 <  60 then 1 
	 else 0 
    end) as 不及格人数
from score
group by 课程号;

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

-- 考察case表达式
select sc.课程号, c.课程名称,
sum(case when 成绩 between 85 and 100 
	 then 1 else 0 end) as '[100-85]',
sum(case when 成绩 >=70 and 成绩<85 
	 then 1 else 0 end) as '[85-70]',
sum(case when 成绩>=60 and 成绩<70  
	 then 1 else 0 end) as '[70-60]',
sum(case when 成绩<60 then 1 else 0 end) as '[<60]'
from score as sc join course as c
on sc.课程号=c.课程号
group by sc.课程号;

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

select s.学号, s.姓名
from student as s inner join score as sc on s.学号=sc.学号
where sc.课程号='0003' and sc.成绩>80;

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

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

select 学号, 
max(case 课程号 when '0001' then 成绩 else 0 end) as '课程号0001',
max(case 课程号 when '0002' then 成绩 else 0 end) as '课程号0002',
max(case 课程号 when '0003' then 成绩 else 0 end) as '课程号0003'
from score
group by 学号;