第十一章上机练习

--查询每个年级的总学时数，并按照升序排列。
SELECT GradeId AS 年级,sum(ClassHour) as 总学时数
FROM [MySchool].[dbo].[Subject]
group by GradeId
order 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 SubjectId
order by avg(StudentResult) desc
--查询每个学生参加的所有考试的总分，并按照降序排列。
Select StudentNo,sum(StudentResult) as 总分
FROM [MySchool].[dbo].[Result]
group by StudentNo
order by sum(StudentResult) desc
--查询每学期学时数超过50的课程数。
select GradeId, count(SubjectName) as 科目,sum(ClassHour) as 课时
from [MySchool].[dbo].[Subject]
group by GradeId
having sum(ClassHour)>50
--查询每学期学生的平均年龄。
select GradeId,avg(DATEDIFF(dd,borndate,getdate())/365)
from Student
group by  GradeId
--查询北京地区的每学期学生人数。
select gradeid,count(studentName) as 人数 
from student
where Address like '北京%'
group by gradeid
--查询参加考试的学生中，平均分及格的学生记录，并按照成绩降序排列
select studentNo,AVG(studentresult)
from result
group by studentNo
having AVG(studentresult)>=60
order by AVG(studentresult) desc
--查询考试日期2013-3-22的课程的及格平均分。
select subjectId as 课程,AVG(studentresult) as 课程平均分
from result
where ExamDate='2013-3-22'
group by subjectId
having AVG(studentresult)>=60
--查询参加考试至少一次考试不及格的学生学号、不及格次数
select studentno as 学号,COUNT(studentresult) as 不及格次数
from result
where studentresult<60
group by studentno

--查询学生姓名、所属年级名称及联系电话。
select s.studentname, g.GradeName,s.phone
from [MySchool].[dbo].[Student] s,[MySchool].[dbo].[Grade] g
where s.gradeid= g.gradeid
select s.studentname, g.GradeName,s.phone
from [MySchool].[dbo].[Student] s
inner join [MySchool].[dbo].[Grade] g on s.gradeid= g.gradeid
--查询年级编号为1的科目名称、年级名称及学时。
select s.GradeName, g.SubjectName,g.ClassHour
from [MySchool].[dbo].[Grade] s,[MySchool].[dbo].[Subject] g
where s.gradeid= g.gradeid and s.gradeId=1
select s.GradeName, g.SubjectName,g.ClassHour
from [MySchool].[dbo].[Grade] s
inner join [MySchool].[dbo].[Subject] g on s.gradeid= g.gradeid and s.gradeId=1
--查询参加科目编号为1的考试的学生姓名、分数、考试日期。
select studentname,StudentResult,examDate
from [MySchool].[dbo].[Result] s,[MySchool].[dbo].[Student] g
where s.studentno=g.studentno and SubjectId=1
select studentname,StudentResult,examDate
from [MySchool].[dbo].[Result] s
inner join [MySchool].[dbo].[Student] g on s.studentno=g.studentno and SubjectId=1
--查询学号为S1101007的学生参加的考试科目名称、分数、考试日期。
select Subjectname,StudentResult,examDate
from [MySchool].[dbo].[Subject] s,[MySchool].[dbo].[Result] g
where s.SubjectId=g.SubjectId and g.studentno='S1101007'
select Subjectname,StudentResult,examDate
from [MySchool].[dbo].[Subject] s
inner join [MySchool].[dbo].[Result] g on s.SubjectId=g.SubjectId and g.studentno='S1101007'
--查询参加考试的学生学号、所考科目名称、分数、考试日期.
select studentno,Subjectname,StudentResult,examDate
from [MySchool].[dbo].[Subject] s,[MySchool].[dbo].[Result] g
where s.SubjectId=g.SubjectId 
select studentno,Subjectname,StudentResult,examDate
from [MySchool].[dbo].[Subject] s
inner join [MySchool].[dbo].[Result] g on s.SubjectId=g.SubjectId 
--查询学生学号、姓名、考试科目名称及成绩。
select s.studentno,studentname,subjectname,studentResult
from [MySchool].[dbo].[student] s,[MySchool].[dbo].[Result] g,[MySchool].[dbo].[subject] a
where s.studentno=g.studentno and g.subjectId=a.subjectId
select s.studentno,studentname,subjectname,studentResult
from [MySchool].[dbo].[student] s
inner join [MySchool].[dbo].[Result] g on s.studentno=g.studentno
inner join [MySchool].[dbo].[subject] a on g.subjectId=a.subjectId
--查询参加“走进Java编程世界”考试的学生姓名、成绩、考试日期。
select s.studentname,g.studentResult,g.ExamDate
from [MySchool].[dbo].[student] s,[MySchool].[dbo].[Result] g,[MySchool].[dbo].[subject] a
where s.studentno=g.studentno and g.subjectId=a.subjectId and subjectname='深入.NET平台和C#编程'
select s.studentname,g.studentResult,g.ExamDate
from [MySchool].[dbo].[student] s
inner join [MySchool].[dbo].[Result] g on s.studentno=g.studentno
inner join [MySchool].[dbo].[subject] a on g.subjectId=a.subjectId
where subjectname='深入.NET平台和C#编程'
--查询所有科目的参考信息（某些科目可能还没有被考试过）
select SubjectName,studentNo,studentResult
from [MySchool].[dbo].[Subject] s 
left outer join [MySchool].[dbo].[Result] a on s.SubjectId=a.SubjectId
--查询没有被考过的科目信息。
select SubjectName,studentNo,studentResult
from [MySchool].[dbo].[Subject] s 
left outer join [MySchool].[dbo].[Result] a on s.SubjectId=a.SubjectId
where studentNo is null and studentResult is null
--所有年级对应的学生信息（需要考虑某些年级可能还没有学生就读）
select GradeName,studentNo,studentName
from [MySchool].[dbo].[Grade] s 
left outer join [MySchool].[dbo].[Student] a on s.GradeId=a.GradeId