11章上机

使用分组查询学生相关信息.sql

--查询每个年级的总学时数，并按照升序排列。
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

 

限定条件的分组查询.sql
--查询学生姓名、所属年级名称及联系电话。
SELECT S.StudentName AS 姓名,G.GradeName AS 年级,S.Phone AS 电话
FROM Student AS S
INNER JOIN Grade AS G ON S.GradeId=G.GradeId

SELECT 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 J
INNER JOIN Grade AS G ON J.GradeId=G.GradeId
WHERE G.GradeId=1

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

--查询参加科目编号为1的考试的学生姓名、分数、考试日期。
SELECT S.StudentName AS 姓名,R.StudentResult AS 分数,R.ExamDate AS 考试日期
FROM Result AS R
INNER JOIN Student AS S ON R.StudentNo=S. StudentNo
WHERE R.SubjectId=1

SELECT 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 R
INNER JOIN Subject AS J ON R.SubjectId=J.SubjectId
WHERE R.StudentNo='S1101007'

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

--查询参加考试的学生学号、所考科目名称、分数、考试日期.

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


SELECT R.StudentNo AS 学生学号,J.SubjectName AS 科目名称,R.StudentResult AS 分数,R.ExamDate AS 考试日期
FROM Result AS R,Subject AS J 
WHERE R.SubjectId=J.SubjectId 




两表内联接查询信息.sql
--查询学生学号、姓名、考试科目名称及成绩。
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编程世界' 





三表内联接查询信息.sql
--查询所有科目的参考信息（某些科目可能还没有被考试过）
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
--查询每学期学时数超过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 Student
WHERE Address LIKE '%北京%'
GROUP BY GradeId

--查询参加考试的学生中，平均分及格的学生记录，并按照成绩降序排列
SELECT AVG(StudentResult) AS 平均分,StudentNo AS 学号 FROM Result 
GROUP BY StudentNo
HAVING AVG(StudentResult)>=60
ORDER BY AVG(StudentResult) DESC

--查询考试日期2013-3-22的课程的及格平均分。
SELECT AVG(StudentResult)AS 及格平均分,SubjectId AS 课程 FROM Result
WHERE StudentResult>=60 AND ExamDate='2013-3-22'
GROUP BY SubjectId

--查询参加考试至少一次考试不及格的学生学号、不及格次数。
SELECT StudentNo AS 学号,COUNT(*) AS 不及格次数 FROM Result 
WHERE StudentResult<60
GROUP BY StudentNo