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

select * from student where s_id in 
(select s_id from score where c_id = 1 and s_score < 60);

第十七题

select s.s_id,s.s_name,
sum(case c_id when 1 then s_score else 0 end) as "语文",
sum(case c_id when 2 then s_score else 0 end) as "数学",
sum(case c_id when 3 then s_score else 0 end) as "英语",
round(avg(sc.s_score),2) as "平均成绩" from student s inner join score sc on s.s_id = sc.s_id 
group by sc.s_id order by avg(sc.s_score) desc;

第十八题
查询各科成绩最高分、最低分和平均分，以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率，及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

select sc.c_id as "课程ID",c.c_name as "课程name",max(sc.s_score) as "最高分",
min(sc.s_score) as "最低分",avg(sc.s_score) as "平均分",
concat(round(sum(case when s_score>=60 then 1 else 0 end)/count(*)*100,2),"%") as "及格率",
concat(round(sum(case when s_score between 70 and 80 then 1 else 0 end)/count(*)*100,2),"%") as "中等率",
concat(round(sum(case when s_score between 80 and 90 then 1 else 0 end)/count(*)*100,2),"%") as "优良率",
concat(round(sum(case when s_score>=90 then 1 else 0 end)/count(*)*100,2),"%") as "优秀率"
from score sc left join course c on sc.c_id = c.c_id group by sc.c_id;

第十九题
按各科成绩进行排序，并显示排名

select sc.c_id , c.c_name,s.s_id,s.s_name,sc.s_score,
(rank() over (partition by c_id order by s_score desc)) as "rank"
from student s inner join score sc on s.s_id = sc.s_id 
inner join course c on sc.c_id = c.c_id;

第二十题
查询学生的总成绩并进行排名

select s.s_id,s.s_name,sum(sc.s_score) as total,
(rank() over(order by sum(sc.s_score) desc)) as r1
from student s inner join score sc on s.s_id = sc.s_id group by sc.s_id