SQL查询

                                     

注意:

以下代码简洁易懂请认真仔细观看!

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

1 select GradeId,SUM(ClassHour) as 总学时数

2 from Subject

3 group by GradeId

4 order by sum(ClassHour)

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

1 select studentno,AVG(studentresult) as 平局分

2 from Result

3 group by StudentNo

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

1 select subjectid,AVG(studentresult)as 平均分

2 from Result

3 group by SubjectId

4 order by 平均分 desc

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

1 select studentno,SUM(studentresult) as 总分

2 from Result

3 group by StudentNo

4 order by 总分 desc

二、 

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

 

1 select gradeid,COUNT(subjectid) as 课程数

2 from Subject

3 where  ClassHour>50

4 group by GradeId

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

 

1 select gradeid,AVG(DATEDIFF(yy,birthday,GETDATE())) as 平均年龄

2 from student

3 group by gradeid

 

3--查询每个年级包含北京的学生

1 select gradeid,COUNT(1) as 总人数

2 from student

3 where address like '%北京%'

4 group by Gradeid

 

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

1 select studentno,AVG(StudentResult) as 平均分

2 from Result

3 group by StudentNo

4 having AVG(StudentResult)>=60

5 order by 平均分 desc

 

5--查询考试时间为2009-9-9课程的及格平均分

1 select subjectid,AVG(studentresult) as 平均分

2 from Result

3 where ExamDate>='2009-9-9' and  ExamDate<'2009-9-10'

4 group by SubjectId

5 having AVG(StudentResult)>=60

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

1 select studentno,COUNT(1) as  次数

2 from Result

3 where StudentResult<60

4 group by StudentNo