SQL经典50题

余月七 2023-05-25 原文

参考内容

首先这篇记录是根据下面B站的视频进行的简单记录！
因为是自己个人写的，所以有些SQL是自己的想法，可能会与视频中有出入，但个人认为有些SQL有几种写法，自己喜欢哪种写哪种，而且视频中只是讲了这些题目的实现逻辑，实际当中肯定要更加规范一点。
以下内容中如有错误，请各位大佬及时指出，并请不吝赐教！

SQL面试50题

视频中参考文章：

图解SQL面试题：经典50题

【SQL】SQL经典50题&答案

数据库

下载这个SQL文件新建一个数据库直接导入即可，sql语句太长就不往这放了

sql50.sql

50题

有些例题比较简单可以略过！

1.查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数

SELECT
	Student.*,
	01_score.CId,
	1_score,
	02_score.CId,
	2_score 
FROM
	Student
	JOIN ( SELECT SId, CId, score AS 1_score FROM SC WHERE CId = '01' ) AS 01_score ON Student.SId = 01_score.SId
	JOIN ( SELECT SId, CId, score AS 2_score FROM SC WHERE CId = '02' ) AS 02_score ON 01_score.SId = 02_score.SId 
WHERE
	1_score > 2_score

2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

SELECT
	sc.SId,
	avg( sc.score ) 
FROM
	sc 
GROUP BY
	sc.SId 
HAVING
	avg( sc.score )> 60

3.(略)查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

SELECT
	a.Sid,
	count( sc.Cid ),
	IFNULL( sum( sc.score ), 0 ) 
  -- sum( CASE WHEN sc.score IS NULL THEN 0 ELSE sc.score END )
FROM
	student AS a
	LEFT JOIN sc ON sc.SId = a.Sid 
GROUP BY
	a.Sid

4.(略)查询姓“张”的老师个数

SELECT
	count( Tid ) 
FROM
	teacher 
WHERE
	Tname LIKE '张%'

5.查询没学过张三老师课的学生的学号和姓名

SELECT
	student.SId,
	student.sname 
FROM
	student 
WHERE
	student.sid NOT IN (
	SELECT
		sc.SId 
	FROM
		sc
		INNER JOIN course ON sc.Cid = course.CId
		INNER JOIN teacher ON course.TId = teacher.TId 
	WHERE
	teacher.Tname = '张三' 
	)

6.查询学过张三老师所教的所有课程的同学的学号和姓名

SELECT
	student.SId,
	student.sname,
	course.Cname,
	teacher.Tname 
FROM
	sc
	INNER JOIN course ON sc.Cid = course.CId
	INNER JOIN teacher ON course.TId = teacher.TId
	INNER JOIN student ON sc.SId = student.sid 
WHERE
	teacher.Tname = '张三' 
ORDER BY
	student.sid

7.查询学过01课程并且学过02课程的学生

SELECT
	* 
FROM
	student 
WHERE
	student.sid IN (
	SELECT
		a.sid 
	FROM
		( SELECT sid FROM sc WHERE sc.CId = '01' ) AS a
	INNER JOIN ( SELECT sid FROM sc WHERE sc.CId = '02' ) AS b ON a.sid = b.sid 
	)

8.(略)查询学过02课程的总成绩

SELECT
	sum( sc.score ) 
FROM
	sc 
WHERE
	sc.cid = '02'

9.查询所有课程成绩小于60的学生

SELECT
	a.sid,
	student.sname
FROM
	( SELECT sc.sid, count( sc.CId ) AS acount FROM sc WHERE sc.score < 60 GROUP BY sc.sid )
	AS a
	INNER JOIN 
	( SELECT sc.sid, count( sc.CId ) AS bcount FROM sc GROUP BY sc.sid ) 
	AS b ON a.sid = b.sid 
	inner join student on student.sid = a.sid
WHERE
	a.acount = b.bcount

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

SELECT
	student.sid,
	student.sname 
