从0.01开始的SQL之路之sql50题

2024-01-09 14:32:58

前期准备工作：熟悉几种join，如inner join,left join,right join。

开始学习sql，直接搜索sql50题。点开百度搜索后的第一条链接。

参考链接：https://zhuanlan.zhihu.com/p/67645448

首先是建表语句

-- 学生表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 "课程成绩高的学生的信息及课程分数

答：这题还是比较简单的，熟悉一下join就能知道怎么写了，需要注意的是a inner join b on和直接a,b where 的用法，但这边还是建议多用用join，代码不一定是越简洁越好，代码规范很重要

select * from student s left join sc c on s.SId=c.SId
where S.SId in (SELECT a.SId from 
(SELECT * from sc where CId=01) a inner join (select * from sc where CId=02) b
on a.SId=b.SId where a.score>b.score)

2.查询同时存在"01"课程和"02"课程的情况

答：这题对比上题来说是显而易见的简单了些许，直接inner join开干

select * from student s inner join 
(select * from sc where CId=01) c
on s.SId=c.SId inner join
(select * from sc where CId=02) d
on s.SId=d.SId

简单。。。输出的列可能多了些，但我是小白嘛，不知道怎么筛选列，就会一个*，嘿嘿

3.查询存在"01"课程但可能不存在"02"课程的情况(不存在时显示为 null )

答：跟上题差不多就是把join改成左外就行，easy

select * from student s inner join 
(select * from sc where CId=01) c
on s.SId=c.SId left join
(select * from sc where CId=02) d
on s.SId=d.SId

4.查询不存在"01"课程但存在"02"课程的情况

答：这题和上题差不多，join 02的位置和01调换一下然后加个条件就行了

select * from student s inner join 
(select * from sc where CId=02) c
on s.SId=c.SId left join
(select * from sc where CId=01) d
on s.SId=d.SId where d.CId is null

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

答：哟，开始需要熟悉一些sql的函数了，没关系有万能的百度和友人，小小函数难不倒我

select * from (select SId,avg(score) as avgs from sc 
group by SId having avgs>=60) a
left join student b on a.SId=b.SId

6..查询在 SC 表存在成绩的学生信息

答：题目怎么逐渐小学生起来了，是出题人不行了吗？（直接*数据有点难看，还是加个distinct的吧，偷懒也得有个度）

select distinct s.* from student s
inner join sc on s.SId=sc.SId

7.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示

为 null )

答：基础题

select * from student s 
left join (select SId,count(CId) as countc,
sum(score) as sums from sc group by SId)a
on s.SId=a.SId

8.查有成绩的学生信息

答：依旧是基础题

select distinct s.* from student s 
left join sc on s.SId = sc.SId
where score is not null

9.查询「李」姓老师的数量

答：基础，了解sql通配符，意思一下的题目

select count(TId) as countt from teacher 
where Tname like '李%'

10.查询学过张三老师授课的同学的信息

答：还行吧，多表查询，基础

select * from student s
inner join sc on s.SId=sc.SId
inner join course c on sc.CId=c.CId
inner join teacher t on c.TId=t.TId
where t.Tname='张三'

11. 查询没有学全所有课程的同学的信息

答：还行吧，基础的函数应用，嘶不对（为什么他们用了exist，我是小白我不知道，好像也没人叫我学，那我就不学了吧，嘿嘿嘿）

select s.* from student s 
left join sc on s.SId = sc.SId
group by s.SId
having count(CId)<(select count(CId) from course)

12.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息

答：题目正常起来了，这才有一丢丢水平嘛，但还是小菜嘛

select distinct s.* from student s 
join SC on s.SId = SC.SId
where CId in (select CId from SC where SId = 01) and s.SId !=01

13.查询和" 01 "号的同学学习的课程完全相同的其他同学的信息

答：小菜终归是小菜，撸起袖子就是干，嵌套查询真的有这么难吗？

select * from student where SId in
(select SId from 
(select * from sc where CId in (select CId from sc where SId=01)) as sid
group by SId having count(CId) =(select count(CId) from sc where SId=01)
) and SId !=01

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

答：先查询张三交的课就行了

select Sname from student 
where SId not in (
select SId from sc 
left join course c on sc.CId=c.CId
left join teacher t on c.TId=t.TId 
where Tname='张三' )

15.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

答：属于上面学到的一些知识的综合使用了，他都指定返回的列了，那么就不用*了，了了了

select s.SId,s.Sname,avg(score) avgs from sc 
left join student s on sc.SId=s.SId 
where sc.SId in (select SId from sc  
where score<60 group by SId
having count(CId) >=2) group by SId

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

答：desc，懂得都懂，不多说了

select * from student s join SC on s.SId=SC.SId
where CId = 01 and score < 60
order by score desc

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

