Sql面试50题

前言：此文章是根据【数据分析】- SQL面试50题 - 跟我一起打怪升级 一起成为数据科学家

学习整理而来，仅供复习参考。

建表与插入数据

--建立学生表
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);

--查询学生表所有数据
SELECT *
FROM student

1、查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号（重点）

• 涉及的表：score，子查询
• 使用的关键字：SELECT,FROM,INNER JOIN,ON,AS

解题思路：

构建如图的表格，使用简单查询即可得到结果。

为构建图中表格，首先需要单独查询到选修课程01的学生id与成绩，然后得到02课程的学生id与成绩。使用两个子查询；

然后**以s_id为连接条件，通过内连接求交集即可得到图下的表。**之后在查询01课程成绩大于02课程的学生id即可，如需要查询学生具体信息，则需要再与学生表进行内连接，以s_id为连接条件即可。

SQL语句：

SELECT a.s_id,a.s_score "01",b.s_score "02"
FROM (SELECT * FROM score WHERE c_id='01') AS a
INNER JOIN(SELECT * FROM score WHERE c_id='02') AS b ON a.s_id=b.s_id
WHERE a.s_score>b.s_score

查询结果：

2、查询平均成绩大于60分的学生的学号和平均成绩（简单，第二道重点）

• 涉及的表：score
• 使用的关键字：SELECT,FROM,INNER JOIN,ON,AS

解题思路：

此题目考察的是对 GROUP BY 语句的使用与理解，首先需要对成绩表按照学生id进行分组，然后使用函数avg统计出平均成绩，再进行条件判断即可。

注意：

SELECT 中查询的字段最好是GROUP BY中使用到的字段或者是统计函数，不然的话可能没有意义。

SQL语句：

--2、查询平均成绩大于60分的学生的学号和平均成绩（简单，第二道重点）
SELECT s_id,AVG(s_score)
FROM score
GROUP BY s_id
HAVING AVG(s_score)>60

查询结果：

---

5/28更新

3、查询所有学生的学号、姓名、选课数、总成绩（不重要）

• 涉及的表：score,student
• 使用的关键字：SELECT,FROM,INNER JOIN,ON,AS

解题思路：

1. 先把学生表和成绩表进行左连接，这里需要使用左连接，这样不会丢失学生表中的数据；

2. 利用GROUP BY 和 COUNT,SUM 获取学生所选的选课数，总成绩。

3. 需要对总成绩为null的情况进行单独处理，让其显示0，这里可以有两种处理方式，一种是IFNULL ，另一个是 case when

   这里可以使用 IFNULL 来进行判断，如果第一个表达式值为空则返回后面的值，即0，如果不为空，则返回第一个表达式的值

SELECT a.s_id,a.s_name,COUNT(c_id),IFNULL(SUM(s_score),0)
FROM  student AS a  LEFT JOIN score AS b 
ON a.s_id=b.s_id
GROUP BY s_id

​ 也可也使用 case when 来进行判断

SELECT a.s_id,a.s_name,COUNT(c_id),
SUM(CASE WHEN s_score IS NULL THEN 0 ELSE s_score END) AS "总成绩"
FROM  student AS a  LEFT JOIN score AS b 
ON a.s_id=b.s_id
GROUP BY s_id

查询结果：

​

4、查询姓“张”的老师的个数（不重要）

• 涉及的表：teacher
• 主要的关键字：LIKE

解题思路：

使用 LIKE 关键字进行查询匹配即可。

• %张 表示结尾带张的字符串
• %张% 表示查询中间带张的姓名
• 张% 表示以张开头的字符串

SELECT COUNT(t_id)
FROM teacher
WHERE t_name LIKE'张%'

--查询不重名的姓张老师的个数
SELECT COUNT(DISTINCT t_name)
FROM teacher
WHERE t_name LIKE'张%'

查询结果： 结果为 1

