sql练习3

-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
-- 17.1 SQL 2000 静态
SELECT a.SID,s.Sname,IFNULL((SELECT m.score from sc m WHERE m.SID=a.SID and m.CID='01'),0)'01课程成绩',
IFNULL((SELECT n.score from sc n WHERE n.SID=a.SID AND n.CID='02'),0)'02课程成绩',
IFNULL((SELECT y.score from sc y WHERE y.SID=a.SID AND y.CID='03'),0)'03课程成绩',
cast(AVG(a.score)AS DECIMAL(18,2))'平均成绩' from student s,sc a
WHERE s.SID=a.SID GROUP BY a.SID ORDER BY cast(AVG(a.score)AS DECIMAL(18,2))DESC

-- 18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
-- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
SELECT a.CID,c.Cname,MAX(a.score)'最高分',MIN(a.score)'最低分',
 cast(AVG(a.score) AS DECIMAL(18,2))'平均分',
CAST((SELECT count(1) from sc m WHERE m.CID=a.CID AND m.score>=60)*100.0/(
SELECT count(1) from sc n WHERE n.CID=a.CID  
) AS DECIMAL(18,2))'及格率',
CAST((SELECT count(1) FROM sc x WHERE x.CID=a.CID AND x.score BETWEEN 70 and 80
)*100.0/(SELECT COUNT(1) from sc y WHERE y.CID=a.CID) AS DECIMAL(18,2))'中等率',
CAST((SELECT COUNT(1) from sc WHERE sc.CID=a.CID and sc.score BETWEEN 80 and 90
)*100/(SELECT count(1) from sc WHERE sc.CID=a.CID) AS DECIMAL(18,2))'优良率',
CAST((SELECT COUNT(1) from sc WHERE sc.CID=a.CID and sc.score>=90)*100/
(SELECT count(1) from sc WHERE sc.CID=a.CID) AS DECIMAL(18,2))'优秀率'
from sc a,course c
WHERE a.CID=c.CID GROUP BY a.CID