答：诸君，我可是兴奋的不行啊，多来点

select distinct * from student s 
inner join (select a.* from sc
left join (select SId,avg(score) as avgs 
from sc group by SId) as a on sc.SId=a.SId 
order by avgs desc) b on b.SId=s.SId
left join sc on sc.SId=s.SId

数据有点多，反正大概是这个样子（这个查询建个视图，剩下的题目是不是能乱杀了）

18.查询各科成绩最高分、最低分和平均分

以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，

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

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

排列

答：题目真长，一看就是大题的那种风格，但是呢，一点都不慌一步步来分析。先按照要求来把要显示的列安排好，然后查询使用case函数获取及格等数据join进course表获取课程名，然后排序就行了，诸君这题很有含金量，我很喜欢

select a.CId,a.Cname,max(score) as 最高分,min(score) as 最低分,avg(score) as 平均分,
sum(及格)/count(SId) as 及格率, 
sum(中等)/count(SId) as 中等率,
sum(优良)/count(SId) as 优良率,
sum(优秀)/count(SId) as 优秀率
from (select SId,sc.CId,score,Cname,TId,
case when score>=60  then 1
else 0  end as 及格,
case when  score>=70 and score<80 then 1
else 0  end as 中等 ,
case when  score>=80 and score<90 then 1
else 0  end as 优良 ,
case when  score>=90 then 1
else 0  end as 优秀 
from sc inner join course c on sc.CId=c.CId) as a
group by a.CId order by count(SId) desc,a.CId

，完美

19.按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

答：说实话，这题的意思把我看绕了，但是没关系，我们来捋一捋，按照各科成绩排名，谁排名？肯定是学生，学生按照课程成绩排名，他这里没说课程的排序方式，我们就默认吧，保留名次空缺？嗯，似懂非懂，是这样吗？

select * from (select a.CId,a.SId,a.score,COUNT(b.score) +1 as ranking
from SC a left join sc b on a.CId = b.CId and a.score<b.score
group by a.CId,a.SId,a.score) c 
left join student s on c.SId=s.SId
order by CId,ranking

20.按各科成绩进行行排序，并显示排名， Score 重复时合并名次

答：和上题差不多，合并名次？那就是不保留名次空缺？加一个distinct就行了吧？


select * from (select a.CId,a.SId,a.score,COUNT(distinct b.score) +1 as ranking
from SC a left join sc b on a.CId = b.CId and a.score<b.score
group by a.CId,a.SId,a.score) c 
left join student s on c.SId=s.SId
order by CId,ranking

天才如我

21.查询学生的总成绩，并进行排名，总分重复时保留名次空缺

答：这题可以通过sql定义变量来实现，select赋值挺方便的，学到了学到了

select a.*,@ranking:= @ranking+1 as ranking
from (select SId, sum(score) as sum from sc
group by SId 
order by sum(score) desc)a,(select @ranking:=0)b

22.查询学生的总成绩，并进行行排名，总分重复时不保留名次空缺

答：不保留名次空缺，嗯，等后面编辑

23.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

答：最烦这种题目了，要写的case真多，借鉴一下吧，下次一定自己写

select sc.CId,Cname,
sum(case when score>=0 and score<=60 then 1
else 0  end) as '[60-0]',
sum(case when score>=0 and score<=60 then 1
else 0  end)/count(SId) as '[60-0]百分比',
sum(case when  score>=60 and score<=70 then 1
else 0  end) as '[70-60]',
sum(case when  score>=60 and score<=70 then 1
else 0  end)/count(SId) as '[70-60]百分比',
sum(case when  score>=70 and score<=85 then 1
else 0  end)as '[85-70]',
sum(case when  score>=70 and score<=85 then 1
else 0  end)/count(SId) as '[85-70]百分比',
sum(case when  score>=85 and score<=100 then 1
else 0  end) as '[100-85]',
sum(case when  score>=85 and score<=100 then 1
else 0  end)/count(SId) as '[100-85]百分比'
from sc 
inner join course c on sc.CId=c.CId group by sc.CId,Cname

24.查询各科成绩前三名的记录

答：哟，不错哟！

select * from (select a.CId,a.SId,a.score,COUNT(b.score) +1 as ranking
from SC a left join sc b on a.CId = b.CId and a.score<b.score
group by a.CId,a.SId,a.score
having ranking <= 3 ) c 
left join student s on c.SId=s.SId
order by CId,ranking

25.查询每门课程被选修的学生数

答：看来我还是喜欢小菜更多

select CId,count(SId)  from sc group by CId

26. 查询出只选修两门课程的学生学号和姓名

答：小菜好，小菜好，能少掉几根头发

