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

SELECT c.c_id,c.c_name,MAX(sc.s_score) "最高分",MIN(sc.s_score) "最低分",AVG(sc.s_score) "平均分" ,((SELECT COUNT(s_id) FROM score WHERE s_score>=60 AND c_id=c.c_id )/(SELECT COUNT(s_id) FROM score WHERE c_id=c.c_id)) "及格率" ,((SELECT COUNT(s_id) FROM score WHERE s_score>=70 AND s_score<80 AND c_id=c.c_id )/(SELECT COUNT(s_id) FROM score WHERE c_id=c.c_id)) "中等率" ,((SELECT COUNT(s_id) FROM score WHERE s_score>=80 AND s_score<90 AND c_id=c.c_id )/(SELECT COUNT(s_id) FROM score WHERE c_id=c.c_id)) "优良率" ,((SELECT COUNT(s_id) FROM score WHERE s_score>=90 AND c_id=c.c_id )/(SELECT COUNT(s_id) FROM score WHERE c_id=c.c_id)) "优秀率" FROM course c LEFT JOIN score sc ON sc.c_id=c.c_id GROUP BY c.c_id