十一章 上机2

SELECT COUNT(ClassHour) AS 课程数,GradeId AS 学期FROM SubjectWHERE ClassHour>50GROUP BY GradeId-------------------------------------------------------------------------SELECT AVG(DATEDIFF(dd,BornDate,GETDATE())/365) AS 平均年龄,GradeId AS 学期FROM StudentGROUP BY GradeId-------------------------------------------------------------------------SELECT COUNT(*) AS 每学期学生人数,GradeId AS 学期FROM StudentWHERE Address LIKE '%北京%'GROUP BY GradeId-------------------------------------------------------------------------SELECT StudentNo AS 学号,AVG(StudentResult) AS 平均分FROM ResultGROUP BY StudentNoHAVING AVG(StudentResult)>=60ORDER BY AVG(StudentResult) DESC-------------------------------------------------------------------------SELECT AVG(StudentResult) AS 及格平均分,SubjectId AS 课程FROM ResultWHERE ExamDate = '2013-3-22' AND StudentResult >=60GROUP BY SubjectId-------------------------------------------------------------------------SELECT StudentNo AS 学号,COUNT(StudentNo) AS 不及格次数FROM ResultWHERE StudentResult<60GROUP BY StudentNo