SQL面试必会50题

表结构

学生表：
Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别
课程表：
Course(c_id,c_name,t_id) –课程编号, 课程名称, 教师编号
教师表：
Teacher(t_id,t_name) –教师编号,教师姓名
成绩表：
Score(s_id,c_id,s_s_score) –学生编号,课程编号,分数

建表

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

配合B站视频学习

只实现功能，不关心性能。

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

SELECT t1.s_id FROM ( 
	(SELECT s_id, s_score AS class1 FROM score WHERE c_id = '01') t1,
	(SELECT s_id, s_score AS class2 FROM score WHERE c_id = '02') t2
) WHERE t1.s_id = t2.s_id AND class1 > class2


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


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

SELECT s_id, AVG(s_score) as avg FROM score
GROUP BY s_id
HAVING avg > 60


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

SELECT a.s_id, b.s_name, cnt, sum FROM (
	SELECT s_id, COUNT(c_id) AS cnt, SUM(s_score) AS sum
	FROM score
	GROUP BY s_id
) a INNER JOIN student AS b ON a.s_id = b.s_id


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


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

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


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

-- 坏了，题看错了，我以为是没过“张三”老师的课
SELECT student.s_id, student.s_name FROM (
	SELECT a.t_name, c_id FROM
	teacher AS a INNER JOIN course AS b
	ON a.t_id = b.t_id
	WHERE a.t_name = '张三'
) t1 INNER JOIN score ON t1.c_id = score.c_id
INNER JOIN student ON score.s_id = student.s_id
WHERE score.s_score < 60


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


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

SELECT student.s_id, student.s_name FROM student
INNER JOIN (
	SELECT s_id FROM score
	WHERE c_id = (
		SELECT c_id FROM 
		teacher INNER JOIN course ON teacher.t_id = course.t_id
		WHERE teacher.t_name = '张三'
	) 
) t1 ON student.s_id = t1.s_id


-- 7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名（重点）

SELECT student.s_id, student.s_name FROM 
(SELECT s_id FROM score WHERE c_id = '01') AS t1
INNER JOIN (SELECT s_id FROM score WHERE c_id = '02') AS t2
ON t1.s_id = t2.s_id
INNER JOIN student
ON t2.s_id = student.s_id


-- 8、查询课程编号为“02”的总成绩（不重点）

SELECT SUM(s_score) FROM score
WHERE c_id = '02'


SELECT SUM(s_score) FROM score
GROUP BY c_id HAVING c_id = '02'


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

SELECT a.s_id, c.s_name FROM (
	SELECT s_id, COUNT(s_score) AS cnt FROM score
	WHERE s_score < 60
	GROUP BY s_id
) AS a
INNER JOIN (
	SELECT s_id, COUNT(s_score) AS cnt FROM score
	GROUP BY s_id
) AS b
ON a.s_id = b.s_id
INNER JOIN student AS c ON b.s_id = c.s_id
WHERE a.cnt = b.cnt


-- 10.查询没有学全所有课的学生的学号、姓名(重点)

-- 有问题会漏掉一门都没有选的人
SELECT student.s_id, student.s_name FROM (
	SELECT s_id, COUNT(DISTINCT c_id) AS cnt FROM score
	GROUP BY s_id 
	HAVING cnt < (SELECT COUNT(DISTINCT c_id) AS cnt FROM course)
) t1 INNER JOIN student
ON t1.s_id = student.s_id


-- 11、查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名（重点）

SELECT student.s_id, student.s_name FROM (
	select DISTINCT s_id FROM score
	WHERE c_id IN (
		select c_id FROM score
		WHERE s_id = '01'
	) AND s_id <> '01'
) AS a
INNER JOIN student
ON a.s_id = student.s_id

-- 12.查询和“01”号同学所学课程完全相同的其他同学的学号(重点)

SELECT * FROM student
WHERE s_id IN (
	SELECT s_id FROM score
	WHERE s_id <> '01'
	GROUP BY s_id HAVING COUNT(c_id) = (SELECT count(c_id) FROM score WHERE s_id ='01')
) AND s_id NOT IN (
SELECT s_id FROM score 
	WHERE c_id NOT IN (
		SELECT c_id FROM score
		WHERE s_id = '01'
	)
	GROUP BY s_id
)


-- 13、查询没学过"张三"老师讲授的任一门课程的学生姓名 和47题一样（重点，能做出来）

