#求每个班,每门课程的最高成绩和最低成绩,输出班级名,课程名,最高成绩,最低成绩
#(18条)
select classname,coursename,max(grade),min(grade)
from grade,course,class,student
where grade.courseid=course.courseid and grade.studentid=student.studentid and student.classid=class.classid
group by class.classid,course.courseid
#having max(grade)>90
#查询本门课成绩比该课程平均成绩低的同学的姓名,课程号和成绩,最终结果按照课程号升序,每个课程内部成绩降序排列(38条)
select studentname,courseid,grade
from student,grade a
where student.studentid=a.studentid and grade <(
select avg(grade)
from grade
where courseid=a.courseid
)
order by courseid asc,grade desc
#派生表:查询出来的数据当临时表来用