十一章 上机2

SELECT COUNT(ClassHour) AS 课程数,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 '%北京%'
GROUP BY GradeId

-------------------------------------------------------------------------
SELECT StudentNo AS 学号,AVG(StudentResult) AS 平均分
FROM Result
GROUP BY StudentNo
HAVING AVG(StudentResult)>=60
ORDER BY AVG(StudentResult) DESC

-------------------------------------------------------------------------
SELECT AVG(StudentResult) AS 及格平均分,SubjectId AS 课程
FROM Result
WHERE ExamDate = '2013-3-22' AND StudentResult >=60
GROUP BY SubjectId

-------------------------------------------------------------------------
SELECT StudentNo AS 学号,COUNT(StudentNo) AS 不及格次数
FROM Result
WHERE StudentResult<60
GROUP BY StudentNo