18.

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

SELECT
    a.c_id,
  a.c_name,
  MAX(s_score),
  MIN(s_score),
  AVG(s_score),
  SUM(CASE WHEN s_score >= 60 THEN 1 ELSE 0 END)/COUNT(1) jige,
  SUM(CASE WHEN s_score >= 70 AND s_score < 80 THEN 1 ELSE 0 END)/COUNT(1) zhongdeng,
  SUM(CASE WHEN s_score >=90 THEN 1 ELSE 0 END) / COUNT(1) youxiu
FROM
    Course a 
LEFT JOIN
    Score b
ON a.c_id = b.c_id
GROUP BY a.c_id

也可以这样写

select a.c_id,b.c_name,MAX(s_score),MIN(s_score),ROUND(AVG(s_score),2),
    ROUND(100*(SUM(case when a.s_score>=60 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 及格率,
    ROUND(100*(SUM(case when a.s_score>=70 and a.s_score<=80 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 中等率,
    ROUND(100*(SUM(case when a.s_score>=80 and a.s_score<=90 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 优良率,
    ROUND(100*(SUM(case when a.s_score>=90 then 1 else 0 end)/SUM(case when a.s_score then 1 else 0 end)),2) as 优秀率
    from score a left join course b on a.c_id = b.c_id GROUP BY a.c_id,b.c_name