FROM
	student
	LEFT JOIN sc ON sc.sid = student.sid 
GROUP BY
	student.sid 
HAVING
	count( DISTINCT sc.cid ) < (SELECT count( cid ) FROM course)

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

SELECT
	student.sid,
	student.sname 
FROM
	student
	INNER JOIN (
	SELECT DISTINCT
		sc.SId 
	FROM
		sc 
	WHERE
		sc.CId IN ( SELECT sc.cid FROM sc WHERE sc.SId = '01' ) 
	AND sc.SId != '01' 
	) AS a ON a.SId = student.sid

12.查询和“01”学号学生所学课程完全相同的学生学号

SELECT
	* 
FROM
	student 
WHERE
	SId IN (SELECT SId 
			FROM
		(SELECT	* FROM	sc a WHERE CId IN ( SELECT CId FROM sc WHERE SId = 01 )) b 
	GROUP BY SId 
	HAVING
		count( CId ) =(SELECT count( CId ) FROM sc c WHERE SId = 01 )) 
	AND SId != 01

13.查询没学过"张三"老师讲授的任一门课程的学生姓名

SELECT
	student.sid,
	student.sname 
FROM
	student 
WHERE
	student.sid NOT IN (
	SELECT
		sc.sid 
	FROM
		sc
		INNER JOIN course ON sc.CId = course.CId
		INNER JOIN teacher ON course.tid = teacher.TId 
	AND teacher.Tname = '张三' 
	)

14. 15.查询两门及其以上不及格的同学的学号、姓名和平均成绩

SELECT
	sc.sid,
	student.sname,
	avg( sc.score ) 
FROM
	sc
	INNER JOIN student ON student.sid = sc.sid 
WHERE
	sc.score < 60 GROUP BY sid HAVING count( DISTINCT sc.cid ) >=2

16.(略)检索01课程分数小于60 ，按课程分数降序排列学生信息

SELECT
	student.sid,
	student.sname 
FROM
	sc
	INNER JOIN student ON sc.sid = student.sid 
WHERE
	sc.cid = '01' 
	AND sc.score < 60 
ORDER BY
	sc.score DESC

17.按平均成绩从高到低显示所有学生的所有课程的成绩和平均成绩

SELECT
	sc.sid,
	max( CASE WHEN sc.cid = '01' THEN sc.score ELSE NULL END ) '语文',
	max( CASE WHEN sc.cid = '02' THEN sc.score ELSE NULL END ) '数学',
	max( CASE WHEN sc.cid = '03' THEN sc.score ELSE NULL END ) '英语',
	avg( sc.score ) 
FROM
	sc 
GROUP BY
	sc.sid 
ORDER BY
	avg( sc.score ) DESC

18.查询各科成绩最高分、最低分、平均分、及格率、中等率、优良率、优秀率

及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

SELECT
	course.cid,
	course.Cname,
	max( sc.score ) '最高分',
	min( sc.score ) '最低分',
	avg( sc.score ) '平均分',
	sum( CASE WHEN sc.score >= 60 THEN 1 ELSE 0 END )/ count( sc.SId ) '及格',
	sum( CASE WHEN sc.score >= 70 AND sc.score < 80 THEN 1 ELSE 0 END )/ count( sc.SId ) '中等',
	sum( CASE WHEN sc.score >= 80 AND sc.score < 90 THEN 1 ELSE 0 END )/ count( sc.SId ) '良好',
	sum( CASE WHEN sc.score >= 90 THEN 1 ELSE 0 END )/ count( sc.SId ) '优秀' 
FROM
	course
	INNER JOIN sc ON sc.cid = course.cid 
GROUP BY
	course.cid

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

-- 窗口函数
https://blog.csdn.net/hyazz_/article/details/95052222

SELECT
	sc.sid,
	sc.cid,
	sc.score,
	ROW_NUMBER() over ( ORDER BY sc.score DESC ) 
FROM
	sc

