数据库笔记7（多表查询）

#求每个班，每门课程的最高成绩和最低成绩，输出班级名，课程名，最高成绩，最低成绩

#(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

#派生表：查询出来的数据当临时表来用