SOL第十一章上机题1-5

--使用分组查询学生相关信息
SELECT SUM(ClassHour)AS 总课时 FROM [Subject] GROUP BY GradeId ORDER BY SUM(ClassHour) 
SELECT StudentNo AS 学号,AVG(StudentResult) AS 学员平均分 FROM Result GROUP BY StudentNo
SELECT SubjectId AS 科目,AVG(StudentResult) AS 课程平均分 FROM Result GROUP BY SubjectId
SELECT StudentNo AS 学号,SUM(StudentResult) AS 学员平均分 FROM Result GROUP BY StudentNo ORDER BY SUM(StudentResult) DESC



--限定条件的分组查询
SELECT GradeId AS 学期,COUNT(SubjectId) AS 课时数 FROM [Subject] WHERE ClassHour>50 GROUP BY GradeId  
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 AS 学号,AVG(StudentResult) AS 学员平均分 FROM Result GROUP BY StudentNo HAVING AVG(StudentResult)>60 ORDER BY AVG(StudentResult) DESC
SELECT StudentNo AS 学号,AVG(StudentResult) AS 学员平均分 FROM Result WHERE ExamDate='2013-3-22' GROUP BY StudentNo HAVING AVG(StudentResult)>60 
SELECT StudentNo AS 学号,COUNT(StudentResult) AS 不及格次数 FROM Result WHERE StudentResult<60 GROUP BY StudentNo 

--两表内连接查询信息
SELECT S.StudentName AS 学生姓名, G.GradeName AS 年级名称,S.Phone AS 联系电话
FROM Student AS S ,Grade AS G
WHERE S.GradeId=G.GradeId

SELECT G.GradeName AS 年级名称, J.SubjectName AS 科目名称, J.ClassHour AS 学时
FROM Subject AS J
INNER JOIN Grade AS G ON J.GradeId=G.GradeId
WHERE G.GradeId=1

SELECT S.StudentName AS 学生姓名, R.StudentResult AS 分数, R.ExamDate AS 考试日期
FROM Student AS S
INNER JOIN Result AS R ON	S.StudentNo=R.StudentNo
WHERE R.SubjectId=1

SELECT J.SubjectName AS 科目名称, R.StudentResult AS 分数, R.ExamDate AS 考试日期
FROM Subject AS J
INNER JOIN Result AS R ON	J.SubjectId=R.SubjectId
WHERE R.StudentNo='S1101007'

SELECT J.SubjectName AS 科目名称, R.StudentResult AS 分数, R.ExamDate AS 考试日期
FROM Subject AS J
INNER JOIN Result AS R ON	J.SubjectId=R.SubjectId

--三表内连接查询信息
SELECT S.StudentNo AS 学号,S.StudentName AS 学生姓名,J.SubjectName AS 科目名称, R.StudentResult AS 成绩
FROM Student AS S
INNER JOIN Result AS R ON (S.StudentNo=R.StudentNo)
INNER JOIN [Subject] AS J ON (R.SubjectId=J.SubjectId)

SELECT S.StudentName AS 学生姓名, R.StudentResult AS 成绩,R.ExamDate AS 考试日期
FROM Result AS R
INNER JOIN Student AS S ON (S.StudentNo=R.StudentNo)
WHERE SubjectId=2


--使用外连接查询信息
SELECT J.SubjectName AS 科目名称,R.StudentNo AS 学号, R.StudentResult AS 成绩
FROM Result AS R
RIGHT OUTER JOIN [Subject] AS J ON R.SubjectId=J.SubjectId

SELECT J.SubjectName AS 科目名称,R.StudentNo AS 学号, R.StudentResult AS 成绩
FROM Result AS R
RIGHT OUTER JOIN [Subject] AS J ON R.SubjectId=J.SubjectId
WHERE R.StudentResult IS NULL AND R.ExamDate IS NULL


SELECT G.GradeName AS 年级名称,S.StudentNo AS 学号,S.StudentName AS 学生姓名
FROM Student AS S
RIGHT OUTER JOIN Grade AS G ON G.GradeId=S.GradeId