【数据挖掘】国科大苏桂平老师数据库新技术课程作业 —— 第三次作业

part 1

设计一个学籍管理小系统。系统包含以下信息：

学号、学生姓名、性别、出生日、学生所在系名、学生所在系号、课程名、课程号、课程类型（必修、选修、任选）、学分、任课教师姓名、教师编号、教师职称、教师所属系名、系号、学生所选课程号、学生所选上课教师姓名、选课成绩。

两种不同表达方式的 E-R 图分别如图 $1$ 和图 $2$ 所示。

图 1    E-R 图（未注明了键、外键、类型等信息）

图 2    E-R 图（注明了键、外键、类型等信息）

part 2

完成在表上的操作 (用 SQL 的 SELECT 语句)：

根据描述内容创建 student 表、course 表、department 表、selectcourse 表、teacher 表、teach 表，分别如图 $1$、$2$、$3$、$4$、$5$、$6$ 所示。

表 1    student 关系表

表 2    course 关系表

表 3    department 关系表

表 4    selectcourse 关系表

表 5    teacher 关系表

表 6    teach 关系表

1. 查询所有学生所选的所有课程名、成绩

查询语句如下：

SELECT C.学生姓名, B.课程名, A.选课成绩 
FROM selectcourse AS A, course AS B, student AS C 
WHERE A.课程号=B.课程号 AND A.学号=C.学号

查询结果如表 $7$ 所示：

表 7    所有学生所选的所有课程名、成绩查询结果

2. 查询 somestucent 表中学生所选的所有课程名、成绩

somestudent 表给出学生名：

表 8    somestudent 关系表

查询语句如下：

SELECT D.学生姓名, B.课程名, A.选课成绩 
FROM selectcourse AS A, course AS B, student AS C, somestudent AS D 
WHERE A.课程号=B.课程号 AND C.学生姓名=D.学生姓名 AND A.学号=C.学号

查询结果如表 $9$ 所示：

表 9    somestucent 表中学生所选的所有课程名、成绩查询结果

3. 查询 somestudent 表中学生的累计学分（未及格的不算）

查询语句如下：

SELECT D.学生姓名, SUM(B.学分) AS '累计学分' 
FROM selectcourse AS A, course AS B, student AS C, somestudent AS D 
WHERE A.课程号=B.课程号 AND C.学生姓名=D.学生姓名 AND A.学号=C.学号 AND A.选课成绩 >= 60 
GROUP BY C.学号

查询结果如表 $10$ 所示：

表 10    somestudent 表中学生的累计学分查询结果

4. 查询 somecourse 表中课程的平均成绩

somecourse 表给出课程名：

表 11    somescourse 关系表

查询语句如下：

SELECT C.课程名, AVG(A.选课成绩) AS '课程平均成绩' 
FROM selectcourse AS A, course AS B, somecourse AS C
WHERE A.课程号=B.课程号 AND B.课程名=C.课程名
GROUP BY C.课程名

查询结果如表 $12$ 所示：

表 12    somecourse 表中课程的平均成绩查询结果

5. 各门课按平均成绩排序（降序，若改为升序，只需要将 DESC 换为 ASC）

查询语句如下：

SELECT B.课程名, AVG(A.选课成绩) AS '平均成绩' 
FROM selectcourse AS A, course AS B
WHERE A.课程号=B.课程号
GROUP BY A.课程号
ORDER BY 平均成绩 DESC

查询结果如表 $13$ 所示：

表 13    各门课按平均成绩排序查询结果

6. 显示所有的课程名和讲课教师名

查询语句如下：

SELECT B.课程名, C.教师姓名
FROM teach AS A, course AS B, teacher AS C
WHERE A.课程号=B.课程号 AND A.教师编号=C.教师编号

查询结果如表 $14$ 所示：

表 14    所有的课程名和讲课教师名查询结果