# 資料庫
###### tags: `NSYSU`
[TOC]
[Question]假設今天的時間為 2005/9/1
## DML
1. 顯示所有圖書館內書籍的資料(資料表Allbooks)。
``` sql=
SELECT allbooks.*
FROM allbooks;
```
2. 在圖書館內的書籍中，請列出分類為 “電腦” 的書籍。
``` sql=
SELECT allbooks.*
FROM allbooks
WHERE allbooks.cate = 1;
```
3. 在圖書館內的書籍中，請列出作者為 “桂思強” 的書籍。
``` sql=
SELECT allbooks.*
FROM allbooks
WHERE allbooks.author = "桂思強";
```
4. 在圖書館內的書籍中，請列出價格超過 500 的書籍。
``` sql=
SELECT allbooks.*
FROM allbooks
WHERE allbooks.price >= 500;
```
5. 在圖書館內的書籍中，請列出 1999 年入館(InDate 欄位)的書籍。
``` sql=
SELECT allbooks.*
FROM allbooks
WHERE year(allbooks.indate) = 1999;
```
6. 請列出圖書館內每一個分類書籍的平均價格。
``` sql=
SELECT allbooks.cate, AVG(allbooks.price)
FROM allbooks
GROUP BY allbooks.cate;
```
Reference: [MSSQL依同產品筆數作平均售價](https://ithelp.ithome.com.tw/questions/10088461)
7. 尋找一本 “山居筆記”的書籍資訊。
``` sql=
SELECT allbooks.*
FROM allbooks
WHERE allbooks.bookname = "山居筆記";
```
8. 請問書籍名稱為 “井然有序”的分類及書籍編號。
sol1:
``` sql=
SELECT allbooks.cate, allbooks.bookid
FROM allbooks
WHERE allbooks.bookname = "井然有序";
```
sol2:
```sql=
SELECT category.catename, allbooks.bookid
FROM allbooks, category
WHERE allbooks.bookname = "井然有序" AND category.cid = allbooks.cate;
```
9. 請列出在 1999 年入館且作者是“桂思強”的書籍。
``` sql=
SELECT allbooks.bookname
FROM allbooks
WHERE allbooks.author = "桂思強" AND year(allbooks.indate) = 1999;
```
10. 請列出分類為“文學”的書籍與作者姓名。
``` sql=
SELECT allbooks.bookname , allbooks.author
FROM allbooks, category
WHERE allbooks.cate = category.cid AND category.catename = "文學" ;
```
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;
```
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
```
+ 步驟二: 利用兩個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;
```
:::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;
```
:::
:::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
)
```
:::
13. 將圖書館內的書籍做 “作者不同” 的統計，遞減排序列出每位作者所著作的書籍數。
``` sql=
SELECT author, COUNT(bookname) as 書籍數
FROM allbooks
Group by author
Order by COUNT(bookname) DESC;
```
14. 請列出著作的書籍數超過兩本以上的作者姓名。
``` sql=
SELECT author, COUNT(bookname) as 書籍數
FROM allbooks
Group by author
HAVING COUNT(bookname) > 2;
```
15. 請列出所有借閱人的資訊(資料表member)。
``` sql=
SELECT *
FROM member;
```
16. 請列出居住在 “台北” 的借閱者姓名與編號。
:::warning
"\*" 自行根據需求轉換
:::
``` sql=
SELECT *
FROM member
WHERE Left(city, 2) = "台北" ;
```
17. 統計每個借閱人目前所借閱的次數，列出借閱人的姓名與借閱次數。
``` sql=
SELECT name, COUNT(*) as 借閱數
FROM member, bw
WHERE member.gid = bw.gid
Group by name;
```
18. 請列出到目前為止還未歸還書籍的借閱人資訊，包括其編號，姓名，借閱日，與預計歸還的時間。
``` sql=
SELECT member.gid, member.name, bw.getdate, bw.dueback
FROM member, bw
WHERE member.gid = bw.gid AND ISNULL(bw.reback);
```
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 = "黃偉立" ;
```
20. 借閱次數排行榜：請列出到目前為止，統計每個借閱人的借閱記錄次數，並依照數目的多寡排列出來。
``` sql=
SELECT name, COUNT(*) as 借閱數
FROM member, bw
WHERE member.gid = bw.gid
Group by name
Order by COUNT(*) DESC;
```
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;
```
``` 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
```
22. 請統計 “山居筆記”這本書被借閱的次數。
``` sql=
SELECT count(*) as 借閱數
FROM bw, bwdetail, allbooks
WHERE bw.getid = bwdetail.getid
AND bwdetail.bid = allbooks.bookid
AND allbooks.bookname = "山居筆記";
```
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
```
``` 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
)
```
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
```
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
```
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
)
```
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)
```
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
```
30. 請問哪一位作者的著作常被借閱？
``` sql=
SELECT TOP 1 author, COUNT(bid)
FROM allbooks,bwdetail
WHERE bookid = bid
GROUP BY author
ORDER BY COUNT(bid) DESC
```
31. 請修改書籍編號以 “H” 為字首的數量，均增加一筆。
``` sql=
UPDATE allbooks
SET allbooks.qty = allbooks.qty + 1
WHERE bookid like 'H*'
```
32. 請新增一個書籍分類為 “科學”。
``` sql=
INSERT INTO category(cid,catename)
VALUES(4,'科學')
```
33. 請新增一本書籍，書籍名稱為 “資料庫系統”, 書籍編號為 “R00005”, 作者為 “張玉盈”, 價格為 “550”, 數量為 “2”, 分類為 “電腦”。
``` sql=
INSERT INTO allbooks(bookid,bookname,author,price,qty,indate,cate)
VALUES('R00005','資料庫系統', '張玉盈', 550, 2,'2005/09/01', 1)
```
34. 請建立一筆借閱人的資料，借閱人編號 “B00007”, 借閱人姓名為 “陳小零”, 身份證字號為 “D212345670”, 地址為 “台南市中正二路 53 號”。
``` sql=
INSERT INTO member(gid,name,ssn,city)
VALUES('B00007','陳小零','D212345670','台南市中正二路 53 號')
```
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#;
```
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#
);
```
38. 請將第七個借閱記錄的歸還日期設為今天。
``` sql=
UPDATE bw
SET dueback = #2005/9/1#
WHERE getid = 7;
```
39. “陳誠”同學借閱兩本書, “歷史的迷惘” 與 “北京法源寺”，借閱日期為今天，借閱期限為 15 日。
［ＳＴＥＰ１］：先新增一筆借閱紀錄到 [bw] TABLE中
``` sql=
INSERT INTO bw(gid, getdate, dueback)
SELECT gid, #2005/9/1#, #2005/9/1# + 15
FROM member
WHERE name = "陳誠";
```
［ＳＴＥＰ２］：再新增借閱明細到 [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 = "北京法源寺";
```
40. 請將至今未歸還書籍的借閱天數延長為 20 天。
``` sql=
UPDATE bw
SET dueback = dueback + 20
WHERE reback IS NULL;
```
## DDL
41. 產生一個逾期借閱記錄表(bw_delay)，除了原有的借閱紀錄表的欄位值外，新增一個 “delay” (逾期天數的欄位)，資料型態為數字，並將所有的相關的欄位值更新。
``` sql=
SELECT *, DATEDIFF('d', dueback, reback) AS delay
INTO bw_delay
FROM bw;
```
42. 為逾期逾期借閱記錄表(bw_delay)建立主索引，索引欄位為借閱編號(getid)。
``` sql=
ALTER TABLE bw_delay
ADD CONSTRAINT PrimaryKey PRIMARY KEY(getid); // 設定主鍵限制
```
43. 請刪除逾期借閱記錄表(bw_delay)的 delay 欄位。
``` sql=
ALTER TABLE bw_delay
DROP COLUMN delay;
```
44. 請刪除逾期借閱記錄表(bw_delay)中的 getid 主索引。
``` sql=
ALTER TABLE bw_delay
DROP CONSTRAINT PrimaryKey; // 移除主鍵限制 ≡ 刪除主鍵
```
45. 請刪除逾期借閱記錄表(bw_delay)。
``` sql=
DROP TABLE bw_delay;
```
Sign in with Wallet
Connect another wallet