mysql 练习

原文地址 https://www.cnblogs.com/coder-wf/p/11128033.html

对某些题可以精简

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

SELECT Course.c_id, Course.c_name, MAX(Score.s_score), MIN(Score.s_score), AVG(Score.s_score) ,
(SELECT COUNT(1) FROM Score WHERE s_score >= 60 AND Score.c_id = Course.c_id)/(SELECT COUNT(1) FROM Score WHERE Score.c_id = Course.c_id) AS ‘及格率‘,
(SELECT COUNT(1) FROM Score WHERE s_score >= 70 AND Score.c_id = Course.c_id)/(SELECT COUNT(1) FROM Score WHERE Score.c_id = Course.c_id) AS ‘中等率‘,
(SELECT COUNT(1) FROM Score WHERE s_score >= 80 AND Score.c_id = Course.c_id)/(SELECT COUNT(1) FROM Score WHERE Score.c_id = Course.c_id) AS ‘优良率‘,
(SELECT COUNT(1) FROM Score WHERE s_score >= 90 AND Score.c_id = Course.c_id)/(SELECT COUNT(1) FROM Score WHERE Score.c_id = Course.c_id) AS ‘优秀率‘
FROM Course LEFT JOIN Score ON
Course.c_id = Score.c_id
GROUP BY Course.c_id