# 基本的SQL
:::danger
:whale: These exercises are from the book named **```資料庫的核心理論與實務```**
'#' -> means i made a correction from the answer write by myself or maybe there's questions haven't resolved yet...
:::
:::warning
### 習題 6-1 (18.)
:whale: 請將以下的資料庫綱目用SQL的CREATE TABLE敘述表達出來，欄位的型態請自行假設，請記得設定主鍵和外部鍵。
18. [習題4-24]的校友資料庫系統
:::
```sql=
Create Table Alumni
( aId VARCHAR(10) NOT NULL,
aName VARCHAR(10),
gender VARCHAR(8),
pId VARCHAR(10) # NOT NULL,
Address VARCHAR(20),
Email VARCHAR(20),
Company VARCHAR(10),
Position VARCHAR(20),
PRIMARY KEY (aId)
# UNIQUE (pId) );
```
```sql=
Create Table Department
( dId VARCHAR(10) NOT NULL,
dName VARCHAR(10),
startYear DATE,
PRIMARY KEY (dId)
# if set dName in NOT NULL, maybe can consider it as a UNIQUE KEY and underline it in ERD. );
```
```sql=
Create Table JobType
( jId VARCHAR(10) NOT NULL,
jName VARCHAR(10),
jDesc VARCHAR(10),
PRIMARY KEY (jId) );
```
```sql=
Create Table tel
( aId VARCHAR(10) NOT NULL,
use VARCHAR(10) NOT NULL, # can change the name 'use' and 'number' because of reserved word issue.
number VARCHAR(10) NOT NULL,
PRIMARY KEY (aId,use,number),
FOREIGH KEY (aId) REFERENCES Alumni(aId)
ON DELETE CASCADE ON UPDATE CASCADE );
```
```sql=
Create Table Class
( dId VARCHAR(10) NOT NULL,
cYear Year NOT NULL, # you can also set INT if just store a year(answer from stackoverflow)
dSession VARCHAR(10) NOT NULL,
cProgram VARCHAR(10) NOT NULL,
cDesc VARCHAR(10),
aId VARCHAR(10),
PRIMARY KEY (dId, cYear, dSession, cProgram),
FOREIGN KEY (dId) REFERENCES Department(dId)
ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY (aId) REFERENCES Alumni(aId)
ON DELETE RESTRICT ON UPDATE CASCADE );
# 'ON DELETE RESTRICT': 代表外部鍵參考的值要被刪除時, 如果該值有被外部鍵的值參考, 則無法刪除。
```
```sql=
Create Table Graduates
( dId VARCHAR(10) NOT NULL,
cYear Year NOT NULL,
cSession VARCHAR(10) NOT NULL,
cProgram VARCHAR(10) NOT NULL,
aId VARCHAR(10) NOT NULL,
# 主鍵5個全包還是aId隔出來當UNIQUE?
PRIMARY KEY (dId, cYear, dSession, cProgram),
UNIQUE (aId),
FOREIGN KEY (dId, cYear, dSession, cProgram) REFERENCES Class(dId, cYear, dSession, cProgram)
ON DELETE RESTRICT ON UPDATE CASCADE, # CASCADE -> RESTRICT
FOREIGN KEY (aId) REFERENCES Alumni(aId)
ON DELETE CASCADE ON UPDATE CASCADE, # ON DELETE CASCADE
);
#問:參考來自兩個表的 兩個REFERENCES到底怎麼決定是否CASCADE?
```
```sql=
Create Table Has
( jId VARCHAR(10) NOT NULL,
aId VARCHAR(10) NOT NULL,
PRIMARY KEY (jId,aId)
FOREIGN KEY (jId) REFERENCES JobType(jId)
ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY (aId) REFERENCES Alumni(aId)
ON DELETE CASCADE ON UPDATE CASCADE );
# which one goes RESTRICT or CASCADE at DELETE condition decided by the story setting.
```
:::warning
### 習題 6-4
:whale: 參考圖6-1的資料庫綱目，請用SQL表達以下查詢
1. 列出買過「蔡依林專輯二」的會員姓名。
2. 列出瀏覽過「蔡依林專輯二」的會員姓名。
3. 列出2004年網路交易(即交易方式是'cart')的交易編號和會員編號。
4. 列出總售價(salePrice)超過400元的交易商品之交易編號、商品名稱、商品訂價、交易數量和售價。
:::
```sql=
#1.
SELECT M.name
FROM Member AS M, Product AS P, Transaction AS T, Record AS R
WHERE P.pName = ‘蔡依林專輯二’
AND M.mid = T.transMid, T.tNo = R.tNo, R.pNo = P.pNo
```
```sql=
#2.
SELECT (DISTINCT) M.name
FROM Member AS M, Product AS P, Browse AS B
WHERE P.pName = ‘蔡依林專輯二’
AND M.mId = B.mId, B.pNo = P.pNo
```
```sql=
#3.
SELECT T.tNo, T.transMid
FROM Transaction AS T
WHERE T.method = ‘cart’
AND T.transTime = to_Date(‘2004’,’yyyy’) ; # AND to_char(T.transTime,’yyyy’) = ‘2004’ ;
```
```sql=
#4.
SELECT R.tNo, P.pName, P.uniPrice, R.amount, R.salePrice
FROM Product AS P, Record AS R
WHERE R.salePrice > ‘400’
AND R.pNo = P.pNo ;
```
:::warning
### 習題 6-5
:whale: 參考圖6-1的資料庫綱目
1. 假設我們想新增一本書、一位會員和一筆瀏覽紀錄，所以產生以下紀錄:
(1)(‘a00001’ , ‘2003-04-05:09:00:08’ , ‘b10000’)
(2)(‘b10000’ , ‘陳水扁’)
(3)(‘b10000’ , ‘總統之路’ , 300 , ‘Book’)
(4)(‘a00001’ , ’c100000000’ , ‘Mary’ , null , null , null , null , null)
請用INSERT INTO敘述新增以上紀錄，並注意新增的次序以免違反參考完整限制。
2. 請用UPDATE敘述將Member資料表中編號為'a0910001'的會員姓名改為「Jenny Pai」。
3. 請用DELETE敘述將第1小題所新增之瀏覽紀錄刪除
:::
```sql=
#1. (1)
INSERT INTO Browse
VALUES (‘a00001’ , ‘2003-04-05:09:00:08’ , ‘b10000’)
(2)
INSERT INTO Author
VALUES (‘b10000’ , ‘陳水扁’)
(3)
INSERT INTO Product
VALUES (‘b10000’ , ‘總統之路’ , 300 , ‘Book’)
(4)
INSERT INTO Member
VALUES (‘a00001’ , ’c100000000’ , ‘Mary’ , null , null , null , null , null)
```
```sql=
#2.
UPDATE Member
SET name = ‘Jenny Pai’
WHERE mId = ‘a0910001’ ;
```
```sql=
#3.
DELETE Browse
WHERE mId = ‘a00001’ ;
```
:::warning
### 習題 6-6
:whale: 考慮[習題4-8]遠距教學互動系統的資料庫綱目，請用SQL查詢句列出修「資料庫管理」的所有學生之學號和姓名。
:::
```sql=
SELECT S.sId, S.name
FROM Student AS S, Takes AS T, Course AS C
WHERE C.coursed = ‘資料庫管理’
AND S.sId = T.sId, T.courseId = C.courseId ;
```
:::warning
### 習題 6-9
:whale: 考慮[習題4-12]錄影帶租借系統的資料庫綱目，請用SQL查詢句列出「李安」所導演的所有影片名稱和其代理商名稱。
:::
```sql=
SELECT V.title, A.aName
FROM Video AS V, Agency AS A
WHERE V.uCode = A.uCode
AND V.director = ‘李安’ ;
```
:::warning
### 習題 6-11
:whale: 考慮[習題4-14]拍賣網站系統的資料庫綱目，請用SQL查詢句列出商品名稱包括「聲寶電漿電視」之所有可拍賣(也就是欄位expired值在今天以後)的商品之名稱、拍賣者姓名、底價和拍賣截止日期時間。
:::
```sql=
SELECT Merchandise.name, Member.mId, bottomPrice, expired
FROM Merchandise, Member
WHERE Merchandise.mId = Member.mId
AND name LIKE ‘%聲寶電漿電視%’
AND to_char(expired, ‘yyyymmdd’) > ’20220406’ ;
```
:::warning
### 習題 6-13
:whale: 考慮[習題4-19]新書推薦系統的資料庫綱目，請用SQL查詢句列出「陳水扁」在2002-11-1以後所設的所有興趣條件
:::
```sql=
SELECT constraint
FROM Interest, Patron
WHERE name = ‘陳水扁’
AND to_char(date, ‘yyyymmdd’) > ‘20021101’
AND Patron.patronId = Interest.patronId ;
```
:::warning
### 習題 6-14
:whale: 考慮[習題4-25]MP3網站系統的資料庫綱目。
2. 使用SQL查詢句來表達以下查詢:
列出下載過「今天你要嫁給我」(為一歌曲名) 的會員之會員編號和姓名。
:::
```sql=
SELECT M.mId, M.name
FROM Member AS M, Download AS D, Song AS S
WHERE S.sName = ‘今天你要嫁給我’
AND S.sId = D.sId, D.mId = M.mId ;
```
:::warning
### :whale: Extra-Excercise 3-2
:::
```sql=
(A).
CREATE TABLE FriendRelation
(mId CHAR(10) NOT NULL,
setDateTime DATETIME NOT NULL,
friend CHAR(10),
PRIMARY KEY(mId, setDateTime),
FOREIGN KEY (mId) REFERENCES Member (mId)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (friend) REFERENCES Member (mId)
ON DELETE SET NULL ON UPDATE CASCADE );
```
```sql=
(B-iv).
SELECT M.name, A.postdate
FROM Member AS M, Article AS A
#(Solution below are reference from teacher)
WHERE REGEXP_LIKE (A.content, ‘(K|k)aohsiung()*[0-9](3)([0-9](2))?’)
AND M.mId = A.mId ;
```
:::warning
### :whale: Extra-Excercise 3-3
:::
```sql=
(A).
CREATE TABLE DailySchedule
(tId CHAR(10) NOT NULL,
sDate DATE NOT NULL,
attraction VARCHAR(10), # Maybe there is a situation u can set attraction in CLOB
lodge VARCHAR(20),
PRIMARY KEY(tId,sDate),
FOREIGN KEY(tId) REFERENCES Tour(tId)
ON DELETE CASCADE ON UPDATE CASCADE );
```
```sql=
(B-i).
SELECT Sum(T.numDates)
FROM Tour AS T, Guide AS G
WHERE T.guide = G.pId
AND G.name LIKE ‘% Ma’
AND to_char(T.departureDate, 'yyyy') = '2009' ; # Can i write in thes way?
```
:::warning
### :whale: Extra-Excercise 3-5
:::
```sql=
(A). # There is a little bit different with the answer on the discussion board. However, i think this version will pass too.
SELECT M.mName, M.place, R.tName
FROM Participant AS P, Marathon AS M, Register AS R
WHERE P.name = ‘馬英九’
AND P.pId = R.pId, R.mName = M.mName ;
```
```sql=
(F). # 整題討論
UPDATE Track AS T
SET T.description=’已額滿’
FROM
(SELECT R.mName, R.tName, count(*) into v_count
FROM Register AS R
GROUP BY R.mName, R.tName)
WHERE T.number = v_count
AND T.mName = R.mName, T.tName = R.tName ;
```
:::warning
### :whale: 108警察三等特考
:::
```sql=
2(1).
CREATE TABLE Room
(rID VARCHAR(10) NOT NULL,
type VARCHAR(10) NOT NULL,
capacity INT NOT NULL,
manageDept VARCHAR(10) NOT NULL,
PRIMARY KEY (rID),
FOREIGN KEY (manageDept) REFERENCES Department(dID)
ON DELETE SET DEFAULT ON UPDATE CASCADE );
```
```sql=
CREATE TABLE Department
(dID VARCHAR(10) NOT NULL,
name VARCHAR(10) NOT NULL,
managerID VARCHAR(10) NOT NULL,
PRIMARY KEY (dID),
FOREIGN KEY (managerID) REFERENCES Employee(eID)
ON DELETE SET DEFAULT ON UPDATE CASCADE );
```
```sql=
CREATE TABLE Employee
(eID VARCHAR(10) NOT NULL,
name VARCHAR(10) NOT NULL,
deptID VARCHAR(10) NOT NULL,
expertise VARCHAR(10) NOT NULL,
PRIMARY KEY (eID,deptID),
FOREIGN KEY (deptID) REFERENCES Department(dID)
ON DELETE SET DEFAULT ON UPDATE CASCADE );
```
```sql=
CREATE TABLE UseRecord
(roomID VARCHAR(10) NOT NULL,
employeeID VARCHAR(10) NOT NULL,
date CHAR(7) NOT NULL, # The question ask to store 'date' in year plus a three-digit sequence number. Therefore, i set the datatype in CHAR directly?
startHour TIME NOT NULL,
endHour TIME NOT NULL,
purpose VARCHAR(10) NOT NULL,
PRIMARY KEY (roomID, employeeID, date, startHour),
FOREIGN KEY (roomID) REFERENCES Room(rID)
ON DELETE SET DEFAULT ON UPDATE CASCADE,
FOREIGN KEY (employeeID) REFERENCES Employee(eID)
ON DELETE SET DEFAULT ON UPDATE CASCADE );
```
:::warning
### :whale: 108調查人員考試
:::
```sql=
2(2).
SELECT t.CID
FROM instructor AS i, teach AS t
WHERE t.year = '106'
AND i.name = '張三'
AND i.IID = t.IID ;
```
:::warning
### :whale: 108調查人員考試
:::
```sql=
# Not sure cause there is no answer to compare with on discussion board.
UPDATE 訂單 AS T
SET T.付款方式 = '信用卡'
FROM 客戶 AS C
WHERE C.客戶姓名 = '孫小毛'
AND T.客戶行動電話 = C.客戶行動電話 ;
```
Sign in with Wallet
Connect another wallet