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='张三')
)

查询结果

在这里插入图片描述

Logo

华为云1024程序员节送福利,参与活动赢单人4000元礼包,更有热门技术干货免费学习

更多推荐