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.GradeId
sql上机练习5
最新推荐文章于 2022-03-29 18:58:54 发布