上机练习1
--查询每个年级的总学时数,并按照升序排列。
SELECT SUM(ClassHour) AS 总学时,GradeId AS 年级 FROM Subject GROUP BY GradeId
--查询每个参加考试的学员的平均分。
SELECT AVG(StudentResult) AS 平均分,StudentNo AS 学号 FROM Result GROUP BY StudentNo
--查询每门课程的平均分,并按照降序排列。
SELECT AVG(StudentResult) AS 平均分,SubjectId AS 课程号 FROM Result GROUP BY SubjectId ORDER BY AVG(StudentResult) DESC
--查询每个学生参加的所有考试的总分,并按照降序排列。
SELECT SUM(StudentResult) AS 总分,StudentNo AS 学号 FROM Result GROUP BY StudentNo ORDER BY SUM(StudentResult) DESC
上机练习2
--查询每学期学时数超过50的课程数。
SELECT COUNT(*) AS 学时超过50课程数,GradeId AS 年级 FROM Subject
WHERE ClassHour>=50
GROUP BY GradeId
--查询每学期学生的平均年龄。
SELECT AVG(DATEDIFF(dd,BornDate,GETDATE())/365) AS 平均年龄,GradeId AS 年级 FROM Student
GROUP BY GradeId
--查询北京地区的每学期学生人数。
SELECT COUNT(*) AS 北京地区总人数,GradeId AS 年级 FROM Student
WHERE Address LIKE '%北