20.(略)查询学生的总成绩并进行排名

SELECT
	sc.SId,
	sum( sc.score ) 
FROM
	sc 
GROUP BY
	sc.sid 
ORDER BY
	sum( sc.score ) DESC

21.(略)查询不同老师所教不同课程平均分从高到低展示

SELECT
	teacher.Tname,
	course.Cname,
	avg( sc.score ) 
FROM
	teacher
	INNER JOIN course ON teacher.TId = course.tid
	INNER JOIN sc ON course.cid = sc.cid 
GROUP BY
	sc.cid 
ORDER BY
	avg( sc.score ) DESC

22.查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

-- 窗口函数
SELECT
	* 
FROM
	(
	SELECT
		*,
		ROW_NUMBER() over ( PARTITION BY sc.cid ORDER BY sc.score DESC ) m 
	FROM
		sc
		INNER JOIN student stu ON stu.sid = sc.sid 
	) a 
WHERE
	m IN ( 2, 3 )

23.使用分段来统计各科成绩，分别统计各分数段人数：课程ID和课程名称

SELECT
	course.cid,
	course.Cname,
	count( CASE WHEN sc.score >= 60 THEN 1 ELSE NULL END ) '及格',
	count( CASE WHEN sc.score >= 70 AND sc.score < 80 THEN 1 ELSE NULL END ) '中等',
	count( CASE WHEN sc.score >= 80 AND sc.score < 90 THEN 1 ELSE NULL END ) '良好',
	count( CASE WHEN sc.score >= 90 THEN 1 ELSE NULL END ) '优秀' 
FROM
	course
	INNER JOIN sc ON sc.cid = course.cid 
GROUP BY
	course.cid

24.查询学生平均成绩以及排名

-- 窗口函数
SELECT
	sc.sid,
	avg(sc.score),
	ROW_NUMBER() over ( ORDER BY avg(sc.score) DESC ) 
FROM
	sc

25.查询各科成绩前三名的记录（不考虑成绩并列情况）

-- 窗口函数
SELECT
	* 
FROM
	(
	SELECT
		*,
		ROW_NUMBER() over ( PARTITION BY sc.cid ORDER BY sc.score DESC ) m 
	FROM
		sc
		INNER JOIN student stu ON stu.sid = sc.sid 
	) a 
WHERE
	m IN ( 1,2, 3 )

26.(略)查询每门课程被选修的学生数

SELECT
	course.cid,
	count( sc.sid ) 
FROM
	sc
	INNER JOIN course ON sc.CId = course.cid 
GROUP BY
	course.cid

27.(略)查询出只有两门课程的全部学生的学号和姓名

SELECT
	student.sid,
	student.sname 
FROM
	student
	INNER JOIN sc ON student.sid = sc.sid 
GROUP BY
	sc.sid 
HAVING
	count( sc.cid )=2

28.(略)查询男生、女生人数

SELECT
	student.ssex,
	count( student.sid ) 
FROM
	student 
GROUP BY
	student.ssex

29. (略)查询名字中含有"三"字的学生信息

SELECT
	student.sid,
	student.sname 
FROM
	student 
WHERE
	student.sname LIKE '%三%'

30. 31.查询1990年出生的学生名单

SELECT
	student.sid,
	student.sname 
FROM
	student 
WHERE
	YEAR(sage) =1990
-- 或者是用like
SELECT
	student.sid,
	student.sname 
FROM
	student 
WHERE
	student.sage like '1990%'

32.(略)查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

SELECT
	student.sid,
	student.sname,
	avg( sc.score ) 
FROM
	sc
	INNER JOIN student ON sc.sid = student.sid 
GROUP BY
	sc.sid 
HAVING
	avg(sc.score )

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

SELECT
	sc.cid,
	avg( sc.score ) 
FROM
	sc 
GROUP BY
	sc.cid 
ORDER BY
	avg( sc.score ) ASC,
	sc.cid DESC

