--快捷键 Ctrl+Shift+R(刷新本地缓存)
--services.msc 开启服务

---- 练习题目：
--go
--use ClassicDb
--go
--select * from StudentCourseScore
--select * from CourseInfo
--select * from StudentInfo
--select * from Teachers
--1.查询"2数学 "课程比" 1语文 "课程成绩高的学生的信息及课程分数
--select a.*,b.Score,c.Score from StudentInfo a join 
--(select * from StudentCourseScore where CourseId=1)b on a.Id=b.StudentId join 
--(select * from StudentCourseScore where CourseId=2)c on a.Id=c.StudentId
--where b.Score< c.Score

---- 1.1 查询同时存在" 数学 "课程和" 语文 "课程的情况
--select StudentId,count(*) from StudentCourseScore where CourseId in (1,2) group by StudentId having count(*)>=2

---- 1.2 查询存在" 数学 "课程但可能不存在" 语文 "课程的情况(不存在时显示为 null )
--select a.*,b.Score from 
--(select * from StudentCourseScore where CourseId=2)a left join 
--(select * from StudentCourseScore where CourseId=1)b on a.StudentId=b.StudentId

---- 1.3 查询不存在" 数学 "课程但存在" 语文 "课程的情况
--select * from StudentCourseScore where StudentId not in (select StudentId from StudentCourseScore where CourseId=1)

---- 2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
--select StudentId,StudentName,平均成绩 from StudentInfo a join 
--(select StudentId,avg(score)平均成绩 from StudentCourseScore group by StudentId having AVG(score)>=60)b 
--on a.Id=b.StudentId

---- 3.查询在 成绩 表存在成绩的学生信息
--select a.* from StudentInfo a join 
--(select StudentId from StudentCourseScore group by StudentId)b on a.Id=b.StudentId

---- 4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
--select * from 
--(select Id, StudentCode,StudentName from StudentInfo)a left join 
--(select StudentId,count(*)选课总数,sum(Score)总成绩 from StudentCourseScore group by StudentId )b on a.Id=b.StudentId

---- 4.1 查有成绩的学生信息
--select * from StudentInfo where Id in (select StudentId from StudentCourseScore group by StudentId)

---- 5.查询「李」姓老师的数量
--select * from Teachers where  TeacherName like '李%'

---- 6.查询学过「张三」老师授课的同学的信息
--select * from StudentInfo where Id in
--(select StudentId from StudentCourseScore where CourseId=1 group by StudentId)

---- 7.查询没有学全所有课程的同学的信息
--select * from StudentInfo where Id in
--(select StudentId from StudentCourseScore group by StudentId having count(*)< 3)

---- 8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
--select * from StudentInfo where Id in
--(select StudentId from StudentCourseScore where CourseId in
--(select CourseId from StudentCourseScore where StudentId in
--(select Id from StudentInfo where StudentCode='01')) group by StudentId)

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

--select a.StudentId,a1.CourseId,a2.CourseId,a3.CourseId from StudentCourseScore a 
--join 
--(select StudentId,CourseId from StudentCourseScore a where CourseId=1)a1  on a.StudentId=a1.StudentId
--join
--(select StudentId,CourseId from StudentCourseScore a where CourseId=2)a2 on a.StudentId=a2.StudentId
--join
--(select StudentId,CourseId from StudentCourseScore a where CourseId=3)a3 on a.StudentId=a3.StudentId
--group by a.StudentId,a1.CourseId,a2.CourseId,a3.CourseId having a.StudentId != 1
---- 10.查询没学过"张三"老师讲授的任一门课程的学生姓名
--select StudentName from StudentInfo where Id not in
--(select StudentId from StudentCourseScore where CourseId=1 group by StudentId)

---- 11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
--select id,StudentCode,StudentName,平均成绩 from StudentInfo a join
--(select StudentId,avg(Score)平均成绩 from StudentCourseScore where Score<60 and StudentId in
--(select StudentId from StudentCourseScore group by StudentId having count(*)>=2) 
--group by StudentId)b on a.Id=b.StudentId

---- 12.检索" 数学 "课程分数小于 60，按分数降序排列的学生信息
--select a.*,b.Score from 
--(select * from StudentInfo where Id in
--(select StudentId from StudentCourseScore where CourseId=2 and Score<60))a
--join 
--(select StudentId,score from StudentCourseScore where CourseId=2 and Score<60)b on a.Id=b.StudentId  order by Score

---- 13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
--select * from 
--(select StudentId,avg(score)平均成绩 from StudentCourseScore
--group by StudentId)a join 
--StudentCourseScore b on a.StudentId=b.StudentId
--order by 平均成绩 desc

