SQL经典50题练习
SQL这套50题在网上流传很广，是比较经典、全面的一套题，最近刚入职数据开发工程师，SQL需要加强，因此自然不能错过这套题，答案都是自己写的，有错误或者可以改进的地方欢迎指出。数据表介绍题中所需数据来自四个表，分别是学生-成绩-课程-老师，此处列出四个表的字段与关联图创建表及插入数据学生表Studentcreate table Student(SId varchar(10),Sname varch
SQL这套50题在网上流传很广，是比较经典、全面的一套题，最近刚入职数据开发工程师，SQL需要加强，因此自然不能错过这套题，每一道题都是自己写的，不会的题有看过其他大佬的解析，然后自己再写一次，可能有些题答案会一样，有错误或者可以改进的地方欢迎指出。
不会的题一般用搜索看经验找灵感，没有详细记录，主要借鉴这位大佬的视频，讲得非常详细。【数据分析】- SQL面试50题 - 跟我一起打怪升级 一起成为数据科学家_哔哩哔哩_bilibili
数据表介绍
题中所需数据来自四个表，分别是学生-成绩-课程-老师，此处列出四个表的字段与关联图
创建表及插入数据
学生表Student
create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-01-01' , '女');
insert into Student values('07' , '郑竹' , '1989-01-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2012-06-06' , '女');
insert into Student values('12' , '赵六' , '2013-06-13' , '女');
insert into Student values('13' , '孙七' , '2014-06-01' , '女');课程表 Course
create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');教师表 Teacher
create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');成绩表 SC
create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);练习题目
1. 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
1.1 查询同时存在" 01 "课程和" 02 "课程的情况
1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
1.3 查询不存在" 01 "课程但存在" 02 "课程的情况
2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
3. 查询在 SC 表存在成绩的学生信息
4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
4.1 查有成绩的学生信息
5. 查询「李」姓老师的数量
6. 查询学过「张三」老师授课的同学的信息
7. 查询没有学全所有课程的同学的信息
8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
9. 查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
10. 查询没学过"张三"老师讲授的任一门课程的学生姓名
11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
12. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
14. 查询各科成绩最高分、最低分和平均分：
以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
15. 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次
16. 查询学生的总成绩，并进行排名，总分重复时保留名次空缺
16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
17. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
18. 查询各科成绩前三名的记录
19. 查询每门课程被选修的学生数
20. 查询出只选修两门课程的学生学号和姓名
21. 查询男生、女生人数
22. 查询名字中含有「风」字的学生信息
23. 查询同名同性学生名单，并统计同名人数
24. 查询 1990 年出生的学生名单
25. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
26. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
27. 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
28. 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
29. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
30. 查询不及格的课程
31. 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
32. 求每门课程的学生人数
33. 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
34. 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成 绩
35. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
36. 查询每门功成绩最好的前两名
37. 统计每门课程的学生选修人数（超过 5 人的课程才统计）。
38. 检索至少选修两门课程的学生学号
39. 查询选修了全部课程的学生信息
40. 查询各学生的年龄，只按年份来算
41. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
42. 查询本周过生日的学生
43. 查询下周过生日的学生
44. 查询本月过生日的学生
45. 查询下月过生日的学生
解答
1. 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
先把01课程和02课程的成绩分别select出两个表，再把两个表连接在一起，用where写大于
select t1.SId, t1.CId, t1.score, t2.CId as CId_2, t2.score as score_2
from (select *
from SC
where CId = '01') as t1
inner join (select *
from SC
where CId = '02') as t2
on t1.SId = t2.SId
where t1.score > t2.score1.1 查询同时存在" 01 "课程和" 02 "课程的情况
只要同时选了01、02课程，输出这个学生选的所有课的相关信息
由于子查询返回的结果多于一行，不加any会报错
select student.SId, Sname, CId, score
from student left join SC
on student.SId = SC.SId
where student.SId = any(select SC.SId
from SC
where CId in ('01', '02'))1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
select * from (select *
from SC
where SC.CId='01')as t1
left join (select *
from SC
where SC.CId='02')as t2
on t1.SId=t2.SId1.3 查询不存在" 01 "课程但存在" 02 "课程的情况
查出所有存在02课程的情况，再筛掉有01课程的
select *
from SC
where CId = '02' and SId not in(select SId
from SC
where SC.CId = '01')2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select student.SId, Sname, avg(score)
from student left join SC
on student.SId = SC.SId
group by SId
having avg(score) >= 603. 查询在 SC 表存在成绩的学生信息
select distinct student.*
from student, SC
where student.SId = SC.SId4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
select student.SId, Sname, COUNT(CId), SUM(score)
from student, SC
where student.SId = SC.SId
group by SId
# 需要显示没选课学生时，需要用join
select t1.SId, Sname, CourseSum, ScoreSum
from (select student.SId, Sname
from student
)t1
left join (select SC.SId, COUNT(SC.CId) as CourseSum,
SUM(SC.score) as ScoreSum
from SC
group by SC.SId)t2
on t1.SId = t2.SId4.1 查有成绩的学生信息
select distinct student.*
from student, SC
where student.SId = SC.SId AND SC.score is not NULL
5. 查询「李」姓老师的数量
通配符要和like搭配，我总写成=
select *
from teacher
where Tname like '李%'6. 查询学过「张三」老师授课的同学的信息
select student.*
from student
left join SC
on student.SId = SC.SId
where CId = (select TId
from teacher
where Tname = '张三')7. 查询没有学全所有课程的同学的信息
计算所有课程的数量和每个学生选课数量，筛选掉课程数量不相等的学生id。
select *
from student
where student.SId not in (select sc.SId
from sc
group by sc.SId
having count(sc.CId)= (select count(CId) from course))8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
查询出01学号同学选的课，用in在所有学生的选课里查询符合条件的
select distinct student.*
from student left join sc on student.SId = sc.SId
where CId in (select CId
from sc
where SId = '01') and student.SId != '01'9. 查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
两层查询保证筛选出来的学生（选的课程都是01学生选过的+所选课程数量一样）
不会出现所选课程包含01学生课程但又选了其他额外课程，或者只选了01学生的一部分课程这些情况，可保证所选课程完全一样。
select * from Student
where sid in (select SId from SC # 查询所选课程数量和01学生一样的学生
where SId != '01'
group by SId
having count(CId) = (select count(CId)
from SC where SId = '01')
# 去掉所选课程中选了01学生未选课程的人
and SId not in (select distinct SId
from sc
where CId not in (select CId from sc
where SId = '01'))
)10. 查询没学过"张三"老师讲授的任一门课程的学生姓名*
连接三个表，找出那些学生选了张三老师的课，再排除掉，
select student.Sname
from student
where SId not in (select s.SId
from student s left join sc on s.SId = sc.SId
left join teacher t on sc.CId = t.TId
where Tname = '张三')11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select student.SId, Sname, avg(score)
from student left join sc on student.SId = sc.SId
where score < 60
group by SId
having count(*) > 112. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
select s.*
from student s left join sc on s.SId = sc.SId
where CId = '01' and score < 60
order by score DESC13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
直接select会只显示每个学生的一条课程记录，所以再和sc表join一下
select *
from sc left join(select SId, avg(sc.score) as AvgScore
from sc
group by SId) t
on sc.SId = t.SId
order by AvgScore DESC14. 查询各科成绩最高分、最低分和平1均分
以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select sc.CId ,max(sc.score)as 最高分, min(sc.score)as 最低分,
AVG(sc.score)as 平均分, count(*)as 选修人数,
sum(case when sc.score>=60 then 1 else 0 end )/count(*)as 及格率,
sum(case when sc.score>=70 and sc.score<80 then 1 else 0 end )/count(*)as 中等率,
sum(case when sc.score>=80 and sc.score<90 then 1 else 0 end )/count(*)as 优良率,
sum(case when sc.score>=90 then 1 else 0 end )/count(*)as 优秀率
from sc
GROUP BY sc.CId
ORDER BY count(*)DESC, sc.CId ASC15. 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次
16. 查询学生的总成绩，并进行排名，总分重复时保留名次空缺
16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
暂时没想出太好的解法，在网上看了一圈也感觉没有很满意的答案，最近工作有点忙，想到了再补上。
17. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
select c.CId, c.Cname, sum(case when sc.score <= 100 and sc.score > 85 then 1 else 0 end) as '[100-85]',
sum(case when sc.score <= 85 and sc.score > 70 then 1 else 0 end) as '[85-70]',
sum(case when sc.score <= 70 and sc.score > 60 then 1 else 0 end) as '[70-60]',
sum(case when sc.score <= 60 and sc.score > 0 then 1 else 0 end) as '[60-0]'
from sc left join course c on sc.CId = c.CId
group by c.CId18. 查询各科成绩前三名的记录
看到这道题想起之前看过的一篇窗口函数解析，其中的例子非常相似，即分组后组内排序。
<窗口函数> over (partition by <用于分组的列名>
order by <用于排序的列名>)第一层select建立单独的rk列，代表课程内排名
第二层select查询所有相关信息（SId，CId，score，rk）
select *
from(select *,
rank() over (partition by CId
order by score) as rk
from sc) r
where rk <= 319. 查询每门课程被选修的学生数
select CId, count(SId) as SIdnum
from SC
group by CId
20. 查询出只选修两门课程的学生学号和姓名
select student.SId, Sname, count(CId) as CIdnum
from student left join sc on student.SId = sc.SId
group by student.SId
having CIdnum = 221. 查询男生、女生人数
select Ssex, count(*)
from student
group by Ssex22. 查询名字中含有「风」字的学生信息
select *
from student
where Sname like '%风%'23. 查询同名同性学生名单，并统计同名人数
select Sname, count(SId) as num
from student
group by Sname
having num > 124. 查询 1990 年出生的学生名单
select *
from student
where year(Sage) = 199025. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
多种标准排序时，直接用逗号连接，优先的写前面。
select CID, avg(score) as AvgScore
from sc
group by CId
order by AvgScore DESC, CId ASC26. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
select s.SId, s.Sname, avg(score) as AvgScore
from student s left join sc on s.SId = sc.SId
group by s.SId
having AvgScore >= 8527. 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
select s.Sname, score
from student s left join sc on s.SId = sc.SId
left join course on sc.CId = course.CId
where Cname = '数学' and score < 6028. 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
select *
from student s left join sc on s.SId = sc.SId
left join course on sc.CId = course.CId29. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
select Sname, Cname, score
from student s left join sc on s.SId = sc.SId
left join course on sc.CId = course.CId
where score > 7030. 查询不及格的课程
select *
from sc
where score < 6031. 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
原数据里没有满足条件的学生，所以查出来是空的也正常，但有两个等于80的。
select s.SId, Sname, score
from student s left join sc on s.SId = sc.SId
where CId = '01' and score > 8032. 求每门课程的学生人数
select CId, count(SId) as StudentNum
from sc
group by CId
33. 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
如果有两个一样的最高分，用limit1也可以保证只有一个人。
select s.*
from student s left join sc on s.SId = sc.SId
left join course c on sc.CId = c.CId
left join teacher t on c.TId = t.TId
where Tname = '张三'
order by score DESC
limit 134. 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成 绩
从张三老师教的课里计算最高分，然后在选了这门课的人里挑出拥有这个最高分的学生。
三段left join重复了一次，想了半天没有想出解决方法，在网上看了一些其他人写的也差不多都是这样，暂时这样写着吧。
select s.*
from student s left join sc on s.SId = sc.SId
left join course c on sc.CId = c.CId
left join teacher t on c.TId = t.TId
where Tname = '张三' and score in (select max(score)
from student s left join sc on s.SId = sc.SId
left join course c on sc.CId = c.CId
left join teacher t on c.TId = t.TId
where Tname = '张三' ) 35. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
把一个表重命名创建出新的表，可以直接用来比较。
select distinct a.*
from sc a
left join sc b on a.CId != b.CId
where a.score = b.score36. 查询每门功课成绩最好的前两名
创建出一个一样的表sc1，"count(*)"计算次数出满足“同课程内某个分数比其他分数小的次数”
<2代表只有一次或零次，即第二名或第一名，即可满足题目条件。
where条件中写的是sc1表里满足上述条件的score（sc1.score < sc.score），因为在from中已经把查询范围限定在sc1里了，如果select sc.*，则会报错。
select *
from sc sc1
where (select count(*)
from sc
where sc1.CId = sc.CId and sc1.score < sc.score
)<2
order by CId, score DESC37. 统计每门课程的学生选修人数（超过 5 人的课程才统计）
select CId, count(SId) as num
from sc
group by CId
having num > 538. 检索至少选修两门课程的学生学号
select SId
from student
where SId in (select SId
from sc
group by SId
having count(*) > 1)39. 查询选修了全部课程的学生信息
学号分组后计算每个学号有几行（选了几门课），与总课程数是否相等
select *
from student
where SId in (select SId
from sc
group by SId
having count(*) = (select count(*)
from course))
40. 查询各学生的年龄，只按年份来算
select *, year(now())-year(sage) as 年龄
from Student41. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
select Sname, floor(datediff(CURRENT_DATE, Sage)/365) as age
from student42. 查询本周过生日的学生
select *, week(Sage), week(now())
from Student
where week(Sage) = week(now());43. 查询下周过生日的学生
在42题基础上+1
select *, week(Sage), week(now())
from Student
where week(Sage) = week(now()) + 1;44. 查询本月过生日的学生
select *, month(Sage), month(now())
from Student
where month(Sage) = month(now());45. 查询下月过生日的学生
select *, month(Sage), month(now())
from Student
where month(Sage) = month(now()) + 1;
为开发者提供学习成长、分享交流、生态实践、资源工具等服务，帮助开发者快速成长。
更多推荐
19
0- 0
已为社区贡献2条内容
所有评论(0)