select s.SId,s.Sname from student s
inner join sc on s.SId=sc.SId 
group by s.SId having count(CId)=2

27. 查询男生、女生人数

答：少掉1根头发

select Ssex as 性别,count(Ssex) as 人数
from student group by Ssex

28.查询名字中含有「风」字的学生信息

答：少掉2根头发

select * from student 
where Sname like '%风%'

29.查询同名同性学生名单，并统计同名人数

答：少掉3根头发

select Sname,count(SId) from student 
group by Sname having count(Sname) >1

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

答：少掉4根头发

select * from student where Sage like '1990%'

31.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编

号升序排列

答：少掉5根头发，喝口水稳一稳

select CId,avg(score) from sc 
group by CId order by avg(score) desc,CId

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

答：3点几了，饮茶先，看这样子头发还能保的住

select sc.SId,s.Sname,avg(score) from student s
inner join sc on s.SId=sc.SId
group by sc.SId having avg(score)>=85

33.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

答：三点几喝口茶，能不能涨一根头发，数学30分在我初中的时候肯定要被打手板

select s.Sname,score,c.Cname  
from student s 
inner join sc on s.SId=sc.SId
inner join course c on sc.CId=c.CId
where c.Cname='数学' and score<60

34.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

答：茶杯牢牢抓稳，数据截图意思一下就好，喝茶重要

select s.Sname,
sc.CId,sc.score from student s 
left join sc on s.SId=sc.SId

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

答：大于70就够了吧，才注意到一个学生是赵雷，不知道这个赵雷能不能来一手《理想》

select s.Sname,Cname,score from student s
inner join sc on s.SId=sc.SId
inner join course c on sc.CId=c.CId
where score>70

36.查询不及格的课程

答：不及格的课程？内涵三门主课？

select distinct Cname from course c
inner join sc on c.CId=sc.CId
where score<60

37.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名

答：

select s.SId,Sname 
from student s inner join sc on s.SId=sc.SId
where CId=01 and score>=80

38.求每门课程的学生人数

答：问题是不是重复了？还有不要问上一题为什么是空，问就是在饮茶

select CId,count(SId)  
from sc group by sc.CId

39.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

答：理想今年你几岁

select s.*,c.Cname,score from student s 
inner join sc on s.SId = sc.SId
inner join course c on sc.CId = c.CId
inner join teacher t on c.TId = t.TId
where Tname = '张三' 
order by score desc limit 1

40.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生

信息及其成绩

答：加一个嵌套查询就差不多了

select s.*,c.Cname,score from student s 
inner join sc on s.SId = sc.SId
inner join course c on sc.CId = c.CId
inner join teacher t on c.TId = t.TId
where Tname = '张三' and score in 
(select max(score) from sc
inner join course c on sc.CId = c.CId
inner join teacher t ON c.TId = t.TId
where Tname = '张三')

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

答：接杯水

select distinct a.* from sc a inner join sc b on a.SId=b.SId
where a.score=b.score and a.CId!=b.CId

42.查询每门成绩最好的前两名

答：哦？还不错哟

select * from (select a.CId,a.SId,a.score,COUNT(b.score) +1 as ranking
from sc a left join SC b on a.CId = b.CId and a.score<b.score
group by a.CId,a.SId,a.score
having ranking <= 2 ) c 
left join student s on c.SId=s.SId

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

答：1,2,3

select CId,count(SId) from sc group by CId 
having count(SId)>5

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

答：1,2,3,4,5,6,7

select SId from sc group by SId having count(CId)>=2

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

答：头发又少掉1根

select s.* from student s
inner join sc on s.SId=sc.SId
group by s.SId 
having count(CId)=(select count(CId) from course)

46.查询各学生的年龄，只按年份来算

答：题目越来越没水平了，咳咳，不过这是好事啊，头发保住了

select Sname,Sage,year(now())-year(Sage) as age
from student

47.按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

答：醒醒，学新函数了！？新函数？等我先品一口茶先

select SId,Sname,TIMESTAMPDIFF(YEAR,Sage,NOW()) AS age
from student

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

答：咳咳，本人目前日期7月23，很显然没有结果集

select * from student 
where week(Sage)=week(now())

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

答：咳咳

select * from student 
where week(Sage)=week(now())+1

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

答：咳咳

select * from student
where month(Sage)=month(now())

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

答：咳咳，下班了，茶也喝完了，溜了，真多出来一题？看来我上面感觉重复了一题是对的，不过我也懒得看了，下班要紧

select * from student
where month(Sage)=month(now())+1

咳咳，已知本博主每写一题平均掉3根头发，求博主写完50题后掉了多少根头发；写完之后博主明显感觉自己的水平从0.01涨到了0.02，恭喜恭喜，天道酬勤！

码农公寓

相关文章