實作測驗 ~ 正規化 + SELECT

有如下表單,你應該如何進行正規化呢?

學號 學生姓名 學生電話 課程代號 課程名稱 年級 老師姓名/電話 老師代號 科系代號 科系名稱 成績
101001 張大頭 0910111222
0922111000
A001 資料庫 三年級 王老師 0933111333 001 001 資管系 80
A002 網路概論 林老師 0955111222 002 003 資工系 50
101002 劉三哥 0912333222
0922111333
A003 程式設計 三年級 孫老師 0912000333 003 001 資管系 85
A002 網路概論 林老師 0955111222 002 003 資工系 76


我們正規化後,資料結構如下~

studnt 學生資料表 (studno, studname, deptid)
teacher 老師資料表 (teano, teaname, deptid)
department 科系資料表 (deptid, deptname)
telephone 電話資料表 (ownerno, tel)
coursemain 課程資料表 (courseid, coursename, coursecredit, courseflag, deptid)
courseopen 開課資料表 (yearsemester, courseid, teano, coursetime, roomid)
course 學生選課單 (yearsemester, courseid, studno, score)



正規化後,請寫出以下SQL Command:

(1)列出學生張大頭的所有選修的課程代號

SELECT c.courseid FROM course c, student s WHERE c.studno=s.studno AND s.studname='張大頭';

(2)列出學生張大頭的所有選修的課程名稱

SELECT m.coursename FROM course c, student s, coursemain m WHERE c.courseid=m.courseid AND c.studno=s.studno AND s.studname='張大頭';

(3)列出學生張大頭的所有選修的課程名稱,以及成績

SELECT m.coursename, c.score FROM course c, student s, coursemain m WHERE c.courseid=m.courseid AND c.studno=s.studno AND s.studname='張大頭';

(4)列出學生張大頭的所有電話

SELECT t.tel FROM telephone t, student s WHERE t.ownerno=s.studno AND s.studname='張大頭';

(5)列出林老師所有開課的總學生人數

SELECT COUNT(*) FROM course c, courseopen co, teacher t WHERE c.courseid=co.courseid AND co.teano=t.teano AND t.teaname='林老師';

(6)列出選修程式設計的學生名單

SELECT s.studno, s.studname FROM student s, course c, coursemain cm WHERE s.studno=c.studno AND c.courseid=cm.courseid AND cm.coursename='程式設計';

(7)列出每位學生的平均分數

SELECT studno, AVG(score) FROM course GROUP BY studno;

(8)列出所有課程成績未達60的學生姓名

SELECT s.studname FROM course c, student s WHERE c.studno=s.studno AND c.score<60;

(9)列出資工系的所有開課課程名稱

SELECT cm.coursename FROM coursemain cm, courseopen co, department d WHERE cm.courseid=co.courseid AND cm.deptid=d.deptid AND d.deptname='資工系';

(10)列出電話、所有人姓名。

 (SELECT studname as name, tel FROM telephone t,student s where t.ownerno=s.studno) UNION  (SELECT teaname as name, tel FROM telephone t,teacher tea where t.ownerno=tea.teano);

(11)列出沒有開課的老師編號、姓名。


SELECT teano,teaname FROM teacher WHERE teano NOT IN (SELECT teano FROM courseopen);


(12)列出每個學生的修課學分總數。

SELECT c.studno,sum(coursecredit) FROM course c,coursemain cm WHERE c.courseid=cm.courseid GROUP BY c.studno;

留言

這個網誌中的熱門文章

如何使用EXCEL連接MYSQL

關聯模式的五大鍵 Super key、Candidate Key、Primary Key、Alternate Key、Foreign Key

SELECT SQL語法總整理