7-24.sql

来自「SQL2000跟课件学习的源代码 分享共同学习」· SQL 代码 · 共 13 行

USE Educational
GO
SELECT C.ClassName 班名, Co.CourseName 课程, MAX(Grade) 最高分, 
MIN(Grade) 最低分, AVG(Grade) 平均分
FROM Grade G
  INNER JOIN Student S  ON  S.StudentID = G.StudentID
  INNER JOIN Class C ON C.ClassID = S.ClassID
  INNER JOIN Speciality_Course SC ON C.SpecialityID = SC.SpecialityID
 and SC.CourseID=G.CourseID
  INNER JOIN Course Co ON Co.CourseID = G.CourseID
WHERE  C.ClassID = '030501'  and  SC.Term = 5
GROUP BY  C.ClassName,  Co.CourseName