7-26.sql

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

USE Educational
GO
SELECT C.ClassID, 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
GROUP BY  C.ClassID, C.ClassName,  Co.CourseName
HAVING AVG(Grade) > 85
ORDER BY  C.ClassID