34.(略)查询课程名称为"数学"，且分数低于60的学生姓名和分数

SELECT
	student.sid,
	student.sname,
	sc.score 
FROM
	sc
	INNER JOIN student ON student.sid = sc.sid
	INNER JOIN course ON course.cid = sc.CId AND course.cname = '数学' 
WHERE
	sc.score < 60

35.查询所有学生的课程及分数情况

SELECT
	student.sid,
	student.sname,
	max( CASE WHEN course.cname = '语文' THEN sc.score ELSE NULL END ) '语文',
	max( CASE WHEN course.cname = '数学' THEN sc.score ELSE NULL END ) '数学',
	max( CASE WHEN course.cname = '英语' THEN sc.score ELSE NULL END ) '英语' 
FROM
	student
	INNER JOIN sc ON sc.sid = student.sid
	INNER JOIN course ON sc.CId = course.CId 
GROUP BY
	student.sid

36.查询任何一门课程成绩在70分以上的姓名、课程名称和分数

SELECT
	student.sname,
	course.cname,
	sc.score 
FROM
	sc
	INNER JOIN student ON student.sid = sc.sid
	INNER JOIN course ON sc.cid = course.cid 
WHERE
	sc.score > 70

37.(略)查询不及格的课程并按课程号从大到小排列

SELECT
	student.sid,
	student.sname,
	course.cname,
	sc.cid,
	sc.score 
FROM
	sc
	INNER JOIN course ON course.cid = sc.cid
	INNER JOIN student ON student.sid = sc.SId 
WHERE
	sc.score < 60 
ORDER BY
	sc.cid DESC

38.(略)查询课程编号为03且课程成绩在80分以上的学生的学号和姓名

SELECT
	student.sname,
	sc.score 
FROM
	sc
	INNER JOIN student ON student.sid = sc.SId 
WHERE
	sc.CId = '03' 
	AND sc.score > 80

39.(略)求每门课程的学生人数

SELECT
  course.cid,
	count( distinct course.sid ) 
FROM
	course
	INNER JOIN sc ON sc.CId = course.cid 
GROUP BY
	course.cid

40.查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩

SELECT
	student.sname,
	sc.score 
FROM
	sc
	INNER JOIN student ON sc.sid = student.sid
	INNER JOIN course ON sc.cid = course.cid
	INNER JOIN teacher ON teacher.tid = course.TId 
	AND teacher.Tname = '张三' 
ORDER BY
	sc.score DESC 
	LIMIT 1

41.查询某个不同课程但成绩相同的学生编号、课程编号、学生成绩

SELECT
	sid 
FROM
	(SELECT b.sid,b.score 
	 FROM sc AS b
	 INNER JOIN ( SELECT sid FROM sc GROUP BY sid HAVING count( DISTINCT cid )> 1 ) AS c ON b.sid = c.sid 
	GROUP BY
		b.sid,
		b.score 
	) AS a 
GROUP BY sid HAVING	count( sid )=1

42.查询每一门课程最好的前两名

43.统计每门课程的学生选修人数（超过5人的课程才统计）。

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

SELECT
	sc.cid,
	COUNT( 1 ) cnt 
FROM
	sc 
GROUP BY
	sc.cid 
HAVING
	count( 1 )> 5 
ORDER BY
	cnt DESC,cid ASC

44.检索至少选修两门课程的学生学号

SELECT
	student.sid,
	student.sname 
FROM
	student
	INNER JOIN sc ON student.sid = sc.sid 
GROUP BY
	sc.sid 
HAVING
	count( sc.cid )>=2

45.查询选修了全部课程的学生信息

SELECT
	student.sid,
	student.sname,
	count( sc.cid ) cnt 
FROM
	student
	INNER JOIN sc ON sc.sid = student.sid 
GROUP BY
	sc.sid 
HAVING
	cnt = (SELECT count( cid ) FROM course)

日期函数

