www.jianshu.com/p/c4e052d48…
详细数据表见上篇《SQL练习经典50题(一)》。
练习题10道：
11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
12.检索" 01 "课程分数小于 60，按分数降序排列的学生信息
13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
14.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程 ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率。其中及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
15.按各科成绩进行排序，并显示排名，Score 重复时保留名次空缺
16.查询学生的总成绩，并进行排名，总分重复时保留名次空缺
16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
17.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
18.查询各科成绩前三名的记录
19.查询每门课程被选修的学生数
20.出只选修两门课程的学生学号和姓名
答案：
11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
思路:各同学的成绩至少2门不及格(sc),学生信息(student)和平均成绩(sc)
select a.id,a.name,cast(avg(score) as decimal(8,2))
from student a,sc
where a.id=sc.sid
and id in(select sid from sc
where score<60 group by sid having count(*)>1)
group by sid；
或者：
select a.id,a.name,avg(sc.score) from student a,sc
where a.id = sc.sid and sc.score < 60
group by sc.sid
having count(*) > 1
12.检索" 01 "课程分数小于 60，按分数降序排列的学生信息
思路:上过'01'课程且分数<60的学生(sc),学生信息(student),按分数降序
select a.*,t.score
from student a,
(select sid,score from sc
where score<60 and cid='01') t
where a.id=t.sid
order by score desc;
13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
思路:所有成绩/平均成绩(sc),平均成绩降序排列
select * from sc
left join
(select sid,avg(score) as avg_score from sc
group by sid) as t
on sc.sid=t.sid
order by t.avg_score desc;
14.查询各科成绩最高分、最低分和平均分：
以如下形式显示：课程 ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率。
其中及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
思路:最高分最低分平均分(sc),课程名(course)
select cid,max(score) 最高分,min(score) 最低分,
avg(score) 平均分,count(*) 选修人数,
sum(case when score>=60 then 1 else 0 end)/count(*) as 及格率,
sum(case when score>=70 and score<80 then 1 else 0 end)/count(*) as 中等率,
sum(case when score>=80 and score<90 then 1 else 0 end)/count(*) as 良好率,
sum(case when score>=90 then 1 else 0 end)/count(*) as 优秀率
from sc
group by cid
order by count(*) desc,cid asc;
注意：率的计算！
15.按各科成绩进行排序，并显示排名，Score 重复时保留名次空缺
思路:按...分组并在组内排名,考察rank () over(partition by...order by...)用法
select cid,sid,score,rank () over (partition by cid order by score desc) rank_
from sc;
注意：rank over/dense_rank/row_number（保留/重复/严格名次）
16.查询学生的总成绩，并进行排名，总分重复时保留名次空缺
select sid,sum_sc,rank () over (order by sum_sc desc) rank_
from (select sid,sum(score) sum_sc from sc
group by sid) t;
16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
select sid,total,dense_rank () over (order by total desc) rank_
from (select sid,sum(score) total from sc
group by sid) t;
或:自定义会话变量@crank:=@crank+1
set @crank=0;
select sid, total, @crank := @crank +1 as rank_ from(
select sid, sum(score) total from sc
group by sid
order by total desc) t;
17.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
select sc.cid,course.name,
concat(round(sum(case when sc.score>=85 then 1 else 0 end)/count(*)*100,2),'%') '[100-85]百分比',
concat(round(sum(case when sc.score<85 and sc.score>=70 then 1 else 0 end)/count(*)*100,2),'%') '[85-70]百分比',
concat(round(sum(case when sc.score<70 and sc.score>=60 then 1 else 0 end)/count(*)*100,2),'%') '[70-60]百分比',
concat(round(sum(case when sc.score<60 then 1 else 0 end)/count(*)*100,2),'%') '[60-0]百分比'
from sc left join course
on sc.cid=course.id
group by sc.cid;
注意:concat(a,b)—连接,round(a,k)—四舍五入保留k位小数
**18.查询各科成绩前三名的记录 **
select * from sc
where
(select count(*) from sc a
where sc.cid=a.cid and sc.score<a.score)<3
order by cid asc,sc.score desc;
注意理解！
19.查询每门课程被选修的学生数
select cid,count(*)
from sc
group by cid;