---- 14.查询各科成绩最高分、最低分和平均分：
--select CourseId,MAX(Score)最高分,MIN(Score)最低分,AVG(Score) 平均分 from StudentCourseScore group by CourseId
---- 15.以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
--/*
--	及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

--	要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
--*/
select t1.CourseName,t2.* from CourseInfo t1,
(select CourseId,count(*)选修人数,MAX(Score)最高分,MIN(Score)最低分,AVG(Score) 平均分,
cast(cast(cast(sum(case when Score>=60 then 1 else 0 end)as decimal(5,2))/count(*)*100 as decimal(5,2))as varchar(20))+'%'as 及格率,
cast(cast(cast(sum(case when Score>=70 and Score<80 then 1 else 0 end)as decimal(5,2))/count(*)*100 as decimal(5,2))as varchar(20))+'%'中等率,
cast(cast(cast(sum(case when Score>=80 and Score<90 then 1 else 0 end)as decimal(5,2))/count(*)*100 as decimal(5,2))as varchar(20))+'%'优良率,
cast(cast(cast(sum(case when Score>=90 then 1 else 0 end)as decimal(5,2))/count(*)*100 as decimal(5,2))as varchar(20))+'%'优秀率
from StudentCourseScore
group by CourseId )t2
where t1.Id=t2.CourseId
group by t1.CourseName,t2.CourseId,t2.选修人数,t2.最高分,t2.最低分,t2.平均分,t2.及格率,t2.优良率,t2.中等率,t2.优秀率
order by count(*) desc,CourseId

---- 15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次
--select CourseId,Score,DENSE_RANK()over(partition by CourseId order by score desc)ranking from StudentCourseScore

---- 16.查询学生的总成绩，并进行排名，总分重复时保留名次空缺
--select StudentId,sum(Score)总成绩,RANK()over(order by sum(score)desc)ranking from StudentCourseScore group by StudentId

---- 16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
--select StudentId,sum(Score)总成绩,ROW_NUMBER()over(order by sum(score) desc)ranking from StudentCourseScore group by StudentId 

---- 17.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
select t1.CourseName,t2.* from  CourseInfo t1,
(select CourseId 课程编号,
sum(case when Score<=100 and score>85 then 1 else 0 end)'[100-85人数]',
cast(cast(cast(sum(case when Score<=100 and score>85 then 1 else 0 end) as decimal(5,2))/count(*)*100 as decimal(5,2))as varchar(20))+'%' as '[100-85]所占百分比',
sum(case when Score<=85 and score>70 then 1 else 0 end) '[85-70人数]',
cast(cast(cast(sum(case when Score<=85 and score>70 then 1 else 0 end) as decimal(5,2))/count(*)*100 as decimal(5,2))as varchar(20))+'%'as '[85-70]所占百分比',
sum(case when Score<=70 and score>60 then 1 else 0 end)'[70-60人数]',
cast(cast(cast(sum(case when Score<=70 and score>60 then 1 else 0 end)as decimal(5,2))/count(*)*100 as decimal(5,2))as varchar(20))+'%'as '[70-60]所占百分比',
sum(case when Score<=60 and score>0 then 1 else 0 end)'[60-0人数]',
cast(cast(cast(sum(case when Score<=60 and score>0 then 1 else 0 end)as decimal)/count(*)*100 as decimal(5,2))as varchar(20))+'%' as '[60-0人数]所占百分比'
from StudentCourseScore 
group by CourseId )t2
where t1.Id=t2.课程编号

---- 18.查询各科成绩前三名的记录
select * from 
(select *,rank()over(partition by courseId order by score)排名 from StudentCourseScore )a where a.排名<=3

---- 19.查询每门课程被选修的学生数
select CourseId,count(CourseId)选修的学生数 from StudentCourseScore group by CourseId

---- 20.查询出只选修两门课程的学生学号和姓名
select a.StudentCode,a.StudentName from StudentInfo a,
(select StudentId,count(*)课程数 from StudentCourseScore  group by StudentId having count(*)=2)b
where  a.Id=b.StudentId

---- 21.查询男生、女生人数
select sum(case when sex='m' then 1 else 0 end)男,sum(case when sex='f' then 1 else 0 end)女 from StudentInfo


---- 22.查询名字中含有「风」字的学生信息
select * from StudentInfo where StudentName like '%风%'

---- 23.查询同名同性学生名单，并统计同名人数
select StudentName,Sex,count(*)同名人数 from StudentInfo group by StudentName,Sex having count(*)>1 

