實作


資料庫結構如下 :

學生資料表(學生編號, 身分證字號, 學生姓名, 科系編號, 年級, 班級)
Student(sid, idno, sname, did, syear, sclass)

老師資料表(老師編號, 身分證字號, 老師姓名, 科系編號, 老師類別)
Teacher(tid, idno, tname, did, ttype)

課程資料表(課程編號, 課程名稱, 科系代號, 學分數, 選修別)
Course(cid, cname, did, ccredit, ctype)

開課資料表(開課編號, 課程編號, 老師編號, 修課年級限制, 教室編號)
OpenCourse(oid, cid, tid, yearlimit, roomno)

時間資料表(開課編號,  開課時間編號)
Schedule(oid, timeno)

選課資料表(學生編號,  開課編號, 成績)
Roll(sid, oid, score)

科系資料表(科系編號, 科系名稱)
Dept(did, dname)

資料內容如下 :




























問題 :
(1)請問如何找到學生s0001沒有衝堂的課程編號與課程名稱?

我們先想~學生s0001修了那些課程? 

SELECT * FROM roll WHERE sid='s0001';












修了這些課程,是那些時間上課呢? 



SELECT timeno FROM roll r,schedule s WHERE sid='s0001' AND r.oid=s.oid;















我們要學生s0001沒有衝堂的課程編號與課程名稱,就是要找課程,他的時間不能跟這個衝突的。

所以第一個想法,就是找到課程其時間不在上面裡面。

SELECT o.oid FROM opencourse o, schedule s WHERE o.oid=s.oid AND s.timeno NOT IN (SELECT timeno FROM roll r,schedule s WHERE sid='s0001' AND r.oid=s.oid);

















但是,你會發現上面想法有個問題,課程o0006的上課時間是12,13,14,雖14沒有衝堂,可是12,13是衝堂的,因此這門課程部分衝堂,還是不能修。

所以我們~找到課程其時間不在原本修課時間,要做個修正。

我們必須先這麼做 :

SELECT o.oid FROM opencourse o, schedule s WHERE o.oid=s.oid AND s.timeno IN (SELECT timeno FROM roll r,schedule s WHERE sid='s0001' AND r.oid=s.oid);

得到如下資料














然後再取得,不是以上課程的~課程編號與課程名稱。

SELECT c.cid, c.cname FROM course c, opencourse o WHERE c.cid=o.cid AND o.oid NOT IN (SELECT o.oid FROM opencourse o, schedule s WHERE o.oid=s.oid AND s.timeno IN (SELECT timeno FROM roll r,schedule s WHERE sid='s0001' AND r.oid=s.oid));

得到以下資料












(2)請問如何找到學生s0001沒有衝堂的課程編號與老師姓名?


跟上題類似

SELECT o.cid, t.tname FROM opencourse o,teacher t WHERE o.tid=t.tid AND o.oid NOT IN (SELECT o.oid FROM opencourse o, schedule s WHERE o.oid=s.oid AND s.timeno IN (SELECT timeno FROM roll r,schedule s WHERE sid='s0001' AND r.oid=s.oid));


得到以下資料
















(3)這下面SQL是在做什麼?


select * from opencourse o,schedule s 
where o.oid=s.oid and o.oid='o0006' and concat(roomno,timeno)  in (
select concat(roomno,timeno) from opencourse o,schedule s 
where o.oid=s.oid and o.oid<>'o0006'); 

先看這段

select concat(roomno,timeno) from opencourse o,schedule s 
where o.oid=s.oid and o.oid<>'o0006';






















取出課程不是o0006的上課教室+時間

假設這個 S1=select concat(roomno,timeno) from opencourse o,schedule s 
where o.oid=s.oid and o.oid<>'o0006';

以下這個的意思是~找到與課程o0006衝堂的資料
select * from opencourse o,schedule s 
where o.oid=s.oid and o.oid='o0006' and concat(roomno,timeno)  in (S1);












我們來看o0006的上課教室是~r0001,上課時間是12,13,14。o0001的上課教室是~r0001,上課時間是11,12,13。

也就是跟課程o0006的上課教室有兩個時間是衝堂的~12,13


留言

這個網誌中的熱門文章

如何使用EXCEL連接MYSQL

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

SELECT SQL語法總整理