第十一章全部上机代码

上机练习1--查询每个年级的总学时数，并按照升序排列。SELECT SUM(ClassHour) AS 总学时,GradeId AS 年级 FROM Subject GROUP BY  GradeId--查询每个参加考试的学员的平均分。SELECT AVG(StudentResult) AS 平均分,StudentNo AS 学号 FROM Result GROUP BY  StudentNo--查询每门课程的平均分，并按照降序排列。SELECT AVG(StudentResult) AS 平均分,SubjectId AS 课程号 FROM Result GROUP BY SubjectId ORDER BY AVG(StudentResult) DESC--查询每个学生参加的所有考试的总分，并按照降序排列。SELECT SUM(StudentResult) AS 总分,StudentNo AS 学号 FROM Result GROUP BY  StudentNo ORDER BY SUM(StudentResult) DESC 

上机练习2--查询每学期学时数超过50的课程数。SELECT COUNT(*) AS 学时超过50课程数,GradeId AS 年级 FROM Subject WHERE ClassHour>=50 GROUP BY GradeId--查询每学期学生的平均年龄。SELECT AVG(DATEDIFF(dd,BornDate,GETDATE())/365) AS 平均年龄,GradeId AS 年级 FROM Student GROUP BY GradeId--查询北京地区的每学期学生人数。SELECT COUNT(*) AS 北京地区总人数,GradeId AS 年级 FROM StudentWHERE Address LIKE '%北京%'GROUP BY GradeId--查询参加考试的学生中，平均分及格的学生记录，并按照成绩降序排列SELECT AVG(StudentResult) AS 平均分,StudentNo AS 学号 FROM Result GROUP BY StudentNoHAVING AVG(StudentResult)>=60ORDER BY AVG(StudentResult) DESC--查询考试日期2013-3-22的课程的及格平均分。SELECT AVG(StudentResult)AS 及格平均分,SubjectId AS 课程 FROM ResultWHERE StudentResult>=60 AND ExamDate='2013-3-22'GROUP BY SubjectId--查询参加考试至少一次考试不及格的学生学号、不及格次数。SELECT StudentNo AS 学号,COUNT(*) AS 不及格次数 FROM Result WHERE StudentResult<60GROUP BY StudentNo

上机练习3--查询所有科目的参考信息（某些科目可能还没有被考试过）SELECT J.SubjectName AS 科目名称,R.StudentNo AS 学号,R.StudentResult AS 分数FROM Subject AS JLEFT OUTER JOIN Result AS RON J.SubjectId=R.SubjectIdSELECT J.SubjectName AS 科目名称,R.StudentNo AS 学号,R.StudentResult AS 分数FROM Result AS RRIGHT OUTER JOIN Subject AS JON J.SubjectId=R.SubjectId--查询没有被考过的科目信息。SELECT J.SubjectName AS 科目名称FROM Result AS RRIGHT OUTER JOIN Subject AS JON J.SubjectId=R.SubjectIdWHERE R.StudentResult IS NULL AND R.StudentNo IS NULL--所有年级对应的学生信息（需要考虑某些年级可能还没有学生就读）SELECT G.GradeName AS 年级, S.StudentNo AS 学号,S.StudentName AS 姓名FROM Grade AS GLEFT OUTER JOIN Student AS SON G.GradeId=S.GradeIdSELECT G.GradeName AS 年级, S.StudentNo AS 学号,S.StudentName AS 姓名FROM Student AS SRIGHT OUTER JOIN Grade AS GON G.GradeId=S.GradeId

上机练习4--查询学生姓名、所属年级名称及联系电话。SELECT S.StudentName AS 姓名,G.GradeName AS 年级,S.Phone AS 电话FROM Student AS SINNER JOIN Grade AS G ON S.GradeId=G.GradeIdSELECT S.StudentName AS 姓名,G.GradeName AS 年级,S.Phone AS 电话FROM Student AS S,Grade AS G WHERE S.GradeId=G.GradeId--查询年级编号为1的科目名称、年级名称及学时。SELECT J.SubjectName AS 科目名称,G.GradeName AS 年级,J.ClassHour AS 学时FROM Subject AS JINNER JOIN Grade AS G ON J.GradeId=G.GradeIdWHERE G.GradeId=1SELECT J.SubjectName AS 科目名称,G.GradeName AS 年级,J.ClassHour AS 学时FROM Subject AS J,Grade AS GWHERE J.GradeId=G.GradeId AND G.GradeId=1--查询参加科目编号为1的考试的学生姓名、分数、考试日期。SELECT S.StudentName AS 姓名,R.StudentResult AS 分数,R.ExamDate AS 考试日期FROM Result AS RINNER JOIN Student AS S ON R.StudentNo=S. StudentNoWHERE R.SubjectId=1SELECT S.StudentName AS 姓名,R.StudentResult AS 分数,R.ExamDate AS 考试日期FROM Result AS R,Student AS S WHERE R.StudentNo=S. StudentNo AND R.SubjectId=1--查询学号为S1101007的学生参加的考试科目名称、分数、考试日期。SELECT J.SubjectName AS 科目名称,R.StudentResult AS 分数,R.ExamDate AS 考试日期FROM Result AS RINNER JOIN Subject AS J ON R.SubjectId=J.SubjectIdWHERE R.StudentNo='S1101007'SELECT J.SubjectName AS 科目名称,R.StudentResult AS 分数,R.ExamDate AS 考试日期FROM Result AS R ,Subject AS JWHERE R.SubjectId=J.SubjectId AND R.StudentNo='S1101007'--查询参加考试的学生学号、所考科目名称、分数、考试日期.SELECT R.StudentNo AS 学生学号,J.SubjectName AS 科目名称,R.StudentResult AS 分数,R.ExamDate AS 考试日期FROM Result AS RINNER JOIN Subject AS J ON R.SubjectId=J.SubjectIdSELECT R.StudentNo AS 学生学号,J.SubjectName AS 科目名称,R.StudentResult AS 分数,R.ExamDate AS 考试日期FROM Result AS R,Subject AS J WHERE R.SubjectId=J.SubjectId 

上机练习5--查询学生学号、姓名、考试科目名称及成绩。SELECT S.StudentNo AS 学号,S.StudentName AS 姓名,J.SubjectName AS 科目名称,R.StudentResult AS 成绩FROM Result AS RINNER 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 JWHERE(R.StudentNo=S.StudentNo) AND (R.SubjectId=J.SubjectId)--查询参加“走进Java编程世界”考试的学生姓名、成绩、考试日期。SELECT S.StudentName AS 姓名,R.StudentResult AS 成绩,R.ExamDate AS 考试日期FROM Result AS RINNER 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 JWHERE(R.StudentNo=S.StudentNo) AND (R.SubjectId=J.SubjectId)AND J.SubjectName='走进Java编程世界'