SQL进阶练习题16-20

大背景和建表、插入语句就不啰嗦了，参考第一篇。

四张表概要：

1. 学生表
   student(sid,sname,sage,ssex) --sid 学生编号,sname 学生姓名,sage 出生年月,ssex 学生性别
2. 课程表
   course(cid,cname,tid) --cid 课程编号,cname 课程名称,tid 教师编号
3. 教师表
   teacher(tid,tname) --tid 教师编号,tname 教师姓名

4. 成绩表
   sc(sid,cid,score) --sid 学生编号,cid 课程编号,score 分数
   为了方便查看，我把四个表截了图：

   
   student
   course
   teacher
   sc

题目：

16. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
17. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
18. 查询各科成绩最高分、最低分和平均分：以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
19. 按各科成绩进行排序，并显示排名， score 重复时保留名次空缺
20. 按各科成绩进行排序，并显示排名， score 重复时合并名次

sql

检索" 01 "课程分数小于 60，按分数降序排列的学生信息

select score from sc
where score < 60
order by score desc;

按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

select sc.sid,sc.score,a.avg from sc
right join (select sid,avg(score) avg from sc
group by sid
order by avg desc) a
on sc.sid=a.sid;

查询各科成绩最高分、最低分和平均分：以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select c.cid,c.cname,a.max,a.min,a.avg,b.jg,b.zd,b.yl,b.yx from course c
left join (select cid,max(score) max,min(score) min,avg(score) avg
from sc group by cid) a
on c.cid=a.cid
left join
(select
cid
,sum(case when score>=60 then 1 else 0 end)/count(cid)*100 jg
,sum(case when score>=70 and score<80 then 1 else 0 end)/count(cid) zd
,sum(case when score>=80 and score<90 then 1 else 0 end)/count(cid) yl
,sum(case when score>=90 then 1 else 0 end)/count(cid) yx
from sc group by cid) b
on b.cid=c.cid;

按各科成绩进行排序，并显示排名， score 重复时保留名次空缺

--mysql中没有rank、row_number等函数,这道题不会做。下面的答案只是步长为一的排名
select @row:=case when @row is null then 1 else @row+1 end as mingci,sc.* from sc,(select @row:=0) a order by sc.score desc;

按各科成绩进行排序，并显示排名， score 重复时合并名次

--暂时没想到怎么做