SQL面试必会50题-个人练习总结

最新推荐文章于 2024-02-28 19:36:53 发布

今天我伏笔了吗

最新推荐文章于 2024-02-28 19:36:53 发布

阅读量172

点赞数

分类专栏： SQL 文章标签： sql 面试数据库

本文链接：https://blog.csdn.net/weixin_43499803/article/details/119952756

版权

SQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

文章目录

题目来源：知乎@小番茄

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

关键点-利用子查询构造临时表如下：

s_id	score(01)	score(02)
01	90	80

SELECT a.s_id, s.s_name, a.s_score, b.s_score
 from 
 (SELECT s_id, s_score from score WHERE c_id='01') as a
  INNER JOIN
 (SELECT s_id, s_score from score WHERE c_id='02') as b
 on a.s_id = b.s_id
 INNER JOIN student as s on a.s_id = s.s_id
WHERE a.s_score > b.s_score;

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

SELECT a.s_id, a.s_name, count(DISTINCT b.c_id) count, sum(case when b.s_score is null then 0 else b.s_score end) sum
  from student a 
	left join score b on a.s_id = b.s_id
	GROUP BY a.s_id, a.s_name

在这里插入图片描述

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

使用多层嵌套子查询,多表连接跳跃.
先找学过张三课的,不能直接用!=

-- 查询没学过“张三”老师课的学生的学号、姓名（重点）
SELECT s_id,s_name FROM student WHERE s_id NOT IN
(SELECT s_id FROM score WHERE c_id = 
 (SELECT c_id FROM course WHERE t_id = 
  (SELECT t_id FROM teacher WHERE t_name = '张三')))

在这里插入图片描述

方法二多表连接

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

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

SELECT s_id, s_name
FROM student
WHERE s_id IN
(SELECT s_id
  FROM score 
	WHERE c_id = 01)
AND s_id IN
(SELECT s_id
  FROM score 
	WHERE c_id = 02)

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

可以不使用GROUP BY

SELECT SUM(s_score)
	FROM score 
	WHERE c_id = 02

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

SELECT s_id, s_name
FROM student
WHERE s_id NOT IN
(SELECT DISTINCT s_id
  FROM score
	WHERE s_score >= 60)
	AND s_id IN
(SELECT s_id FROM score)

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

思路：每个学生（分组）学过的课程数量小于课程的总数，注意要用student表左连接以防止有的人在course表没有记录（王菊）

SELECT a.s_id, a.s_name
  FROM student a
	LEFT JOIN score b
	on a.s_id = b.s_id
	GROUP BY a.s_id
	HAVING COUNT(DISTINCT b.c_id) < (SELECT COUNT(DISTINCT c_id) FROM course)

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

SELECT DISTINCT a.s_id,  a.s_name
  FROM student a
	INNER JOIN score b
	on a.s_id = b.s_id
	WHERE b.c_id IN
	(SELECT c_id from score WHERE s_id=01)
	 AND a.s_id != 01

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

满足两个条件：

排除：有课和01的课不相同
选课数和01相同

SELECT s_id 
  FROM score 
  WHERE s_id != '01'
  GROUP BY s_id 
	HAVING COUNT(DISTINCT c_id) = 
	  (SELECT COUNT(DISTINCT c_id) from score WHERE s_id='01') 
	  AND s_id NOT IN
		(SELECT DISTINCT s_id FROM score WHERE c_id NOT IN 
		   (SELECT c_id from score WHERE s_id='01') )

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

SELECT a.s_id, a.s_name, AVG(b.s_score)
  FROM student a
	  INNER JOIN score b on a.s_id = b.s_id
	  INNER JOIN 
		  (SELECT s_id FROM score WHERE s_score < 60 
			   GROUP BY s_id HAVING COUNT(DISTINCT s_score) >= 2) c
	    on a.s_id = c.s_id
	GROUP BY s_id, s_name

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

SELECT a.*, s_score
  FROM student a
	  INNER JOIN score b on a.s_id = b.s_id
	WHERE s_score < 60 and c_id = '01'
	ORDER BY s_score DESC

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

利用聚合函数 + case when 实现行转列

SELECT s_id,
  MAX(CASE WHEN c_id = '01' THEN s_score ELSE NULL END) '01',
	MAX(CASE WHEN c_id = '02' THEN s_score ELSE NULL END) '02',
	MAX(CASE WHEN c_id = '03' THEN s_score ELSE NULL END) '03',
	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 sc.c_id,
  c.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) '及格率'
FROM score sc
  INNER JOIN course c on sc.c_id = c.c_id
GROUP BY c_id

在这里插入图片描述

19、按各科成绩进行排序，并显示排名

窗口函数

rank 跳跃排序 1224
dense_rank 连续排序 1223
row_number 不重复排序 1234
-（好处）返回的内容不发生变化

SELECT *, RANK() over(PARTITION by c_id ORDER BY s_score DESC) '排名'
FROM score

在这里插入图片描述

20、查询学生的总成绩并进行排名（不重点）

SELECT *, sum(s_score), RANK() over(ORDER BY sum(s_score) DESC) '排名'
FROM score
GROUP BY s_id

在这里插入图片描述

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

SELECT *
FROM 
  (SELECT st.*, c_id, s_score, ROW_NUMBER() over(PARTITION by sc.c_id ORDER BY s_score DESC) m
	   FROM score sc INNER JOIN student st on sc.s_id = st.s_id) a
WHERE m in (2,3)

在这里插入图片描述

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

SELECT c_id,
       SUM(CASE WHEN s_score < 60 THEN 1 ELSE 0 END) '不及格', 
			 SUM(CASE WHEN s_score >= 60 AND s_score < 80 THEN 1 ELSE 0 END) '60-80'
			 SUM(CASE WHEN s_score >= 80 AND s_score < 100 THEN 1 ELSE 0 END) '80-100'
  FROM score
	GROUP BY c_id

在这里插入图片描述

31、查询1990年出生的学生名单（重点year）

SELECT *
FROM student
WHERE YEAR(s_birth) = 1990

在这里插入图片描述

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

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

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

SELECT sc.s_id,
  MAX(CASE WHEN c.c_name = '语文' THEN sc.s_score END) '语文',
	MAX(CASE WHEN c.c_name = '数学' THEN sc.s_score END) '数学',
	MAX(CASE WHEN c.c_name = '英语' THEN sc.s_score END) '英语',
	MAX(CASE WHEN c.c_name = '化学' THEN sc.s_score END) '化学'
FROM score sc INNER JOIN course c on sc.c_id = c.c_id
GROUP BY sc.s_id

在这里插入图片描述

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

SELECT s_name, s_score
FROM student st
  INNER JOIN score sc on st.s_id = sc.s_id
	INNER JOIN course c on sc.c_id = c.c_id
	INNER JOIN teacher t on c.t_id = t.t_id
WHERE t_name = '张三'
ORDER BY s_score DESC
LIMIT 0,1

在这里插入图片描述

今天我伏笔了吗

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQL面试必会50题-个人练习总结

1.查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号（重点）关键点-利用子查询构造临时表如下：s_idscore(01)score(02)019080SELECT a.s_id, s.s_name, a.s_score, b.s_score from (SELECT s_id, s_score from score WHERE c_id='01') as a INNER JOIN (SELECT s_id, s_score from scor
复制链接

扫一扫