SELECT * FROM student
WHERE s_id NOT IN (
	SELECT s_id FROM (
		SELECT c_id FROM teacher
		INNER JOIN course
		ON teacher.t_id = course.t_id
		WHERE teacher.t_name = '张三'
	) t1 INNER JOIN score ON t1.c_id = score.c_id
)


-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩（重点）

SELECT student.s_id, student.s_name, t1.avg FROM student
INNER JOIN (
	SELECT s_id, AVG(s_score) AS avg FROM score
	WHERE s_score < 60
	GROUP BY s_id
	HAVING COUNT(c_id) >= 2
) t1 ON student.s_id = t1.s_id


-- 16、检索"01"课程分数小于60，按分数降序排列的学生信息（和34题重复，不重点）

SELECT student.* FROM score
INNER JOIN student
ON score.s_id = student.s_id
WHERE c_id = '01' AND s_score < 60
ORDER BY s_score DESC


-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩(重重点与35一样)

SELECT 
s_id "学号", 
MAX(CASE WHEN c_id = '01' THEN s_score ELSE NULL END) "语文",
MAX(CASE WHEN c_id = '02' THEN s_score ELSE NULL END) "数学",
MAX(CASE WHEN c_id = '03' THEN s_score ELSE NULL END) "英语",
AVG(s_score) "平均分"
FROM score
GROUP BY s_id
ORDER BY AVG(s_score) DESC

-- 18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

-- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 (超级重点)

SELECT 
course.c_id "课程ID", 
course.c_name "课程名称", 
MAX(s_score) "最高分", 
MIN(s_score) "最低分", 
AVG(s_score) "平均分",
SUM(CASE WHEN s_score >= 60 THEN 1 ELSE 0 END) / COUNT(s_id) "及格率",
SUM(CASE WHEN s_score >= 70 AND s_score < 80 THEN 1 ELSE 0 END) / COUNT(s_id) "中等率",
SUM(CASE WHEN s_score >= 80 AND s_score < 90 THEN 1 ELSE 0 END) / COUNT(s_id) "优良率",
SUM(CASE WHEN s_score >= 90 THEN 1 ELSE 0 END) / COUNT(s_id) "优秀率"
FROM score 
INNER JOIN course 
ON score.c_id = course.c_id
GROUP BY score.c_id


-- 19、按各科成绩进行排序，并显示排名(重点row_number)

SELECT *, ROW_NUMBER() over(PARTITION BY c_id ORDER BY s_score DESC) AS 'rank' FROM score

 
-- 20、查询学生的总成绩并进行排名（不重点）
SELECT student.s_id, student.s_name, SUM(s_score) AS sum FROM score
INNER JOIN student
ON score.s_id = student.s_id
GROUP BY s_id
ORDER BY sum DESC

-- 21 、查询不同老师所教不同课程平均分从高到低显示(不重点)


-- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩（重要 25类似）


SELECT * FROM (
	SELECT *, ROW_NUMBER() OVER(PARTITION BY c_id ORDER BY s_score DESC) AS rn FROM score
) t1 WHERE t1.rn IN (2, 3)



-- 23、使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称(重点和18题类似)

SELECT 
course.c_id, 
course.c_name,
SUM(CASE WHEN s_score < 60 THEN 1 ELSE 0 END) a,
SUM(CASE WHEN s_score >= 60 and s_score < 70 THEN 1 ELSE 0 END) b,
SUM(CASE WHEN s_score >= 70 and s_score < 85 THEN 1 ELSE 0 END) c,
SUM(CASE WHEN s_score >= 85 THEN 1 ELSE 0 END) d
FROM score
INNER JOIN course ON score.c_id = course.c_id
GROUP BY c_id

-- 24、查询学生平均成绩及其名次（同19题，重点）

SELECT student.s_id, AVG(score.s_score) AS avg, ROW_NUMBER() OVER(ORDER BY AVG(score.s_score) DESC) AS 'rank' FROM score
INNER JOIN student ON score.s_id = student.s_id
GROUP BY s_id

-- 25、查询各科成绩前三名的记录（不考虑成绩并列情况）（重点 与22题类似）

SELECT * FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY c_id ORDER BY s_score DESC) AS RN FROM score
) t1
WHERE t1.RN IN (1, 2, 3)


