本公众号使用SQL SERVER2014测试。SQL语句可能因数据库系统不同而存在少许差异。
测试数据请参照上一章节，各表查询结果如下图：
练习题目
12. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
14. 查询各科成绩最高分、最低分和平均分：
以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
15. 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次
16. 查询学生的总成绩，并进行排名，总分重复时保留名次空缺
16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
17. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0]及所占百分比
18. 查询各科成绩前三名的记录
19. 查询每门课程被选修的学生数
20. 查询出只选修两门课程的学生学号和姓名
参考答案
--------------------------------------------------------
select a.SId,a.Sname,avg(b.score) as 平均成绩from Student a, sc b where a.SId=b.SId and b.score < 60group by a.sid,a.Sname having count(*)>=2输出结果
--12. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
select a.*from Student a, sc b where a.SId=b.SId and b.score < 60 and b.CId=01 order by b.score desc输出结果
--13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select sc.SId,sc.CId,sc.score,t.avgs from sc,(select sc.SId,avg(score) as avgs from SC group by sc.SId)twhere sc.SId=t.SIdorder by t.avgs desc输出结果
--14. 查询各科成绩最高分、最低分和平均分：
-- 以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
-- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
-- 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select CId,count(*) as 选修人数,max(score) as 最高分,min(score) as 最低分,cast(avg(score) as decimal(5,2)) as 平均分,cast(cast(cast(sum(case when sc.score>=60 then 1 else end )as decimal(5,2)) /count(*)*100 as decimal(5,2)) as varchar(20))+'%%' as 及格率,cast(cast(cast(sum( case when sc.score>=70 and sc.score<80 then 1 else end )as decimal(5,2)) /count(*)*100 as decimal(5,2)) as varchar(20))+'%%' as 中等率,cast(cast(cast(sum( case when sc.score>=80 and sc.score<90 then 1 else end )as decimal(5,2)) /count(*)*100 as decimal(5,2)) as varchar(20))+'%%' as 优良率,cast(cast(cast(sum( case when sc.score>=90 then 1 else end )as decimal(5,2)) /count(*)*100 as decimal(5,2)) as varchar(20))+'%%' as 优秀率from SC group by cidorder by count(*) desc,cid输出结果
--15. 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
select sc.CId,sc.score ,rank() over (partition by cid order by sc.score desc) as num from sc输出结果
--15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次
select cid,sc.score ,dense_RANK() over (partition by cid order by sc.score desc) as num from SC输出结果
--16. 查询学生的总成绩，并进行排名，总分重复时保留名次空缺
select sid,sum(score) as score,rank() over (order by sum(score) desc) as num from sc group by SId输出结果
--16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
select sid,sum(score) as score,row_number() over (order by sum(score) desc) as num from sc group by SId输出结果
--17. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
select t1.Cname,t2.* from Course t1,(select SC.cid,sum(case when sc.score>=85 and sc.score<=100 then 1 else end) as [100-85],cast(cast(cast(sum(case when sc.score>=85 and sc.score<=100 then 1 else end) as decimal(5,2))/count(*)*100 as decimal(5,2))as varchar(20))+'%%' as '[100-85]所占百分比' ,sum(case when sc.score>=70 and sc.score<85 then 1 else end) as [85-70],cast(cast(cast(sum(case when sc.score>=70 and sc.score<85 then 1 else end) as decimal(5,2))/count(*)*100 as decimal(5,2))as varchar(20))+'%%' as '[85-70]所占百分比' ,sum(case when sc.score>=60 and sc.score<70 then 1 else end) as [70-60],cast(cast(cast(sum(case when sc.score>=60 and sc.score<70 then 1 else end) as decimal(5,2))/count(*)*100 as decimal(5,2))as varchar(20))+'%%' as '[70-60]所占百分比' ,sum(case when sc.score> and sc.score<60 then 1 else end) as [60-],cast(cast(cast(sum(case when sc.score> and sc.score<60 then 1 else end) as decimal(5,2))/count(*)*100 as decimal(5,2))as varchar(20))+'%%' as '[60-0]所占百分比'from SC group by SC.cid) t2where t1.CId=t2.CId输出结果
--18. 查询各科成绩前三名的记录
select * from sc where (select count(*) from sc t1 where t1.CId =sc.CId and t1.score>sc.score)<3order by cid , score desc输出结果
--19. 查询每门课程被选修的学生数
select cid,count(*) as 选修人数 from SCgroup by cid输出结果
--20. 查询出只选修两门课程的学生学号和姓名
select a.sid ,a.Sname from Student a,SC b where a.SId=b.SIdgroup by a.sid ,a.Sname having count(*)=2输出结果
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31537575/viewspace-3165344/ ，如需转载，请注明出处，否则将追究法律责任。
