SQL学习计划二：小小项目+查询训练

最新推荐文章于 2023-05-09 15:18:40 发布

Mr_OOR

最新推荐文章于 2023-05-09 15:18:40 发布

阅读量654

点赞数 2

分类专栏： SQL Server学习计划栏文章标签： sql 数据库 database mysql

原文链接：https://www.cnblogs.com/GT_Andy/archive/2010/08/13/1921754.html

版权

SQL Server学习计划栏专栏收录该内容

6 篇文章 2 订阅

订阅专栏

Hello！此篇本是大神“爱新觉罗.毓华(十八年风雨，守得冰山雪莲花开)”所创，我是从中少许修改并添加“批注”讲解，帮助各位小白学习！这里本人全用SQL Server2019版进行展现~

若有困难，请按以下步骤享用：

咱儿要么看批注，学习解题思路！
咱儿要么运行答案只看结果不要偷瞄代码！忍住！忍住！
然后带着批注的思路看答案学习！
删答案→过脑子→戳键盘！

/*
原标题：一个项目涉及到的50个Sql语句(整理版)
原作者：爱新觉罗.毓华(十八年风雨，守得冰山雪莲花开)
若侵即删！
*/

数据源：
--1.学生表
Student(S#,Sname,Sage,Ssex) --S# 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
--2.课程表
Course(C#,Cname,T#) --C# --课程编号,Cname 课程名称,T# 教师编号
--3.教师表
Teacher(T#,Tname) --T# 教师编号,Tname 教师姓名
--4.成绩表
SC(S#,C#,score) --S# 学生编号,C# 课程编号,score 分数

create table Student(S# varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10))
insert into Student values('01' , N'赵雷' , '1990-01-01' , N'男')
,('02' , N'钱电' , '1990-12-21' , N'男')
,('03' , N'孙风' , '1990-05-20' , N'男')
,('04' , N'李云' , '1990-08-06' , N'男')
,('05' , N'周梅' , '1991-12-01' , N'女')
,('06' , N'吴兰' , '1992-03-01' , N'女')
,('07' , N'郑竹' , '1989-07-01' , N'女')
,('08' , N'王菊' , '1990-01-20' , N'女')
create table Course(C# varchar(10),Cname nvarchar(10),T# varchar(10))
insert into Course values('01' , N'语文' , '02')
,('02' , N'数学' , '01')
,('03' , N'英语' , '03')
create table Teacher(T# varchar(10),Tname nvarchar(10))
insert into Teacher values('01' , N'张三')
,('02' , N'李四')
,('03' , N'王五')
create table SC(S# varchar(10),C# varchar(10),score decimal(18,1))
insert into SC values('01' , '01' , 80)
,('01' , '02' , 90)
,('01' , '03' , 99)
,('02' , '01' , 70)
,('02' , '02' , 60)
,('02' , '03' , 80)
,('03' , '01' , 80)
,('03' , '02' , 80)
,('03' , '03' , 80)
,('04' , '01' , 50)
,('04' , '02' , 30)
,('04' , '03' , 20)
,('05' , '01' , 76)
,('05' , '02' , 87)
,('06' , '01' , 31)
,('06' , '03' , 34)
,('07' , '02' , 89)
,('07' , '03' , 98)
（批注：这里的创表代码直接全部复制执行！！！）

（批注：此图是4张表的数据展示，请保存、打开、参照享用哟！！！）

那么废话不多说，撸起袖子就是戳！
（批注：鼠标别滑那么快，当心看到答案哟！！！）

-1、查询"01"比"02"课程成绩高的学生的信息及课程分数
--1.1、查询同时存在"01"和"02"课程的情况
（批注：在from里灵活运用两个SC表）

select a.* , b.score [课程01],c.score [课程02]
from Student a , SC b , SC c
where b.C# ='01' and c.C# ='02'
and b.S# = a.S# and c.S# = a.S#
and b.score > c.score

--1.2、查询同时存在"01"和"02"课程的情况,和存在"01"但不存在"02"课程的情况
（批注：06号吴兰的课程有"01"没"02"，不存在就显示null）

select a.* , b.score [课程01],c.score [课程02]
from Student a
left join SC b on a.S# = b.S# and b.C# ='01'
left join SC c on a.S# = c.S# and c.C# ='02'
where b.score > isnull(c.score,0)

--2、查询"01"比"02"课程成绩低的学生的信息及课程分数
--2.1、查询同时存在"01"课程和"02"课程的情况
（批注：这里直接把1.1的>改成<即可）

select a.* , b.score [课程01],c.score [课程02]
from Student a , SC b , SC c
where b.C# ='01' and c.C# ='02'
and b.S# = a.S# and c.S# = a.S#
and b.score < c.score

--2.2、查询同时存在"01"和"02"课程的情况,和存在"02"但不存在"01"课程的情况
（批注：07号郑竹的课程有"02"没"01"，注意select显示）

select a.* , c.score [c课程01], b.score [b课程02]
from Student a
left join SC b on a.S# = b.S# and b.C# ='02'
left join SC c on a.S# = c.S# and c.C# ='01'
where b.score > isnull(c.score,0)

--3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
（批注：这里简单到不行咱儿就不多说了）

select a.S# , a.Sname , avg(b.score) [平均分]
from Student a , sc b
where a.S# = b.S#
group by a.S#,a.Sname
having avg(score)>=60

--4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
--4.1、查询在sc表存在成绩的学生信息的SQL语句。
（批注：把上一题改下符号即可）

select a.S# , a.Sname , avg(b.score) [平均分]
from Student a , sc b
where a.S# = b.S#
group by a.S#,a.Sname
having avg(score)<60

--4.2、查询在sc表中没成绩的学生信息的SQL语句。
（批注：08号王菊一课成绩都没有）

select a.S# , a.Sname , avg(b.score) [平均分]
from Student a
left join sc b on a.S# = b.S#
group by a.S#,a.Sname
having isnull(avg(score),0)<60

--5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
--5.1、查询所有有成绩的SQL。
（批注：不难）

select a.S# [学生编号], a.Sname [学生姓名]
, count(b.C#) 选课总数, sum(score) [所有课程的总成绩]
from Student a , SC b
where a.S# = b.S#
group by a.S#, a.Sname

--5.2、查询所有(包括有成绩和无成绩)的SQL。
（批注：08号王菊一课成绩都没有）

select a.S# [学生编号], a.Sname [学生姓名]
, count(b.C#) 选课总数, sum(score) [所有课程的总成绩]
from Student a left join SC b on a.S# = b.S#
group by a.S#, a.Sname

--6、查询"李"姓老师的数量
（批注：方法不止一种咱儿怎么喜欢怎么来）

select count(Tname)
from Teacher
group by Tname
having Tname like '李%'

--7、查询学过"张三"老师授课的同学的信息
（批注：这里where全搞定）

select st.*
from Teacher t, Course c, SC, Student st
where t.Tname = '张三'
and t.T# = c.T#
and c.C# = sc.C#
and sc.S# = st.S#

--8、查询没学过"张三"老师授课的同学的信息
（批注：思路是去掉"有学过的名单"）

select * from Student
where Student.S# not in
(
select distinct sc.S#
from Teacher t, Course c, SC
where t.Tname = '张三'
and t.T# = c.T#
and c.C# = SC.C#
)

--9、查询学过"01"和"02"的课程的同学的信息
（批注：有点梦回1.1哈哈哈）

select s.* from student s, sc a, sc b
where a.S# = b.S#
and a.C# = '01'
and b.C# = '02'
and s.S# = a.S#

--10、查询学过"01"但是没有"02"的课程的同学的信息
（批注：方法不一怎喜怎来）

select Student.* from sc a, Student
where a.S# = Student.S#
and a.C# = '01'
and a.S# not in (select S# from sc where C# = '02')

--11、查询没有学全所有课程的同学的信息
（批注：08号王菊一课都没学）

select * from Student s
where s.S# not in
(
select S#
from SC
group by S#
having count(C#) = (select count(*) from Teacher)
)

--12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
（批注：思路是"01学过的课程名单"→in课程名单的学生→表连接看同学信息）

select distinct Student.* from SC, Student
where SC.S# = Student.S#
and SC.C# in (select C# from SC where S# = '01')
and SC.S# <> '01'

--13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
（批注：跟上题一样，一小块一小块做，最后再整合）

select * from Student
where S# in
(
select S# from SC
where C# in(select C# from SC where S# = '01')
and S# != '01'
group by S#
having count(S#)=(select count(*) from SC where S# = '01' group by S#)
)

--14、查询没学过"张三"老师讲授的任一门课程的学生姓名
（批注：思路是找课程编号→学过的学生名单→Student表除去这些名单）

select * from Student
where S# not in
(
select S# from SC
where C# = (select C# from Teacher t, Course c where t.T# = c.T# and t.Tname = '张三')
)

--15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
（批注：一步步来，不急不急）

select SC.S#, Student.Sname, avg(score)平均值 from Student, SC
where Student.S# in
(
select S# from SC
where score < 60
group by S#
having count(S#) >=2
)
and Student.S# = SC.S#
group by SC.S#, Student.Sname

--16、检索"01"课程分数小于60，按分数降序排列的学生信息
（批注：简单的题做起来是不是很爽~~）

select Student.*, score from SC, Student
where SC.S# = Student.S#
and C# = '01'
and score < 60
order by score desc

--17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
（批注：有趣的来了！先看下图！理解case when用法就完事了，注意group by）

select a.Sname 姓名
,max(case when C# = '01' then score end)课程01
,max(case when C# = '02' then score end)课程02
,max(case when C# = '02' then score end)课程02
,avg(score)平均分
from Student a left join SC b on a.S# = b.S#
group by a.Sname
order by 平均分 desc

--18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
--及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
（批注：各科及格个数→left join表连接→各科及格个数/各科考试总数，看着多其实简简单单，还多学了decimal()函数是不是很开心~~注意是cast不是case）

select SC.C# [课程编号], Course.Cname [课程名称], max(SC.score) [最高分], min(SC.score) [最低分]
, cast( avg(SC.score) as decimal(18,2)) [平均分]
, cast( JG.及格*100.0/count(*) as decimal(18,2)) [及格率%]
, cast( ZD.中等*100.0/count(*) as decimal(18,2)) [中等率%]
, cast( YL.优良*100.0/count(*) as decimal(18,2)) [优良率%]
, cast( YX.优秀*100.0/count(*) as decimal(18,2)) [优秀率%]

from Course, SC
left join (select C#, count(*)及格 from sc where score>=60 group by C#)JG on
SC.C# = JG.C#
left join (select C#, count(*)中等 from sc where score>=70 and score<80 group by C#)ZD on
SC.C# = ZD .C#
left join (select C#, count(*)优良 from sc where score>=80 and score<90 group by C#)YL on SC.C# = YL.C#
left join (select C#, count(*)优秀 from sc where score>=90 group by C#)YX on
SC.C# = YX.C#
where SC.C# = Course.C#
group by SC.C#, Course.Cname, JG.及格, ZD.中等, YL.优良, YX.优秀

--19、按各科成绩进行排序，并显示排名
（批注：这里学ROW_NUMBER()分组排序函数啦~~其他排名函数空降24题）

select *,
ROW_NUMBER() over(partition by C# order by score desc) 排序
from SC

--20、查询学生的总成绩并进行排名
（批注：哈哈现在是不是简简单单？别落下08号王菊）

select Student.S#, Student.Sname, sum(score)总成绩
,ROW_NUMBER() over(order by sum(score) desc)排名
from Student left join SC on Student.S# = SC.S#
group by Student.S#, Student.Sname

--21、查询不同老师所教不同课程平均分从高到低显示
（批注：这里就不多说啦，也没要求保存两位小数哈哈哈）

select Teacher.Tname,
cast(avg(score) as decimal(18,2)) 平均分
from SC, Course, Teacher
where SC.C# = Course.C# and Course.T# = Teacher.T#
group by Teacher.Tname
order by 平均分 desc

--22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
（批注：这里回到19题，注意不能直接where筛选哟）

select * from
(
select *,
ROW_NUMBER() over(partition by C# order by score desc)排名
from SC
)t
where 排名>1 and 排名<4

--23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[60-0]及所占百分比
--23.1 统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]
--横版
（批注：case when把每行都扫一遍判断，可以试着把sum和group by删了看一下结果）

select Course.C# [课程编号], Course.Cname [课程名称]
,sum(case when score >= 85 then 1 else 0 end) [85-100]
,sum(case when score >= 70 and score <85 then 1 else 0 end) [70-85]
,sum(case when score >= 60 and score <70 then 1 else 0 end) [60-70]
,sum(case when score <60 then 1 else 0 end) [0-60]

from SC, Course where SC.C# = Course.C# group by Course.C#, Course.Cname

--竖版(数量为0显示)
（批注：难点是group by把"分数段"再写一遍，还有all这个神奇的东西）

select c.C# [课程编号], c.Cname [课程名称]

,(case when score >= 85 then '85-100'
when score>=70 and score<85 then '70-85'
when score>=60 and score<70 then '60-70' else '0-60' end) 分数段
,count(*)数量
from SC, Course c where SC.C# = c.C#

group by all c.C#, c.Cname
,(case when score >= 85 then '85-100'
when score>=70 and score<85 then '70-85'
when score>=60 and score<70 then '60-70' else '0-60' end)

--竖版(数量为0不显示)
（批注：把上一题的"all"删了就行，这里咱们就不重复写啦，当然也可以看看下面的写法啦）

--23.2 统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[<60]及所占百分比
--横版
（批注：这个只不过把23.1横版/count(*)即可）

select Course.C# [课程编号], Course.Cname [课程名称]
,sum(case when score >= 85 then 1 else 0 end) [85-100]
,cast(sum(case when score >= 85 then 1 else 0 end)*100.0/count(*) as decimal(18,2)) [百分比%]

,sum(case when score >= 70 and score <85 then 1 else 0 end) [70-85]
,cast(sum(case when score >= 70 and score <85 then 1 else 0 end)*100.0/count(*) as decimal(18,2)) [百分比%]

,sum(case when score >= 60 and score <70 then 1 else 0 end) [60-70]
,cast(sum(case when score >= 60 and score <70 then 1 else 0 end)*100.0/count(*) as decimal(18,2)) [百分比%]

,sum(case when score <60 then 1 else 0 end) [0-60]
,cast(sum(case when score <60 then 1 else 0 end)*100.0/count(*) as decimal(18,2)) [百分比%]

from SC, Course where SC.C# = Course.C# group by Course.C#, Course.Cname

--竖版(数量为0显示)
（批注：我这弄了aa表格，where连接后做除数）

select c.C# [课程编号], c.Cname [课程名称],
分数段=(
case when score >= 85 then '85-100'
when score>=70 and score<85 then '70-85'
when score>=60 and score<70 then '60-70' else '0-60' end
)
,count(*)数量
,cast(count(*)*100.0/aa.co as decimal(18,2)) [百分比%]

from SC, Course c, (select C#, count(1) co from sc group by C#)aa
where SC.C# = c.C# and c.C# = aa.C#

group by all c.C#, c.Cname, aa.co
,(case when score >= 85 then '85-100'
when score>=70 and score<85 then '70-85'
when score>=60 and score<70 then '60-70' else '0-60' end)

--竖版(数量为0不显示)
（批注：同理，把上一题"all"删了即可，这里咱们就不重复写啦，当然也可以看看下面的写法啦）

--24、查询所有学生已学课程的数量及其名次
--没有并列排名
（批注：ROW_NUMBER()）

select Student.S#, Student.Sname
,已学课数 = count(score)
,排名 = ROW_NUMBER() over(order by count(score) desc)
from Student left join SC on SC.S# = Student.S#
group by Student.S#, Student.Sname

--并列保留名次排名
（批注：RANK()）

select Student.S#, Student.Sname
,已学课数 = count(score)
,排名 = RANK() over(order by count(score) desc)
from Student left join SC on SC.S# = Student.S#
group by Student.S#, Student.Sname

--并列不保留名次排名
（批注：DENSE_RANK()）

select Student.S#, Student.Sname
,已学课数 = count(score)
,排名 = DENSE_RANK() over(order by count(score) desc)
from Student left join SC on SC.S# = Student.S#
group by Student.S#, Student.Sname

--25、查询各科成绩前三名的记录
（批注：这里可以梦回22题，然后排名什么并列不并列跟上题的3个函数一样）

select * from Student,
(
select *,
ROW_NUMBER() over(partition by C# order by score desc)排名
from SC
)t
where Student.S# = t.S#
and 排名<4

--26、查询每门课程被选修的学生数
（批注：这题闭着眼睛打~）

select C#, count(*)数量 from SC group by C#

--27、查询出只有两门课程的全部学生的学号和姓名
（批注：这题不做也罢~）

select s.S#, s.Sname
from SC, Student s
where SC.S# = s.S#
group by s.S#, s.Sname
having count(1) = 2

--28、查询男生、女生人数
（批注：这里不用什么花里胡哨啊，简单点）

select Ssex, count(*) from Student group by Ssex

--29、查询名字中含有"风"字的学生信息
（批注：这里用一下like）

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

--30、查询同名同性学生名单，并统计同名人数
（批注：没结果就对了哈哈哈）

select Sname, count(*) from Student group by Sname having count(*)>1

-31、查询1990年出生的学生名单

（批注：Sage列的类型是datetime，这里可以学习一下datepart()函数）

select * from Student where year(sage) = 1990

select * from Student where datepart(yy,sage) = 1990

--32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
（批注：这里order by需要写两个）

select b.C#, b.Cname, avg(score)平均成绩
from SC a, Course b where a.C# = b.C#
group by b.C#, b.Cname
order by 平均成绩 desc, b.C# asc

--33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
（批注：这些题是不是轻松一些？哈哈哈）

select a.S#, a.Sname, avg(score)平均成绩
from Student a, SC b where a.S# = b.S#
group by a.S#, a.Sname
having avg(score)>=85

--34、查询课程名称为"数学"，且分数低于60的学生姓名和分数
（批注：这个就不用多说了）

select Sname, Cname, score
from Student, SC, Course
where Student.S# = SC.S# and SC.C# = Course.C#
and Cname = '数学' and score < 60

--35、查询所有学生的课程及分数情况；
（批注：这个就不多说了）

select Student.S#, Student.Sname, Cname, score
from Student
left join SC on Student.S# = SC.S#
left join Course on SC.C# = Course.C#
order by Student.S#, SC.C#

--36、查询任何一门课程成绩在70分以上(包括70)的姓名、课程名称和分数；
（批注：这个不多说了）

select Student.Sname, Cname, score
from Student, SC, Course
where score >= 70
and Student.S# = SC.S#
and SC.C# = Course.C#

--37、查询任何一门课程不及格的姓名、课程名称和分数；
（批注：这个不多说）

select Student.*, Cname, score
from Student, SC, Course
where score < 60
and Student.S# = SC.S#
and SC.C# = Course.C#

--38、查询课程编号为01且课程成绩在80分以上(包括80)的学生学号、姓名、分数；
（批注：这不多说）

Student.S#, Student.Sname, score
from Student, SC
where Student.S# = SC.S# and SC.C# = '01' and score >= 80

--39、求每门课程的学生人数
（批注：不多说）

select SC.C#, Cname, count(*)学生人数
from SC, Course
where SC.C# = Course.C#
group by SC.C#, Cname

--40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
--当最高分只有一个时
（批注：这里先降序再top 1即可）

select top 1 a.*, c.Cname, score
from Student a, SC b, Course c, Teacher t
where Tname = '张三'
and a.S# = b.S# and b.C# = c.C# and c.T# = t.T#
order by score desc

--当最高分出现多个时
（批注：这个就用并列排名函数，可以改成"李四"老师看看效果）

select * from
(
select a.*, c.Cname, score
,RANK() over(order by score desc)排名
from Student a, SC b, Course c, Teacher t
where Tname = '张三' and a.S# = b.S#
and b.C# = c.C# and c.T# = t.T#
)aa
where 排名 = 1

--41、查询不同课程但成绩相同的学生的学生编号、课程编号、学生成绩
（批注：题目是当一个学生满足这些条件，还是不同学生一起满足这些条件呢？？
哈哈哈，所以这里把重复出现的成绩的信息查出来即可！
因为重复都是80分，所以试试修改一下数据！
比如把07号郑竹的02课程改成98分看看效果，对还是不对）

select * from SC where score in
(select score from SC group by score having count(*) > 1)
order by score desc

--42、查询每门功成绩最好的前两名
（批注：这里ROW_NUMBER()最合适不过，跟top类似的道理，因为题目是只要两名同学，就算排名第一有好几位但我只要两位就够了）

select * from
(
select S#, C#, score
,ROW_NUMBER() over(partition by C# order by score desc)排名
from SC
)t
where t.排名 <= 2

--43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
（批注：这个不难）

select C# 课程号, count(*)选修人数
from SC group by C# having count(*) > 5
order by 选修人数 desc, 课程号 asc

--44、检索至少选修两门课程的学生学号
（批注：这不难）

select S# 学号, count(*)课程数
from SC group by S# having count(*) >= 2

--45、查询选修了全部课程的学生信息
--用课程数量对比
（批注：这个不难，在SC表中找到已学3个课程的学生即可）

select * from Student
where S# in(
select S# from SC group by S#
having count(*) = (select count(*) from Course)
)

--使用双重否定来完成
（批注：这里先看下图学习一下not exists的用法，在表a除去表b也存在的ID，其实就是“左/右表独有”）

（批注：有趣！
第1步建立一个所有学生并学完所有课程的虚拟表m，
第2步用not exists用法除去SC上过课的真实表，得到缺课的信息表，缺课越多出现次数越多
第3步Student不在缺课的信息表上的S#，表示已上满课）

select * from Student where S# not in
(
select S# from (select * from Student, Course)m
where not exists(select * from SC where SC.S# = m.S# and SC.C# = m.C#)
)

--46、查询各学生的年龄
--按照年份来算
（批注：getdate()函数提取当前日期，这里可以学习一下datediff ()函数）

select *, year(getdate())- year(Sage) 年龄 from Student
select *, datediff(yy, sage, getdate()) 年龄 from Student

--按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
（批注：这个挺有趣的，自行百度convert()函数那个120的意思）

select *, case when
right(convert(varchar(10),getdate(),120),5)
< right(convert(varchar(10),sage,120),5)
then datediff(yy , sage , getdate()) - 1
else datediff(yy , sage , getdate()) end

from student