-- 26、查询每门课程被选修的学生数(不重点)

SELECT course.c_id, course.c_name, COUNT(score.s_id) AS cnt FROM course
INNER JOIN score ON course.c_id = score.c_id
GROUP BY score.c_id


-- 27、 查询出只有两门课程的全部学生的学号和姓名(不重点)

SELECT student.s_id, student.s_name FROM student
INNER JOIN (
	SELECT s_id, COUNT(c_id) AS cnt FROM score
	GROUP BY s_id
) t1 ON student.s_id = t1.s_id
WHERE cnt = 2


-- 28、查询男生、女生人数(不重点)

SELECT s_sex, COUNT(s_id) FROM student
GROUP BY s_sex


-- 29 查询名字中含有"风"字的学生信息（不重点）

SELECT * FROM student
WHERE s_name LIKE '%风%'


-- 32、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩（不重要）

SELECT student.s_id, student.s_name, AVG(s_score) FROM score
INNER JOIN student ON score.s_id = student.s_id
GROUP BY s_id


-- 33、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列（不重要）

SELECT c_id, AVG(s_score) AS avg FROM score
GROUP BY c_id
ORDER BY avg, c_id DESC


-- 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数（不重点）

SELECT student.s_name, score.s_score FROM score
INNER JOIN student ON score.s_id = student.s_id
WHERE c_id = (SELECT c_id FROM course WHERE c_name = '数学') AND s_score < 60

-- 35、查询所有学生的课程及分数情况（重点）

SELECT 
score.s_id,
MAX(CASE WHEN course.c_name = '语文' THEN score.s_score ELSE NULL END) "语文"
FROM score 
INNER JOIN course
ON score.c_id = course.c_id
GROUP BY s_id



-- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数（重点）

SELECT student.s_name,course.c_name, score.s_score FROM score
INNER JOIN course ON score.c_id = course.c_id
INNER JOIN student ON score.s_id = student.s_id
WHERE s_score > 70


-- 37、查询不及格的课程并按课程号从大到小排列(不重点)

SELECT * FROM score
WHERE s_score < 60
ORDER BY s_id DESC


-- 38、查询课程编号为03且课程成绩在80分以上的学生的学号和姓名（不重要）

SELECT * FROM score
INNER JOIN student
WHERE c_id = '03' AND s_score > 80


-- 40、查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩（重要top）


SELECT student.s_name, score.s_score FROM score
INNER JOIN student ON score.s_id = student.s_id
WHERE c_id = (
	SELECT c_id FROM teacher
	INNER JOIN course
	ON teacher.t_id = course.t_id
	WHERE t_name = '张三'
)
ORDER BY s_score DESC
LIMIT 1


-- 41.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 （重点）

-- 42、查询每门功成绩最好的前两名（同22和25题）

SELECT * FROM student
INNER JOIN (
	SELECT *, ROW_NUMBER() OVER(PARTITION BY c_id ORDER BY s_score DESC) AS rk 
	FROM score
) t1 ON student.s_id = t1.s_id
WHERE rk <= 2


-- 43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，
-- 查询结果按人数降序排列，若人数相同，按课程号升序排列（不重要）

SELECT c_id, COUNT(s_id) FROM score 
GROUP BY c_id


-- 44、检索至少选修两门课程的学生学号（不重要）

SELECT s_id, COUNT(c_id) AS cnt FROM score
GROUP BY s_id
HAVING cnt >= 2


-- 45、 查询选修了全部课程的学生信息（重点划红线地方）

SELECT s_id, COUNT(c_id) AS cnt FROM score
GROUP BY s_id
HAVING cnt = (SELECT COUNT(*) FROM course)


-- 47、查询没学过“张三”老师讲授的任一门课程的学生姓名（还可以，自己写的，答案中没有）

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


-- 48、查询两门以上不及格课程的同学的学号及其平均成绩（还可以，自己写的，答案中没有）

SELECT s_id, AVG(s_score) AS avg FROM score
WHERE s_score < 60
GROUP BY s_id


-- 49、查询各学生的年龄（精确到月份）

SELECT s_name, DATEDIFF('2021-7-24',s_birth) / 365 AS age -- 不考虑闰年
FROM student


-- 50、查询本月过生日的学生	

SELECT s_name FROM student
WHERE MONTH(s_birth) = MONTH(DATE(NOW()))