//我们要用就以学生成绩为实例吧
/*
结构
学生表
student(s#,sname,sage,ssex) --s# 学生编号,sname 学生姓名,sage 出生年月,ssex 学生性别
--2.课程表
course(c#,cname,t#) --c# --课程编号,cname 课程名称,t# 教师编号
*/
查询各科成绩最高分、最低分和平均分：以如下形式显示：课程id，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
--及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
--方法1
select m.c# [课程编号], m.cname [课程名称],
max(n.score) [最高分],
min(n.score) [最低分],
cast(avg(n.score) as decimal(18,2)) [平均分],
cast((select count(1) from sc where c# = m.c# and score >= 60)*100.0 / (select count(1) from sc where c# = m.c#) as decimal(18,2)) [及格率(%)],
cast((select count(1) from sc where c# = m.c# and score >= 70 and score cast((select count(1) from sc where c# = m.c# and score >= 80 and score cast((select count(1) from sc where c# = m.c# and score >= 90)*100.0 / (select count(1) from sc where c# = m.c#) as decimal(18,2)) [优秀率(%)]
from course m , sc n
where m.c# = n.c#
group by m.c# , m.cname
order by m.c#
--方法2
select m.c# [课程编号], m.cname [课程名称],
(select max(score) from sc where c# = m.c#) [最高分],
(select min(score) from sc where c# = m.c#) [最低分],
(select cast(avg(score) as decimal(18,2)) from sc where c# = m.c#) [平均分],
cast((select count(1) from sc where c# = m.c# and score >= 60)*100.0 / (select count(1) from sc where c# = m.c#) as decimal(18,2)) [及格率(%)],
cast((select count(1) from sc where c# = m.c# and score >= 70 and score cast((select count(1) from sc where c# = m.c# and score >= 80 and score cast((select count(1) from sc where c# = m.c# and score >= 90)*100.0 / (select count(1) from sc where c# = m.c#) as decimal(18,2)) [优秀率(%)]
from course m
order by m.c#