# 数据库的有关知识==&gt;&gt;我们的血泪史之经典练习(1-2)

select SubjectId as 课程编号,AVG(studentresult) as 平均分    --04.投影结果
from Result                                                                     --01.定位到表
where SubjectId<3                                                          --02.分组前第一道过滤
group by SubjectId                                                          --03.分组
having COUNT(studentno)>3                                            --05.分组后第二道过滤
order by 课程编号 desc                                                     --06.最后排序

**************经典练习1*******************

--01.查询每个年级的总学时数，并按照升序排列

`select GradeId as 年级编号,SUM(ClassHour) as 总学时数from Subjectgroup by GradeIdorder by sum(ClassHour)`

--02.查询每个参加考试的学员平均分

`select studentno as 学生编号,AVG(studentresult) as 平局分from Resultgroup by StudentNo`

--03.查询每门课程的平均分，并按照降序排列

`select subjectid as 学科编号,AVG(studentresult)as 平均分from Resultgroup by SubjectIdorder by 平均分 desc --'平均分'也可以为'AVG(studentresult)'哟!`

--04.查询每个学生参加所有考试的总分，并按照降序排列

`select studentno as 学生编号,SUM(studentresult) as 总分from Resultgroup by StudentNoorder by 总分 desc --' 总分'也可以为'SUM(studentresult)'哟!`

`select gradeid as 年级编号,gender as 性别,COUNT(1) as 人数from studentgroup by GradeId,Genderorder by GradeId,Gender`

--06.每个年级的总人数，满足总人数必须大于等于3

`select gradeid as 年级编号,COUNT(1) as 总人数from studentgroup by GradeIdhaving COUNT(1)>=3select * from Student`

*****************经典练习2****************

--01.查询每年级学时数超过50 的课程数 S1

`select gradeid as年级编号,COUNT(subjectid) as 课程数from Subjectwhere ClassHour>50group by GradeId`

--02.查询每年级学生的平均年龄:

`select gradeid as 年级编号,AVG(DATEDIFF(yy,birthday,GETDATE())) as 平均年龄from studentgroup by gradeid`

--03.查询每个年级包含北京的学生总数 (like '%北京%')

`select gradeid as 年级编号,COUNT(1) as 总人数from studentwhere address like '%北京%'group by Gradeid`

--04.查询参加考试的学生中，平均分及格的学生记录(学号，平均分)，按照降序排列

`select studentno as 学生编号,AVG(StudentResult) as 平均分from Resultgroup by StudentNohaving AVG(StudentResult)>=60order by 平均分 desc --'平均分'也可改为'AVG(StudentResult)'哟!`

--05.查询考试时间为2015-12-20课程的及格平均分 (课程编号    所有课程平均分)

`select subjectid as 学科编号,AVG(studentresult) as 平均分from Resultwhere ExamDate>='2015-11-20' and ExamDate<'2015-11-20'group by SubjectIdhaving AVG(StudentResult)>=60`

--06.统计至少有一次不及格的学生学号和次数。

`select studentno as 学生编号,COUNT(1) as 次数from Resultwhere StudentResult<60group by StudentNo`