SQL进阶练习题21-25

#SQL进阶练习题21-25
大背景和建表、插入语句就不啰嗦了，参考第一篇。

四张表概要：

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 分数
   为了方便查看，我把四个表截了图：
   
   
   
   

题目：

21. 查询学生的总成绩，并进行排名，总分重复时保留名次空缺
22. 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
23. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
24. 查询各科成绩前三名的记录
25. 查询每门课程被选修的学生数

sql

查询学生的总成绩，并进行排名，总分重复时保留名次空缺

select s.sname,sum(sc.score) sum_score from student s join sc
on s.sid=sc.sid
group by s.sid
order by sum_score desc;

查询学生的总成绩，并进行排名，总分重复时不保留名次空缺

select s.sname,sum(sc.score) sum_score from student s join sc
on s.sid=sc.sid
group by s.sid
order by sum_score desc;

统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

select sc.cid,c.cname
,sum(case when score >=85 then 1 else 0 end) as '100-85'
,sum(case when score>=70 and score<85 then 1 else 0 end) as '85-70'
,sum(case when score>=60 and score<70 then 1 else 0 end) as '70-60'
,sum(case when score<60 then 1 else 0 end) as '60-0'
from sc left join course c
on sc.cid=c.cid
group by sc.cid,c.cname;

查询各科成绩前三名的记录

select a.cid,a.sid,a.score from sc a
left join sc b on a.cid=b.cid and a.score<b.score
group by a.sid,a.cid,a.score
having COUNT(b.sid)<3
order by a.cid,a.score desc;

查询每门课程被选修的学生数

select cid,count(*) from sc
group by cid;