参考：https://www.cnblogs.com/yuyueq/p/16039789.html#24-%E6%97%A5%E6%9C%9F%E5%87%BD%E6%95%B0

函数	描述
NOW()	返回当前的日期和时间
CURDATE()	返回当前的日期
CURTIME()	返回当前的时间
DATE()	提取日期或日期/时间表达式的日期部分
EXTRACT()	返回日期/时间按的单独部分
DATE_ADD()	给日期添加指定的时间间隔
DATE_SUB()	从日期减去指定的时间间隔
DATEDIFF()	返回两个日期之间的天数
DATE_FORMAT()	用不同的格式显示日期/时间

46.(略)查询各学生的年龄

SELECT
	sname,
	TIMESTAMPDIFF(YEAR,sage,CURDATE()) 
FROM
	student

47.(略)查询本周过生日的学生

SELECT
	* 
FROM
	student 
WHERE
	WEEK ( sage, 1 )= WEEK ( NOW(), 1 );

48.查询下周过生日的学生

-- 要考虑跨年的情况

49.(略)查询本月过生日的学生

SELECT
	* 
FROM
	student 
WHERE
	month ( sage)= month (CURDATE());

50.查询下月过生日的学生

SELECT
	* 
FROM
	student 
WHERE
CASE WHEN MONTH (sage)= 12 THEN MONTH (sage)= 1 ELSE MONTH (sage)=(CURDATE())+ 1 END;

B站例题

SELECT
	course.Cname,
	a.score,
	student.sname 
FROM
	( SELECT max( sc.score ) score, cid FROM sc GROUP BY cid ) a
	LEFT JOIN sc ON sc.score = a.score AND sc.cid = a.cid
	LEFT JOIN course ON sc.cid = course.cid
	LEFT JOIN student ON student.sid = sc.sid

SQL 经典 span class token 数据库 java

有关SQL经典50题的更多相关文章

