---
# System prepended metadata

title: 資料庫
tags: [NSYSU]

---

# 資料庫
###### tags: `NSYSU`
[TOC]

![](https://i.imgur.com/ev54jBl.png)

[Question]假設今天的時間為 2005/9/1
## DML
1.	顯示所有圖書館內書籍的資料(資料表Allbooks)。
    ``` sql=
    SELECT allbooks.*
    FROM allbooks;
    ```
    ![](https://i.imgur.com/K3Py34O.png)

2.	在圖書館內的書籍中，請列出分類為 “電腦” 的書籍。
    ``` sql=
    SELECT allbooks.*
    FROM allbooks
    WHERE allbooks.cate = 1;
    ```
    ![](https://i.imgur.com/nBsaljD.png)

3.	在圖書館內的書籍中，請列出作者為 “桂思強” 的書籍。
    ``` sql=
    SELECT allbooks.*
    FROM allbooks
    WHERE allbooks.author = "桂思強";
    ```
    ![](https://i.imgur.com/phmjHMm.png)

4.	在圖書館內的書籍中，請列出價格超過 500 的書籍。
    ``` sql=
    SELECT allbooks.*
    FROM allbooks
    WHERE allbooks.price >= 500;
    ```
    ![](https://i.imgur.com/BfUKj8s.png)

5.	在圖書館內的書籍中，請列出 1999 年入館(InDate 欄位)的書籍。
    ``` sql=
    SELECT allbooks.*
    FROM allbooks
    WHERE year(allbooks.indate) = 1999;
    ```
    ![](https://i.imgur.com/VYRrTAc.png)

6.	請列出圖書館內每一個分類書籍的平均價格。
    ``` sql=
    SELECT allbooks.cate, AVG(allbooks.price)
    FROM allbooks
    GROUP BY allbooks.cate;
    ```
    ![](https://i.imgur.com/5kcn0vn.png)
    Reference: [MSSQL依同產品筆數作平均售價](https://ithelp.ithome.com.tw/questions/10088461)

7.	尋找一本 “山居筆記”的書籍資訊。
    ``` sql=
    SELECT allbooks.*
    FROM allbooks
    WHERE allbooks.bookname = "山居筆記";
    ```
    ![](https://i.imgur.com/jALEU9V.png)

8.	請問書籍名稱為 “井然有序”的分類及書籍編號。
    sol1:
    ``` sql=
    SELECT allbooks.cate, allbooks.bookid
    FROM allbooks
    WHERE allbooks.bookname = "井然有序";
    ```
    ![](https://i.imgur.com/y8sFO40.png)
    
    sol2:
    ```sql=
    SELECT category.catename, allbooks.bookid
    FROM allbooks, category
    WHERE allbooks.bookname = "井然有序" AND category.cid = allbooks.cate;
    ```
    ![](https://i.imgur.com/j8MVpkw.png)

    
9.	請列出在 1999 年入館且作者是“桂思強”的書籍。
	``` sql=
	SELECT allbooks.bookname
	FROM allbooks
	WHERE allbooks.author = "桂思強" AND year(allbooks.indate) = 1999;
	```
    ![](https://i.imgur.com/UCfAZz7.png)

10.	請列出分類為“文學”的書籍與作者姓名。
	``` sql=
	SELECT allbooks.bookname , allbooks.author
	FROM allbooks, category
	WHERE allbooks.cate = category.cid AND category.catename = "文學" ;
	```
	![](https://i.imgur.com/67xYhOM.png)

11.	將圖書館內的書籍做 “類別不同” 的統計，列出每個類別的名稱與書籍數。
	``` sql=
	SELECT category.catename, COUNT(category.cid) AS cid之筆數
	FROM allbooks, category
	WHERE allbooks.cate = category.cid
	Group by category.catename;
	```
    >有些書不只一本，應該用sum()
    >[name=Chen Wei]
    ``` sql=
	SELECT category.catename, SUM(allbooks.qty) AS 書籍數
    FROM allbooks, category
    WHERE allbooks.cate = category.cid
    GROUP BY category.catename;
	```
    ![](https://i.imgur.com/W50l5FZ.png)

12.	將圖書館內的書籍作者做 “類別不同” 的統計，列出每個類別的作者數。

	因DISTINCT不能寫在聚合函數裡面，所以只能用subquery(沒錯，就只有ACCESS不能這樣寫，幹)
    :::info
    >(剛剛發現DISTINCT好像是讓"每個"欄位值都唯一，所以可以這樣寫)
    >[name=Chen Wei]
    ``` sql=
    SELECT catename, COUNT(author) AS 作者數
    FROM (
        SELECT DISTINCT category.catename, allbooks.author
        FROM allbooks, category
        WHERE allbooks.cate = category.cid
    )
    GROUP BY catename;
    ```
    :::

	+ 步驟一: 先分離出唯一的作者與其分類
	```sql=
	SELECT DISTINCT author AS n,  allbooks.cate as c
	FROM allbooks, category
	Group by allbooks.cate, allbooks.author
	```
	![](https://i.imgur.com/kjFq7bY.png)


	+ 步驟二: 利用兩個subform來計算每個種類不重複的作者數 
	``` sql=
	SELECT t.c, COUNT(t.n) AS 作者數
	FROM  (
    	SELECT DISTINCT author AS n,  allbooks.cate as c
    	FROM allbooks, category
    	Group by allbooks.cate, allbooks.author
	) AS t,
	(
    	SELECT DISTINCT author AS n2,  allbooks.cate as c2
    	FROM allbooks, category
    	Group by allbooks.cate, allbooks.author
	) AS t2
	WHERE t.c = t2.c2 AND t.n = t2.n2
	Group by t.c;
	```
	![](https://i.imgur.com/wpfTvMh.png)

	:::info
	+ 題目變型: 依"類別不同"的統計，列出唯一的作者名
	``` sql=
	SELECT allbooks.cate, allbooks.author AS 作者名
	FROM  allbooks, (
    	SELECT DISTINCT allbooks.author
    	FROM allbooks, category
    	WHERE allbooks.cate = category.cid
	) 
	Group by allbooks.cate, allbooks.author;
	
	```
	![](https://i.imgur.com/hObtYYG.png)
	:::
	:::info
	+ 題目變型: 列出作者數(不重複)
	```sql=
	SELECT COUNT(t.n) AS 作者數
	FROM (
    	SELECT DISTINCT author AS n,  allbooks.cate AS c
    	FROM allbooks, category
    	Group by allbooks.cate, allbooks.author
	) AS t	;
	```
    
    ```sql=
    化簡:
    SELECT count(author) AS 作者數
    FROM
    (
    SELECT distinct author
	FROM allbooks
    )
    ```

	![](https://i.imgur.com/FZp2SLq.png)
	:::

13.	將圖書館內的書籍做 “作者不同” 的統計，遞減排序列出每位作者所著作的書籍數。
	``` sql=
	SELECT author, COUNT(bookname) as 書籍數
	FROM allbooks
	Group by author
	Order by COUNT(bookname) DESC;
	```
	![](https://i.imgur.com/IQiFGfr.png)

14.	請列出著作的書籍數超過兩本以上的作者姓名。
	``` sql=
	SELECT author, COUNT(bookname) as 書籍數
	FROM allbooks
	Group by author
	HAVING COUNT(bookname) > 2;
	```
	![](https://i.imgur.com/UNipgE3.png)

15.	請列出所有借閱人的資訊(資料表member)。
	``` sql=
	SELECT *
	FROM member;
	```
	![](https://i.imgur.com/yCHPa3m.png)

16.	請列出居住在 “台北” 的借閱者姓名與編號。
	:::warning
	"\*" 自行根據需求轉換
	:::
	``` sql=
	SELECT *
	FROM member
	WHERE Left(city, 2) = "台北" ;
	```
	![](https://i.imgur.com/VWAUfw9.png)

17.	統計每個借閱人目前所借閱的次數，列出借閱人的姓名與借閱次數。
	``` sql=
	SELECT name, COUNT(*) as 借閱數
	FROM member, bw
	WHERE member.gid = bw.gid
	Group by name;
	```
	![](https://i.imgur.com/oiZKSX6.png)

18.	請列出到目前為止還未歸還書籍的借閱人資訊，包括其編號，姓名，借閱日，與預計歸還的時間。
	``` sql=
	SELECT member.gid, member.name, bw.getdate, bw.dueback
	FROM member, bw
	WHERE member.gid = bw.gid AND ISNULL(bw.reback);
	```
	![](https://i.imgur.com/bslWKjf.png)

19.	請列出到目前為止，借閱人“黃偉立”曾經借閱的書籍。
	``` sql=
	SELECT  DISTINCT allbooks.bookname
	FROM member, bw, bwdetail, allbooks
	WHERE member.gid = bw.gid 
	AND bw.getid = bwdetail.getid 
	AND bwdetail.bid = allbooks.bookid AND member.name = "黃偉立" ;
	```
	![](https://i.imgur.com/1sQLG2R.png)

20.	借閱次數排行榜：請列出到目前為止，統計每個借閱人的借閱記錄次數，並依照數目的多寡排列出來。
	``` sql=
	SELECT name, COUNT(*) as 借閱數
	FROM member, bw
	WHERE member.gid = bw.gid
	Group by name
	Order by COUNT(*) DESC;
	```
	![](https://i.imgur.com/rcm5Jd2.png)

21.	書籍借閱排行榜：請列出到目前為止，統計每個借閱人曾經借過的書籍數目，並依照數目的多寡排列出來。
	``` sql=
	SELECT  member.gid, COUNT(*) AS 借閱數
	FROM member, bw, bwdetail
	WHERE member.gid = bw.gid 
	AND bw.getid = bwdetail.getid 
	Group by member.gid
	Order by COUNT(*) DESC;
	```
    
    
	![](https://i.imgur.com/YkwsVh3.png)

    ``` sql=
    應該不是 count 是 sum(有可能一次借兩本)
	SELECT member.name,sum(bwdetail.quan) AS 借閱本數
    FROM bwdetail,bw,member
    WHERE member.gid = bw.gid 
        AND bwdetail.getid = bw.getid
    GROUP BY member.name
    Order by sum(bwdetail.quan) desc

	```
    ![](https://i.imgur.com/56OJXzH.png)

22.	請統計 “山居筆記”這本書被借閱的次數。
	``` sql=
	SELECT count(*) as 借閱數
	FROM  bw, bwdetail, allbooks
	WHERE bw.getid = bwdetail.getid 
	AND bwdetail.bid = allbooks.bookid 
	AND allbooks.bookname = "山居筆記";
	```
	![](https://i.imgur.com/OHf73Yy.png)

23.	書籍熱門借閱排行榜：請統計到目前為止每本書籍被借閱的次數，並依照被借閱次數的多寡從大至小列舉出書籍名稱，作者，分類與借閱次數。
	``` sql=
	SELECT allbooks.bookname, allbooks.author, category.catename, count(*) as 借閱數
	FROM allbooks, bwdetail, bw, category
	WHERE bw.getid = bwdetail.getid 
	AND bwdetail.bid = allbooks.bookid 
	AND allbooks.cate = category.cid
	Group by allbooks.bookname, allbooks.author, category.catename
	Order by count(*) DESC
	```
    
    
	![](https://i.imgur.com/UC550rr.png)
    
    
    ``` sql=
	如果同一個人在一次的紀錄裡借同樣的書兩本算兩次的話
    SELECT allbooks.bookname  , allbooks.author  , category.catename ,  sum (quan) AS 借閱數
    FROM bwdetail , allbooks , category
    WHERE allbooks.bookid = bwdetail.bid AND allbooks.cate = category.cid
    Group By allbooks.bookname  , allbooks.author  , category.catename
    Order By sum (quan) DESC
	```

24.	請列出已歸還書籍(沒有再借閱書籍)的借閱人姓名及編號。
	``` sql=
	select gid, name
    from member
    where gid not in (
    select gid
    from bw
    where reback is null
    group by gid
    )
	```
    ![](https://i.imgur.com/LgvgwBP.png)
	
	sol2:
	``` sql=
	select gid, name
    from member
    where gid not in (
    select DISTINCT gid
    from bw
    where reback is null
    )
	```

25.	請列出曾經有逾期未歸還書籍記錄的借閱人編號，姓名，以及逾期天數。
	``` sql=
	SELECT name, member.gid,
    DATEDIFF("d",dueback,reback) as 逾期天數
    FROM member,bw
    WHERE member.gid = bw.gid AND 
    reback > dueback
	```
    ![](https://i.imgur.com/pR4lMOx.png)

26.	請列出目前被借出去的書籍名稱，編號，及冊數。
	``` sql=
    SELECT bookname,bookid,
    SUM(quan) as num
    FROM allbooks, bw,bwdetail
    WHERE bw.reback IS NULL AND
    bwdetail.getid = bw.getid  AND
    bookid = bid
    GROUP BY  bookname,bookid
	
	```
    ![](https://i.imgur.com/RmBXkQj.png)
    
27.	請列出目前不在借出去的書籍名單中且在圖書館內的書籍名稱，編號，及冊數。
	//Maybe wrong answer.
    ``` sql=
	SELECT DISTINCT bookname,bookid,qty
    FROM allbooks, bw,bwdetail
    WHERE bw.reback IS NOT NULL AND
    bwdetail.getid = bw.getid  AND
    bookid = bid

	```
    True:
    ```sql=
    SELECT bookname,bookid,qty
    from allbooks
    where bookname not in
    (
    SELECT DISTINCT bookname
    FROM allbooks, bw,bwdetail
    WHERE bw.reback IS NULL AND bwdetail.getid = bw.getid  AND bookid = bid
    )
    ```
    ![](https://i.imgur.com/0MkH4IH.png)

28.	請列出目前被借出去的書籍名稱，編號，及剩餘在館藏內的冊數。
	``` sql=
	SELECT DISTINCT bookname,bookid,qty
    FROM allbooks, bw,bwdetail
    WHERE bw.reback IS  NULL AND
    bwdetail.getid = bw.getid  AND
    bookid = bid
	```
    True:
    ```sql=
    SELECT allbooks.bookname,allbooks.bookid,allbooks.qty - (t.s)
    from allbooks, bwdetail,
    (
    SELECT DISTINCT bookname , sum(quan) as s
    FROM allbooks, bw,bwdetail
    WHERE bw.reback IS NULL AND bwdetail.getid = bw.getid  AND bookid = bid
    group by bookname
    ) as t
    where allbooks.bookid = bwdetail.bid
    and allbooks.bookname = t.bookname
    group by allbooks.bookname, allbooks.bookid, allbooks.qty - (t.s)
    ```
    ![](https://i.imgur.com/D2ZZHz4.png)
    
29.	請問哪一個分類的書籍被借閱的次數最多？
	``` sql=
	SELECT TOP 1 catename, COUNT(bid)
    FROM category, allbooks,bwdetail
    WHERE category.cid = allbooks.cate AND
    bid = bookid
    GROUP BY catename
    ORDER BY  COUNT(bid) DESC
	```
    ![](https://i.imgur.com/xOFDL1d.png)

30.	請問哪一位作者的著作常被借閱？
	``` sql=
	SELECT TOP 1 author, COUNT(bid)
    FROM allbooks,bwdetail
    WHERE bookid = bid
    GROUP BY author
    ORDER BY COUNT(bid) DESC
	```
    ![](https://i.imgur.com/rTHpnWB.png)

31.	請修改書籍編號以 “H” 為字首的數量，均增加一筆。
	``` sql=
	UPDATE allbooks
    SET allbooks.qty = allbooks.qty + 1
    WHERE bookid like 'H*'
	```
    ![](https://i.imgur.com/rBLoN1v.png)

32.	請新增一個書籍分類為 “科學”。
	``` sql=
	INSERT INTO category(cid,catename)
    VALUES(4,'科學')
	```
    ![](https://i.imgur.com/OIyrfl8.png)

33.	請新增一本書籍，書籍名稱為 “資料庫系統”, 書籍編號為 “R00005”, 作者為 “張玉盈”, 價格為 “550”, 數量為 “2”, 分類為 “電腦”。
	``` sql=
	INSERT INTO allbooks(bookid,bookname,author,price,qty,indate,cate)
    VALUES('R00005','資料庫系統', '張玉盈',  550, 2,'2005/09/01', 1)
	```
    ![](https://i.imgur.com/elMnZrx.png)

34.	請建立一筆借閱人的資料，借閱人編號 “B00007”, 借閱人姓名為 “陳小零”, 身份證字號為 “D212345670”, 地址為 “台南市中正二路 53 號”。
	``` sql=
	INSERT INTO member(gid,name,ssn,city)
    VALUES('B00007','陳小零','D212345670','台南市中正二路 53 號')
	```
    ![](https://i.imgur.com/YsqAQPN.png)

35.	請備份 “2005/6” 之前且已經歸還書籍的借閱記錄（將 bw 及 bwdetail 的資料表合成），產生一個備份資料表(bw_backup)，包括借閱編號，借閱人編號，書籍編號，書籍冊數等欄位。
	// wrong
    ``` sql=
	SELECT bw.getid, bw.gid, bwdetail.bid, bwdetail.quan
    INTO bw_backup
    FROM bw, bwdetail
    WHERE bw.getid = bwdetail.getid AND YEAR(reback) <= 2005 AND MONTH(reback) <= 6;
	```
	>這是錯的吧 2004/08 並沒有包含在內 但的確是在 2005/06 之前
	>[name= SHIH YU_CHANG]
	>借閱記錄哪來的 2004/08
	>[name=Chia-chih]
	>那是因為剛好沒有這筆資料 要不然也不需要判斷年分
	>[name= SHIH YU_CHANG]
	>本來就需要判斷年份，依據題意「備份 “2005/6” 之前」
	>[name=Chia-chih]
	>
    
	true
    ``` sql=
    SELECT bw.getid, bw.gid, bwdetail.bid, bwdetail.quan
    INTO bw_backup
    FROM bw, bwdetail
    WHERE bw.getid = bwdetail.getid AND reback < #2005/7/1#;
    ```
    ![](https://i.imgur.com/EMTY8IE.png)
    
36.	請將 “2005/7” 已經歸還書籍借閱記錄，包括借閱編號，借閱人編號，書籍編號，書籍冊數等欄位新增至 bw_backup 資料表中。
    ``` sql=
	INSERT INTO bw_backup
    SELECT bw.getid, bw.gid, bwdetail.bid, bwdetail.quan
    FROM bw, bwdetail
    WHERE bw.getid = bwdetail.getid AND reback < #2005/8/1#;
    ```

37.	請刪除 “2005/6”之前的已歸還書籍的借閱人與書籍記錄。
	``` sql=
	DELETE *
    FROM bw_backup
    WHERE EXISTS(
        SELECT *
        FROM bw
        WHERE bw.getid = bw_backup.getid AND reback < #2005/7/1#
    );
	```
    ![](https://i.imgur.com/xDnnwKN.png)

38.	請將第七個借閱記錄的歸還日期設為今天。
	``` sql=
	UPDATE bw
    SET dueback = #2005/9/1#
    WHERE getid = 7;
	```
    ![](https://i.imgur.com/zLHC6ts.png)

39.	“陳誠”同學借閱兩本書, “歷史的迷惘” 與 “北京法源寺”，借閱日期為今天，借閱期限為 15 日。
	［ＳＴＥＰ１］：先新增一筆借閱紀錄到 [bw] TABLE中
    ``` sql=
	INSERT INTO bw(gid, getdate, dueback)
    SELECT gid, #2005/9/1#, #2005/9/1# + 15
    FROM member
    WHERE name = "陳誠";
	```
    ![](https://i.imgur.com/ZKlguTi.png)

    ［ＳＴＥＰ２］：再新增借閱明細到 [bwdetail] TABLE中
    ``` sql=
	INSERT INTO bwdetail(getid, bid, quan)
    SELECT (
        SELECT getid
        FROM bw, member
        WHERE bw.gid = member.gid AND member.name = "陳誠" AND bw.getdate = #2005/9/1#
    ),
    bookid,
    1
    FROM allbooks
    WHERE bookname = "歷史的迷惘" OR bookname = "北京法源寺";
	```
    ![](https://i.imgur.com/siW9pql.png)

40.	請將至今未歸還書籍的借閱天數延長為 20 天。
	``` sql=
	UPDATE bw
    SET dueback = dueback + 20
    WHERE reback IS NULL;
	```
    ![](https://i.imgur.com/jMk1V13.png)

## DDL

41.	產生一個逾期借閱記錄表(bw_delay)，除了原有的借閱紀錄表的欄位值外，新增一個 “delay” (逾期天數的欄位)，資料型態為數字，並將所有的相關的欄位值更新。
	``` sql=
	SELECT *, DATEDIFF('d', dueback, reback) AS delay
    INTO bw_delay
    FROM bw;
	```
    ![](https://i.imgur.com/HilYfvP.png)

42.	為逾期逾期借閱記錄表(bw_delay)建立主索引，索引欄位為借閱編號(getid)。
	``` sql=
	ALTER TABLE bw_delay
    ADD CONSTRAINT PrimaryKey PRIMARY KEY(getid);	// 設定主鍵限制
	```
    ![](https://i.imgur.com/GYrmGmO.png)

43.	請刪除逾期借閱記錄表(bw_delay)的 delay 欄位。
	``` sql=
	ALTER TABLE bw_delay
    DROP COLUMN delay;
	```
    ![](https://i.imgur.com/Q1Mq6K6.png)

44.	請刪除逾期借閱記錄表(bw_delay)中的 getid 主索引。
	``` sql=
	ALTER TABLE bw_delay
    DROP CONSTRAINT PrimaryKey;		// 移除主鍵限制 ≡ 刪除主鍵
	```
    ![](https://i.imgur.com/DijUFTM.png)

45.	請刪除逾期借閱記錄表(bw_delay)。
	``` sql=
	DROP TABLE bw_delay;
	```
