Java教程

从0.01开始的SQL之路之sql50题

本文主要是介绍从0.01开始的SQL之路之sql50题,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

 前期准备工作:熟悉几种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,恭喜恭喜,天道酬勤!

这篇关于从0.01开始的SQL之路之sql50题的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!