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编
十一章 上机练习4 + 5
最新推荐文章于 2022-04-23 10:37:36 发布