题目

有一个学生课程数据库，包含学生表Student（Sno, Sname, Ssex, Sbirthday,Smajor）、课程表Course（Cno,Cname,Ccredit,Cpno），学生选课表SC（Sno,Cno,Grade），其中Sno表示学生学号，Sname表示学生姓名，Ssex表示学生性别，Sbirthday表示出生年月,Smajor表示专业；Cno表示课程号,Cname表示课程名称,Ccredit表示课程学分,Cpno表示课程的先修课的课程号；Grade表示成绩；请完成下列SQL查询：1. 找出每个学生低于他自己选修课程平均成绩的课程号？2. 查询选修了全部课程的学生专业？

有一个学生课程数据库，包含学生表Student（Sno, Sname, Ssex, Sbirthday,Smajor）、课程表Course（Cno,Cname,Ccredit,Cpno），学生选课表SC（Sno,Cno,Grade），其中Sno表示学生学号，Sname表示学生姓名，Ssex表示学生性别，Sbirthday表示出生年月,Smajor表示专业；Cno表示课程号,Cname表示课程名称,Ccredit表示课程学分,Cpno表示课程的先修课的课程号；Grade表示成绩；请完成下列SQL查询：

1. 找出每个学生低于他自己选修课程平均成绩的课程号？

2. 查询选修了全部课程的学生专业？

题目解答

答案

  1. 找出每个学生低于平均成绩的课程号： 

    SELECT SC.Sno, SC.Cno
FROM SC
JOIN (
 SELECT Sno, AVG(Grade) AS AvgGrade
 FROM SC
 GROUP BY Sno
) AS AvgGrades ON SC.Sno = AvgGrades.Sno
WHERE SC.Grade < AvgGrades.AvgGrade;

  2. 查询选修了全部课程的学生专业： 

    SELECT S.Smajor
FROM Student AS S
JOIN (
 SELECT Sno
 FROM SC
 GROUP BY Sno
 HAVING COUNT(Cno) = (SELECT COUNT(Cno) FROM Course)
) AS FullCourseStudents ON S.Sno = FullCourseStudents.Sno;

解析

题目题目1：找出每个学生低于他自己选修课程平均成绩的课程号

考察知识：子查询、分组聚合（GROUP BY）、聚合函数（AVG）、表连接（JOIN）。
解题思路：

  1. 计算每个学生的平均成绩：通过子查询SELECT Sno, AVG(Grade) AS AvgGrade FROM SC GROUP BY Sno，按学生学号分组计算每个学生的平均成绩，结果存储为临时表AvgGrades。
  2. 关联原表筛选低分课程：将SC表与AvgGrades表通过Sno连接，筛选出Grade < AvgGrade的记录，得到每个学生低于自己低于自己平均成绩的课程号。

题目2：查询选修了全部课程的学生专业

考察知识：子查询、分组（GROUP BY）、聚合函数（COUNT）、表连接（JOIN）、条件判断（HAVING）。
**解题思路：

  1. 计算选修全部课程的学生学号：子查询SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(Cno)=(SELECT COUNT(Cno) FROM Course)中，内层SELECT COUNT(Cno) FROM Course获取总课程数，外层按学号分组统计每个学生选课数，筛选出选课数等于总课程数的学生学号，存储为FullCourseStudents。
  2. 关联学生表关联获取专业：将Student表与FullCourseStudents表通过Sno连接，提取对应的Smajor（专业）`。