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$ 所示。

學號	學生姓名	性別	出生日	所在系號
0	張三	男	19870610	0
1	李四	男	19870610	1
2	王五	女	19870610	2
3	趙六	男	19870610	3
4	李德	男	19870610	4
5	王和	男	19870610	5
6	田亮	男	19870610	6
7	周潤發	男	19870610	7
8	劉德華	男	19870610	8
9	謝霆鋒	男	19870610	9
10	鄧超	男	19870610	0
11	劉翔	男	19860214	1
12	姚明	男	19860214	2
13	高峰	男	19860214	3
14	史冬鵬	男	19860214	4
15	郭晶晶	女	19860214	5
16	湯唯	女	19860214	6
17	范冰冰	女	19860214	7
18	李冰冰	女	19860214	8
19	趙本山	男	19860214	9
20	郭德綱	男	19860214	0
21	于謙	男	19860214	1
22	岳云鵬	男	19870814	2
23	姜昆	男	19870905	3
24	李金斗	男	19870905	4
25	劉青云	男	19870905	5
26	張靚穎	女	19870905	6
27	韓寒	男	19870905	7
28	李宇春	女	19870905	8
29	張靜初	女	19870905	9
30	莫文蔚	女	19870905	9

表 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????所有的課程名和講課教師名查詢結果