十一章 上机练习二

--上机练习二
--NO.1
SELECT Gradeid AS 学期,COUNT(*) AS 超过50的课程数目 FROM dbo.Subject GROUP BY Gradeid HAVING SUM(ClassHour)>50
--NO.2
SELECT Gradeid AS 学期,AVG(DATEDIFF(dd,BornDate,GETDATE())/365) AS 平均年龄 FROM Student GROUP BY GradeId
--NO.3
SELECT Gradeid AS 学期,COUNT(*) AS 人数 FROM Student  WHERE Address LIKE '北京%'  GROUP BY Gradeid
--NO.4
SELECT StudentNo AS 学号,AVG(StudentResult)AS 成绩 FROM Result GROUP BY StudentNo  HAVING AVG(StudentResult)>=60 ORDER BY AVG(StudentResult)DESC
--NO.5
SELECT Subjectid AS 学科名称,AVG(StudentResult)AS 平均分 FROM dbo.Result WHERE ExamDate='2013-3-22' GROUP BY Subjectid HAVING AVG(StudentResult)>60
--NO.6
SELECT StudentNo AS 学生编号,COUNT (StudentResult)AS 不及格次数  FROM dbo.Result WHERE StudentResult<60 GROUP BY StudentNo