sql上机练习2

USE MySchool

--查询每学期学时数超过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 '%北京%'
GROUP BY GradeId

--查询参加考试的学生中，平均分及格的学生记录，并按照成绩降序排列
SELECT AVG(StudentResult) AS 平均分,StudentNo AS 学号 FROM Result 
GROUP BY StudentNo
HAVING AVG(StudentResult)>=60
ORDER BY AVG(StudentResult) DESC

--查询考试日期2013-3-22的课程的及格平均分。
SELECT AVG(StudentResult)AS 及格平均分,SubjectId AS 课程 FROM Result
WHERE StudentResult>=60 AND ExamDate='2013-3-22'
GROUP BY SubjectId

--查询参加考试至少一次考试不及格的学生学号、不及格次数。
SELECT StudentNo AS 学号,COUNT(*) AS 不及格次数 FROM Result 
WHERE StudentResult<60
GROUP BY StudentNo