第十一章上机练习

--查询每个年级的总学时数，并按照升序排列。SELECT GradeId AS 年级,sum(ClassHour) as 总学时数FROM [MySchool].[dbo].[Subject]group by GradeIdorder by GradeId--查询每个参加考试的学员的平均分。SELECT StudentNo AS 学号,avg(StudentResult) as 平均分FROM [MySchool].[dbo].[Result]group by StudentNo--查询每门课程的平均分，并按照降序排列。Select SubjectId,avg(StudentResult) as 平均分FROM [MySchool].[dbo].[Result]group by SubjectIdorder by avg(StudentResult) desc--查询每个学生参加的所有考试的总分，并按照降序排列。Select StudentNo,sum(StudentResult) as 总分FROM [MySchool].[dbo].[Result]group by StudentNoorder by sum(StudentResult) desc--查询每学期学时数超过50的课程数。select GradeId, count(SubjectName) as 科目,sum(ClassHour) as 课时from [MySchool].[dbo].[Subject]group by GradeIdhaving sum(ClassHour)>50--查询每学期学生的平均年龄。select GradeId,avg(DATEDIFF(dd,borndate,getdate())/365)from Studentgroup by  GradeId--查询北京地区的每学期学生人数。select gradeid,count(studentName) as 人数 from studentwhere Address like '北京%'group by gradeid--查询参加考试的学生中，平均分及格的学生记录，并按照成绩降序排列select studentNo,AVG(studentresult)from resultgroup by studentNohaving AVG(studentresult)>=60order by AVG(studentresult) desc--查询考试日期2013-3-22的课程的及格平均分。select subjectId as 课程,AVG(studentresult) as 课程平均分from resultwhere ExamDate='2013-3-22'group by subjectIdhaving AVG(studentresult)>=60--查询参加考试至少一次考试不及格的学生学号、不及格次数select studentno as 学号,COUNT(studentresult) as 不及格次数from resultwhere studentresult<60group by studentno--查询学生姓名、所属年级名称及联系电话。select s.studentname, g.GradeName,s.phonefrom [MySchool].[dbo].[Student] s,[MySchool].[dbo].[Grade] gwhere s.gradeid= g.gradeidselect s.studentname, g.GradeName,s.phonefrom [MySchool].[dbo].[Student] sinner join [MySchool].[dbo].[Grade] g on s.gradeid= g.gradeid--查询年级编号为1的科目名称、年级名称及学时。select s.GradeName, g.SubjectName,g.ClassHourfrom [MySchool].[dbo].[Grade] s,[MySchool].[dbo].[Subject] gwhere s.gradeid= g.gradeid and s.gradeId=1select s.GradeName, g.SubjectName,g.ClassHourfrom [MySchool].[dbo].[Grade] sinner join [MySchool].[dbo].[Subject] g on s.gradeid= g.gradeid and s.gradeId=1--查询参加科目编号为1的考试的学生姓名、分数、考试日期。select studentname,StudentResult,examDatefrom [MySchool].[dbo].[Result] s,[MySchool].[dbo].[Student] gwhere s.studentno=g.studentno and SubjectId=1select studentname,StudentResult,examDatefrom [MySchool].[dbo].[Result] sinner join [MySchool].[dbo].[Student] g on s.studentno=g.studentno and SubjectId=1--查询学号为S1101007的学生参加的考试科目名称、分数、考试日期。select Subjectname,StudentResult,examDatefrom [MySchool].[dbo].[Subject] s,[MySchool].[dbo].[Result] gwhere s.SubjectId=g.SubjectId and g.studentno='S1101007'select Subjectname,StudentResult,examDatefrom [MySchool].[dbo].[Subject] sinner join [MySchool].[dbo].[Result] g on s.SubjectId=g.SubjectId and g.studentno='S1101007'--查询参加考试的学生学号、所考科目名称、分数、考试日期.select studentno,Subjectname,StudentResult,examDatefrom [MySchool].[dbo].[Subject] s,[MySchool].[dbo].[Result] gwhere s.SubjectId=g.SubjectId select studentno,Subjectname,StudentResult,examDatefrom [MySchool].[dbo].[Subject] sinner join [MySchool].[dbo].[Result] g on s.SubjectId=g.SubjectId --查询学生学号、姓名、考试科目名称及成绩。select s.studentno,studentname,subjectname,studentResultfrom [MySchool].[dbo].[student] s,[MySchool].[dbo].[Result] g,[MySchool].[dbo].[subject] awhere s.studentno=g.studentno and g.subjectId=a.subjectIdselect s.studentno,studentname,subjectname,studentResultfrom [MySchool].[dbo].[student] sinner join [MySchool].[dbo].[Result] g on s.studentno=g.studentnoinner join [MySchool].[dbo].[subject] a on g.subjectId=a.subjectId--查询参加“走进Java编程世界”考试的学生姓名、成绩、考试日期。select s.studentname,g.studentResult,g.ExamDatefrom [MySchool].[dbo].[student] s,[MySchool].[dbo].[Result] g,[MySchool].[dbo].[subject] awhere s.studentno=g.studentno and g.subjectId=a.subjectId and subjectname='深入.NET平台和C#编程'select s.studentname,g.studentResult,g.ExamDatefrom [MySchool].[dbo].[student] sinner join [MySchool].[dbo].[Result] g on s.studentno=g.studentnoinner join [MySchool].[dbo].[subject] a on g.subjectId=a.subjectIdwhere subjectname='深入.NET平台和C#编程'--查询所有科目的参考信息（某些科目可能还没有被考试过）select SubjectName,studentNo,studentResultfrom [MySchool].[dbo].[Subject] s left outer join [MySchool].[dbo].[Result] a on s.SubjectId=a.SubjectId--查询没有被考过的科目信息。select SubjectName,studentNo,studentResultfrom [MySchool].[dbo].[Subject] s left outer join [MySchool].[dbo].[Result] a on s.SubjectId=a.SubjectIdwhere studentNo is null and studentResult is null--所有年级对应的学生信息（需要考虑某些年级可能还没有学生就读）select GradeName,studentNo,studentNamefrom [MySchool].[dbo].[Grade] s left outer join [MySchool].[dbo].[Student] a on s.GradeId=a.GradeId