實作

資料庫結構如下 :

學生資料表(學生編號, 身分證字號, 學生姓名, 科系編號, 年級, 班級)
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。