7个大一C语言必学的程序 / C语言经典代码大全 - 2
嗨~大家好，这里是可莉！今天给大家带来的是7个C语言的经典基础代码~那一起往下看下去把【程序一】打印100到200之间的素数#includeintmain(){ inti; for(i=100;i 【程序二】输出乘法口诀表#includeintmain(){inti;for(i=1;i 【程序三】判断1000年---2000年之间的闰年#includeintmain(){intyear;for(year=1000;year 【程序四】给定两个整形变量的值，将两个值的内容进行交换。这里提供两种方法来进行交换，第一种为创建临时变量来进行交换，第二种是不创建临时变量而直接进行交换。1.创建临时变量来
Hive SQL 五大经典面试题 - 2
目录第1题连续问题分析：解法：第2题分组问题分析：解法：第3题间隔连续问题分析：解法：第4题打折日期交叉问题分析：解法：第5题同时在线问题分析：解法：第1题连续问题如下数据为蚂蚁森林中用户领取的减少碳排放量iddtlowcarbon10012021-12-1212310022021-12-124510012021-12-134310012021-12-134510012021-12-132310022021-12-144510012021-12-1423010022021-12-154510012021-12-1523.......找出连续3天及以上减少碳排放量在100以上的用户分析：遇到这类
sql - 查询忽略时间戳日期的时间范围 - 2
我正在尝试查询我的Rails数据库(Postgres)中的购买表，我想查询时间范围。例如，我想知道在所有日期的下午2点到3点之间进行了多少次购买。此表中有一个created_at列，但我不知道如何在不搜索特定日期的情况下完成此操作。我试过:Purchases.where("created_atBETWEEN?and?",Time.now-1.hour,Time.now)但这最终只会搜索今天与那些时间的日期。最佳答案您需要使用PostgreSQL'sdate_part/extractfunction从created_at中提取小时
sql - 在 Rails Console for PostgreSQL 的表中显示数据 - 2
我找到了这样的东西:Rails:Howtolistdatabasetables/objectsusingtheRailsconsole?这一行没问题:ActiveRecord::Base.connection.tables并返回所有表但是ActiveRecord::Base.connection.table_structure("users")产生错误:ActiveRecord::Base.connection.table_structure("projects")我认为table_structure不是Postgres方法。如何列出Postgres数据库的Rails控制台中表中的所有
ruby - 防止SQL注入(inject)/好的Ruby方法 - 2
Ruby中防止SQL注入(inject)的好方法是什么？最佳答案直接使用ruby？使用准备好的语句:require'mysql'db=Mysql.new('localhost','user','password','database')statement=db.prepare"SELECT*FROMtableWHEREfield=?"statement.execute'value'statement.fetchstatement.close 关于ruby-防止SQL注入(inject
ruby-on-rails - 如何在 Rails 中的不同数据库上执行直接 SQL 代码 - 2
我正在编写一个Rails应用程序，它将监视某些特定数据库的数据质量。为了做到这一点，我需要能够对这些数据库执行直接SQL查询——这当然与用于驱动Rails应用程序模型的数据库不同。简而言之，这意味着我无法使用通过ActiveRecord基础连接的技巧。我需要连接的数据库在设计时是未知的(即:我不能将它们的详细信息放在database.yaml中)。相反，我有一个模型“database_details”，用户将使用它来输入应用程序将在运行时执行查询的数据库的详细信息。因此与这些数据库的连接实际上是动态的，细节仅在运行时解析。最佳答案
深度学习12. CNN经典网络 VGG16 - 2
深度学习12.CNN经典网络VGG16一、简介1.VGG来源2.VGG分类3.不同模型的参数数量4.3x3卷积核的好处5.关于学习率调度6.批归一化二、VGG16层分析1.层划分2.参数展开过程图解3.参数传递示例4.VGG16各层参数数量三、代码分析1.VGG16模型定义2.训练3.测试一、简介1.VGG来源VGG（VisualGeometryGroup）是一个视觉几何组在2014年提出的深度卷积神经网络架构。VGG在2014年ImageNet图像分类竞赛亚军，定位竞赛冠军；VGG网络采用连续的小卷积核（3x3）和池化层构建深度神经网络，网络深度可以达到16层或19层，其中VGG16和VGG
sql - Rails:使用 Postgres 创建对象时重复 ActiveRecord::RecordNotUnique？ - 2
我正在使用Rails4应用程序，它需要创建大量对象以响应来自另一个系统的事件。当我调用create!时，主键列上出现非常频繁的ActiveRecord::RecordNotUnique错误(由PG::UniqueViolation引起)我的模型之一。我在SO上找到了其他答案，建议挽救异常并调用retry:beginTableName.create!(data:'here')rescueActiveRecord::RecordNotUnique=>eife.message.include?'_pkey'#Onlyretryprimarykeyviolationslog.warn"Retr
ruby-on-rails - Ruby 和 SQL 中的重复业务逻辑 - 2
我有一个PORO(普通旧Ruby对象)来处理一些业务逻辑。它接收一个ActiveRecord对象并对其进行分类。为了简单起见，以下面为例:classClassificatorSTATES={1=>"Positive",2=>"Neutral",3=>"Negative"}definitializer(item)@item=itemenddefnameSTATES.fetch(state_id)endprivatedefstate_idreturn1if@item.value>0return2if@item.value==0return3if@item.value但是，我还想根据这些st
sql - ActiveRecord 的意外行为包括 - 2
我正在使用ARincludes在对象User和Building之间执行LEFTOUTERJOIN的方法，其中User可能有也可能没有Building关联:users=User.includes(:building).references(:buildings)因为我正在使用references，任何关联的Building对象都将被预先加载。我的期望是我随后能够遍历用户列表，并检查用户是否有与其关联的建筑物而不会触发额外的查询，但实际上每当我尝试访问建筑物属性时我都会看到对于没有建筑物的用户，AR会进行另一个SQL调用以尝试检索该建筑物(尽管在后续尝试中它只会返回nil)。这些查询显然是