11章 上机练习 1----5

--查询每个年级的总学时数
SELECT SubjectId,sum(ClassHour) AS 总学时
FROM Subject
group by SubjectId
ORDER BY sum(ClassHour)asc

--查询每个参加考试的学员平均分.
SELECT StudentNo,AVG(StudentResult)
FROM Result
GROUP BY StudentNo

--查询每门课程的平均分
select SubjectName,AVG(ClassHour)AS 平均分
FROM [MySchool].[dbo].[Subject]
GROUP BY SubjectName
ORDER BY sum(ClassHour)DESC

--每个学生参加的所有考试总分
select StudentNo,sum(StudentResult)as 总分
from Result
group by StudentNo
order by sum(StudentResult)desc

--上级二
--学时超过50的课程
select GradeId,sum(ClassHour)as 学时,count(SubjectName)as 课程
from Subject
group by GradeId
having sum(ClassHour)>50

--每学期学生的平均年龄
select GradeId as 年级,avg(DATEDIFF(dd,BornDate,getdate()))/365 as 平均年龄
from Student
group by GradeId

--北京每学期学生人数
select GradeId as 学期,COUNT(StudentNo)as 人数
from Student
where Address like'%北京%'
group by GradeId

--平均分及格的学生记录
select StudentNo,avg(StudentResult)as 平均分
from Result
group by StudentNo
having avg(StudentResult)>=60
order by avg(StudentResult)desc

--考试日期为2013-3-22日课程的及格平均分
select StudentNo,AVG(StudentResult)as 平均分
from Result
where StudentResult>60 and ExamDate='2013-3-22'
group by StudentNo

--考试不及格的学生学号.不及格次数
select StudentNo as 学号,COUNT(*)as 次数
from Result
where StudentResult<60
group by StudentNo


--上机3
--学生姓名,所属年级,联系电话
select StudentName as 学生姓名,GradeName as 所属年级,Phone as 联系电话
from Student,Grade
where  Student.GradeId=Grade.GradeId

--年级编号为一的年级名称,科目名称,学时
select Student.GradeId,SubjectName,ClassHour
from Student,Subject
where Student.GradeId=Subject.GradeId and Subject.GradeId=1

--参加科目编号为一的考试学生姓名,分数,考试日期
select Student.StudentName as 姓名,StudentResult as 分数,ExamDate as 日期
from Student,Result
where Student.StudentNo=Result.StudentNo and Id=1

--学号为s1101007的学生参加的考试科目名称,分数,考试日期
select Subject.SubjectName as 考试名称,StudentResult as 分数,ExamDate as 日期
from Subject,Result
where Subject.SubjectId=Result.SubjectId and Result.StudentNo='S1101007'

--考试的学生学号,所考科目的名称,分数,考试日期
select Result.StudentNo as 考号,SubjectName as 名称,StudentResult as 分数,ExamDate as 日期
from Result,Subject
where Result.SubjectId=Subject.SubjectId 

--上机四
--查询学号,姓名,考试科目,成绩
select Result.StudentNo as 学号,Student.StudentName as 姓名,
Subject.SubjectName as 科目,Result.StudentResult as 成绩
from Result
inner join Subject on Result.SubjectId=Subject.SubjectId
inner join Student on Result.StudentNo=Student.StudentNo

--走进JAVA世界 考试学生姓名,成绩,考试日期
select Student.StudentName as 姓名,Result.StudentResult as 成绩,Result.ExamDate as 考试日期
from Student
inner join Result on Result.StudentNo=Student.StudentNo
inner join Subject on Result.SubjectId=Subject.SubjectId and
StudentName='走进JAVA世界'

--上机5
-- 查询所有科目的考试信息
select Subject.SubjectName as 科目名称,StudentNo as 学号,Result.StudentResult as 分数
from Subject
left outer join Result on Result.SubjectId=Subject.SubjectId

--查询从未考试的科目信息
select Subject.SubjectName as 名称,Result.StudentNo as 学号,StudentResult as 分数
from Result
right outer join  Subject on Result.SubjectId=Subject.SubjectId
where StudentResult is null

--所有年级对应的学生信息
select Grade.GradeName,StudentNo,StudentName
from Student
right outer join Grade on Grade.GradeId=Student.GradeId