---- 24.查询 1990 年出生的学生名单
select * from StudentInfo where Birthday like '1990%'

---- 25.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select CourseId,avg(Score)平均成绩 from StudentCourseScore group by CourseId order by AVG(score) desc,CourseId

---- 26.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
select a.StudentCode,a.StudentName,b.平均成绩 from StudentInfo a,
(select StudentId,AVG(Score)平均成绩 from StudentCourseScore group by StudentId having AVG(Score)>=85)b 
where a.Id=b.StudentId

---- 27.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
select a.StudentName,b.Score from StudentInfo a,
(select * from StudentCourseScore where CourseId in (select Id from CourseInfo where CourseName='数学') and Score<60)b
where a.Id=b.StudentId

---- 28.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
select a.StudentName, b.CourseId,b.Score from StudentInfo a left join
 StudentCourseScore b on a.Id=b.StudentId

---- 29.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
select a.StudentName,c.CourseName,b.Score from StudentInfo a,
(select * from StudentCourseScore where CourseId in (1,2,3) and Score>70)b,CourseInfo c
where a.Id=b.StudentId and b.CourseId=c.Id

---- 30.查询不及格的课程
select * from StudentCourseScore where Score<60

---- 31.查询课程编号为 1 且课程成绩在 80 分以上的学生的学号和姓名
select a.StudentCode,a.StudentName from StudentInfo a,
(select * from StudentCourseScore where CourseId=1 and Score>80)b where a.Id=b.StudentId

---- 32.求每门课程的学生人数
select CourseId,count(*)人数 from StudentCourseScore group by CourseId

---- 33.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select * from StudentInfo  a,
(select top 1 StudentId,max(score)最高成绩 from StudentCourseScore  where
CourseId=(select Id from CourseInfo where TeacherId=(select Id from Teachers where TeacherName='张三'))group by StudentId order by 最高成绩 desc)b
where a.Id=b.StudentId
----34.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select a.*,max(b.最高成绩) from StudentInfo  a,
(select StudentId,max(score)最高成绩 from StudentCourseScore  where
CourseId in(select Id from CourseInfo where TeacherId=(select Id from Teachers where TeacherName='张三')) )b
where a.Id=b.StudentId
group by a.Birthday,a.ClassId,a.Id,a.Sex,a.StudentCode,a.StudentName
--select * from StudentCourseScore
--select * from StudentInfo 

---- 35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select s.StudentCode,c.Id,scs.Score from StudentInfo s
left join StudentCourseScore scs on s.StudentCode=scs.StudentId
left join CourseInfo c on c.Id=scs.CourseId
where
(
select count(*) from StudentInfo s2
left join StudentCourseScore scs2 on s2.StudentCode=scs2.StudentId
left join CourseInfo c2 on c2.Id=scs2.CourseId
where scs2.Score=scs.Score and c.Id != c2.Id
)>1
---- 36.查询每门功成绩最好的前两名
select * from 
(select top 2 * from StudentCourseScore where CourseId=1 order by Score desc)a union
select * from 
(select top 2 * from StudentCourseScore where CourseId=2 order by Score desc)b union
select * from
(select top 2 * from StudentCourseScore where CourseId=3 order by Score desc)c
order by CourseId ,Score desc

---- 37.统计每门课程的学生选修人数（超过 5 人的课程才统计）。
select CourseId,count(*)选修人数 from StudentCourseScore group by CourseId having count(*)>5

---- 38.检索至少选修两门课程的学生学号
select StudentCode from StudentInfo where id in
(select StudentId from StudentCourseScore group by StudentId having count(*)>=2)

---- 39.查询选修了全部课程的学生信息
select * from StudentInfo where id in
(select StudentId from StudentCourseScore group by StudentId having count(*)=3)

---- 40.查询各学生的年龄，只按年份来算/getdate()现在时间 year(getdate())-year(Birthday)     datediff 计算两段时间差距,year年
select StudentCode,datediff(year , Birthday , getdate())as 年龄 from StudentInfo

---- 41.按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一   /right(getdate(),5)从右边开始数到第五位，列 1991-12-01，12-01
select * , case when right(getdate(),5) < right(birthday,5) then
datediff(year , Birthday , getdate()) - 1 else datediff(year , Birthday , getdate()) end  年龄  from StudentInfo

---- 42.查询本周过生日的学生
select * from StudentInfo

---- 43.查询下周过生日的学生

---- 44.查询本月过生日的学生

---- 45.查询下月过生日的学生