5、查询没学过“张三”老师课的学生的学号、姓名（重点⭐）

• 涉及的表：course,teacher,student,score
• 主要的关键字：LIKE

解题思路：

一个老师可能会教多门课程，本题目的背景是成绩表中有成绩表示学生选择了这门课程。

1. 首先需要在课程表中查询到张三老师教的所有课程， 获取这些课程的课程号;
2. 然后可以在成绩表中进行查询，获取选修过张三老师课程的学生的学号；
3. 在学生表中进行查询，使用 not in ，查询学号不在第二步选修了张三老师课程学号的学号，即可得到结果。

注意：使用子查询效率会低一点，使用临时表会比较多一点。

​ 尝试直接查询没有选修的学号，后来发现因为一个学生会选择多门课程，不好排除，因此先查询选修过的同学，在查询没有选修的同学。

SQL语句：

多增加了一门课程 和成绩，方便测试。

 INSERT INTO Course VALUES('04','JAVA','01');
 INSERT INTO Score VALUES('01' , '04' , 100);

获取张三老师教的所有课程号。

SELECT c_id
FROM course AS a 
INNER JOIN (SELECT t_id  FROM teacher  WHERE t_name='张三') AS b
ON a.t_id=b.t_id

获取学过了张三老师任意一门课程的学生学号，使用 DISTINCT 对学号去重，因为一个学生可能会选择多门课程。

SELECT DISTINCT s_id
FROM score
WHERE c_id IN(
SELECT c_id
FROM course AS a 
INNER JOIN (SELECT t_id  FROM teacher  WHERE t_name='张三') AS b
ON a.t_id=b.t_id
)

查询不在选修了的学号里的其他人，即没有学过张三老师课程的同学。

SELECT s_id,s_name FROM student
WHERE s_id NOT IN(
SELECT DISTINCT s_id
FROM score
WHERE c_id IN(
SELECT c_id
FROM course AS a 
INNER JOIN (SELECT t_id  FROM teacher  WHERE t_name='张三') AS b
ON a.t_id=b.t_id)
)

查询结果：

6、查询学过“张三”老师所教的所有课的同学的学号、姓名（重点）

• 涉及的表：course,teacher,student,score
• 主要的关键字：LIKE

解题思路：

这题第一感觉可能是对第五题题目进行取反，仔细看题发现要求的是学过张三老师教的所有课程。

一个老师可能会教多门课程，本题目的背景是成绩表中有成绩表示学生选择了这门课程。

1. 先把所有的表进行内关联，得到一张大表；
2. 查询老师姓名等于张三的信息，行；
3. 统计出张三老师一共教了多少门课程 例题中为2；
4. 按照学生id统计出学生的行数 即 学生所选修张三老师课程的数目；
5. 查询选修课数等于张三老师教的所有课程数目的学生信息。

注意：

​ 个人感觉视频中的写法是错误的，题目要求的是学过张三教的所有课，视频的写法只考虑了张三只教一门课的方法，如下图，当张三教了两门课程的时候，按照题目意思应该只查出01号学生雷

SELECT *
FROM student AS s 
INNER JOIN score AS a ON s.s_id=a.s_id
INNER JOIN course AS b ON a.c_id=b.c_id
INNER JOIN teacher AS c ON b.t_id=c.t_id
WHERE t_name='张三'
ORDER BY s.s_id

SQL语句：

SELECT s.s_id,COUNT(s.s_id),s.s_name
FROM student AS s 
INNER JOIN score AS a ON s.s_id=a.s_id
INNER JOIN course AS b ON a.c_id=b.c_id
INNER JOIN teacher AS c ON b.t_id=c.t_id
WHERE t_name='张三'
GROUP BY s.s_id
HAVING COUNT(s.s_id)=(
SELECT COUNT(*) FROM course 
WHERE t_id=(SELECT t_id FROM teacher WHERE t_name='张三')
)

查询结果：