十一章 上机练习4 + 5

USE MySchool

--查询所有科目的参考信息（某些科目可能还没有被考试过）
SELECT J.SubjectName AS 科目名称,R.StudentNo AS 学号,R.StudentResult AS 分数
FROM Subject AS J
LEFT OUTER JOIN Result AS R
ON J.SubjectId=R.SubjectId

SELECT J.SubjectName AS 科目名称,R.StudentNo AS 学号,R.StudentResult AS 分数
FROM Result AS R
RIGHT OUTER JOIN Subject AS J
ON J.SubjectId=R.SubjectId

--查询没有被考过的科目信息。

SELECT J.SubjectName AS 科目名称
FROM Result AS R
RIGHT OUTER JOIN Subject AS J
ON J.SubjectId=R.SubjectId
WHERE R.StudentResult IS NULL AND R.StudentNo IS NULL

--所有年级对应的学生信息（需要考虑某些年级可能还没有学生就读）
SELECT G.GradeName AS 年级, S.StudentNo AS 学号,S.StudentName AS 姓名
FROM Grade AS G
LEFT OUTER JOIN Student AS S
ON G.GradeId=S.GradeId

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


--查询学生学号、姓名、考试科目名称及成绩。
SELECT S.StudentNo AS 学号,S.StudentName AS 姓名,J.SubjectName AS 科目名称,R.StudentResult AS 成绩
FROM Result AS R
INNER JOIN Student AS S ON(R.StudentNo=S.StudentNo)
INNER JOIN Subject AS J ON(R.SubjectId=J.SubjectId)

SELECT S.StudentNo AS 学号,S.StudentName AS 姓名,J.SubjectName AS 科目名称,R.StudentResult AS 成绩
FROM Result AS R,Student AS S,Subject AS J
WHERE(R.StudentNo=S.StudentNo) AND (R.SubjectId=J.SubjectId)

--查询参加“走进Java编程世界”考试的学生姓名、成绩、考试日期。
SELECT S.StudentName AS 姓名,R.StudentResult AS 成绩,R.ExamDate AS 考试日期
FROM Result AS R
INNER JOIN Student AS S ON (R.StudentNo=S.StudentNo)
INNER JOIN Subject AS J ON(R.SubjectId=J.SubjectId)
WHERE J.SubjectName='走进Java编程世界' 

SELECT S.StudentName AS 姓名,R.StudentResult AS 成绩,R.ExamDate AS 考试日期
FROM Result AS R, Student AS S ,Subject AS J
WHERE(R.StudentNo=S.StudentNo) AND (R.SubjectId=J.SubjectId)AND J.SubjectName='走进Java编