SQL经典50题

参考内容

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

SQL面试50题

视频中参考文章

图解SQL面试题:经典50题

【SQL】SQL经典50题&答案

数据库

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

sql50.sql

img

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站例题

img

img

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

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:数字20 设计师:CSDN官方博客 返回首页
评论

打赏作者

余月七

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值