17、查询各科成绩最高分、最低分和平均分:
以如下形式显示:课程 ID,
,及格率,中等率,优良率,优秀率
及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
①、
select 01 AS cid,
max(score),
min(score),
avg(score),
count(*),
(sum(case when score>=60 then 1 else 0 end)*100/count(*) ) AS 及格率,
(sum(case when score >=70 AND score<80 then 1 else 0 end)*100/count(*)) AS 中等率,
(sum(case when score >=80 AND score<90 then 1 else 0 end)*100/count(*)) AS 优良率,
(sum(case when score>=90 then 1 else