参考内容
首先这篇记录是根据下面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/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://wwwblogs/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经典50题
发布评论