select * from StudentCourseScore
select * from CourseInfo
select * from StudentInfo
select * from Teachers

-- 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.CourseId 课程编号,b.Score 成绩 from 
(select * from StudentCourseScore where CourseId=1)a  left join 
(select * from StudentCourseScore where CourseId=2)b on a.StudentId=b.StudentId

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

-- 2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select StudentCode,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 * from StudentInfo where Id in
(select StudentId from StudentCourseScore group by StudentId)

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

-- 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 )

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

	--学了但没有学全
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=
(select Id from StudentInfo where StudentCode=01)) group by StudentId)

-- 9.查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
select * from StudentInfo where Id in
(select StudentId from StudentCourseScore where CourseId in
(select CourseId from StudentCourseScore where StudentId=
(select Id from StudentInfo where StudentCode=01)) group by StudentId having count(*)=3)

-- 10.查询没学过"张三"老师讲授的任一门课程的学生姓名
select * from StudentInfo where Id not in
(select StudentId from StudentCourseScore where CourseId =
(select id from Teachers where TeacherName='张三'))

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

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

-- 13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select a.*,b.Score 语文成绩,c.Score 数学成绩,d.Score 英语成绩 from 
(select StudentId,AVG(Score)平均成绩 from StudentCourseScore group by StudentId)a left join
(select * from StudentCourseScore where CourseId=1) b on  a.StudentId=b.StudentId left join
(select * from StudentCourseScore where CourseId=2) c on  a.StudentId=c.StudentId left join
(select * from StudentCourseScore where CourseId=3) d on  a.StudentId=d.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
	要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
	按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
*/	
--DECIMAL(5,2)的意思：最大5位数字，其中两位小数的数字类型。
select b.CourseName 课程name,a.* from 
(select CourseId 课程ID,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))+'%'as 中等率,
	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))+'%'as 优良率,
	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))+'%'as 优秀率
from StudentCourseScore group by CourseId)a join
(select * from CourseInfo)b on a.课程ID=b.Id

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

-- 16.查询学生的总成绩，并进行排名，总分重复时保留名次空缺 --相同的归为一组且每组序号一样,排序不会连续执行 
select  StudentId,SUM(Score)总成绩,RANK()over(order by sum(score)desc)总成绩排名 from StudentCourseScore group by StudentId

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

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

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

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

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

-- 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 StudentCode,StudentName,平均成绩 from StudentInfo a join 
(select StudentId,avg(score)平均成绩 from StudentCourseScore group by StudentId having AVG(score)>85)b on a.Id=b.StudentId

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

-- 28.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
select a.Studentname, b.Score 语文成绩,c.Score 数学成绩,d.Score 英语成绩 from StudentInfo a  
left join StudentCourseScore b on a.Id=b.StudentId and b.CourseId=1
left join StudentCourseScore c on a.Id=c.StudentId and c.CourseId=2
left join StudentCourseScore d on a.Id=d.StudentId and d.CourseId=3

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




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

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

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

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


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

-- 36.查询每门功成绩最好的前两名

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

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

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

-- 40.查询各学生的年龄，只按年份来算

-- 41.按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

-- 42.查询本周过生日的学生

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

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

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