--(1)查询每个总学时数

select GradeId,SUM(classHour) from subject

group by GradeId

order by(SUM(classHour))

--(2)查询每个考试学员的平均分

select studentNo,AVG(studentresult) from result

group by studentNo 

--(3)查询每门课程的平均分,降序排列

select subjectId,AVG(studentresult) from result

group by subjectId

order by AVG(studentresult) desc

--(4)查询每个学生考试总分,降序排序

select studentNo,sum(studentresult) from result

group by studentNo

order by sum(studentresult) desc

--(5)每学期学时超过50的课程数

select GradeId,count(classHour) as 学时超过50课程数 from subject

where classHour>50

group by gradeId

--(6)查询每学期学生的平均年龄

select GradeId,AVG(DATEDIFF(yyyy,birthday,GETDATE())) as 平均年龄

from student

group by GradeId

--(7)查询北京地区每学期学生人数

select GradeId,COUNT(1) as 北京地区学生人数

from student

where address like '%北京%'

group by GradeId

--(8)查询学生平均成绩及格的学生记录,降序排列

select studentNo,AVG(studentresult) as 平均成绩

from result

group by studentNo

having AVG(studentresult)>=60

order by 平均成绩 desc

 --(9)考试日期内的及格课程平均分

 select * from result

 select subjectid as 课程,AVG(studentresult) as 平均分

 from result

 --where examdate>'2013-2-15' and examdate<'2013-2-16'  可以写成这个

 where datepart(yyyy,examdate)=2013 and datepart(mm,examdate)=06 and datepart(dd,examdate)=30

 group by subjectId

 having AVG(studentresult)>=60

--(10)查询至少一次考试成绩不合格的学生学号,不及格次数

select studentNo as 学号,COUNT(0) as 考试不及格次数

from result

where studentResult<60

group by studentNo