第七章 習題

 

[ 習題7-1 ]

考慮以下的查詢：

SELECT tNo, COUNT(pNo)

FROM (Transaction NATURAL JOIN Record) NATURAL JOIN Product

WHERE method = ‘cart’ AND unitPrice > 500

GROUP BY tNo

HAVING COUNT(pNo) > 2;

 

請解釋以上查詢句的意義，它和本章Q20有何不同？

 

[ 習題7-2 ]

參考圖6-1的資料庫綱目， 並按題意列出 SQL 查詢句

1.    列出居住於台北縣且沒有介紹過其他會員的會員之會員編號和姓名。

2.    列出沒有被購買過的書籍之商品編號和名稱。

3.    列出在2004年從未被瀏覽過也從未被買過的所有商品之商品編號 和名稱。

4.    請列出Jenny所瀏覽過但未購買的商品之商品編號和名稱。

5.    列出瀏覽過但沒購買過’蔡依林專輯二’的會員之會員編號和姓名。

 

[ 習題7-3 ]

參考圖6-1的資料庫綱目， 並按題意列出 SQL 查詢句：

1.     請產生一個名為Trans2005的VIEW，列出2005年被購買的每件商品之商品編號,名稱,種類，購買總數量，購買總金額。沒有被購買的不用列出。

2.     請根據Trans2005，找出在2005年被購買的每件書籍之商品編號,名稱及購買總數量。

3.    請根據Trans2005，找出2005年的書籍總購買金額。

4.     請根據Trans2005和其他資料表，找出在2005年裏都沒有被購買過的商品。其中包括商品編號，名稱，創作者及定價。

 

 

[ 習題7-4 ]

參考圖6-1的資料庫綱目， 並按題意列出 SQL 查詢句

1.    列出每一商品之編號，名稱，定價，和創作者人數，請注意沒有創作者的商品資訊也要列出。

2.    列出每一女性會員（身分證的第二碼為2）的會員編號，姓名，和購買總金額。

3.    列出2004年2月的總交易金額。

4.    產生一個名為MalePurchase的VIEW，列出男性會員對於每一類商品在2004年的購買總金額。（提示：由pId的第二個字元可看出性別—1為男性2為女性）

5.    根據MalePurchase的VIEW，列出男性會員在2004年購買書籍類商品的總金額。

6.    產生一個名為BrowseInf的VIEW，包括欄位會員編號，會員姓名，其瀏覽時間，和所瀏覽的商品名稱。

7.    將第6小題的VIEW授權給syhwang查詢但不可轉移授權。

8.    利用第6小題的VIEW和其他資料表，列出每一位女性會員的姓名，和其在2004年10月份瀏覽每一件商品的次數。

9.    將2004年10月份銷售總金額超過100,000的商品定價提高一成。

10.    列出購買過所有「孫燕姿」CD（孫燕姿為創作者）的會員之會員編號和姓名。

11.    對於每一在2004年10月份被超過100位會員瀏覽的商品，列出其商品編號，商品名稱，和其在2004年10月份的銷售總金額。

 

 

 

[ 習題7-5 ]

參考圖6-1的資料庫綱目， 並按題意列出 SQL 查詢句

1.    找出有購買過編號為‘a0910001’的會員所購買過的所有商品的會員之會員編號和姓名。

2.    對於每一筆交易總金額超過1,000的交易，列出其交易編號和所購買的商品種樣數。

3.    請找出2005年10月份每一會員的交易總金額，列出會員編號，姓名， 交易總金額。請注意沒有交易的會員也需列出，其交易總金額則為0。

4.    對於每一寫書超過5本以上的作者﹝由Author資料表得知，並假設作者姓名為唯一﹞，列出其姓名和2005年10月份銷售書籍的總金額。

 

 

[ 習題7-6 ]

參考圖6-1的資料庫綱目， 並按題意列出 SQL 查詢敘述

1.    1. 請將Member 資料表裡加入一個型態為VARCHAR（30）的屬性url，並修改屬性名稱address為mailing_add。

2.    請用CREATE ASSERTION來表達以下資料限制：每一本書（也就是catalog=’Book’的商品）至少需有一位作者。

3.    2. 找出在2005年購買高單價商品（假設超過1000元的商品稱為高單價商品）超過5件的會員之會員編號，姓名和其2005年的交易總金額。

 

[ 習題7-7 ]

考慮 [習題4-6] 遠距教學的資料庫綱目，請用SQL查詢句表達以下查詢：

列出修”資料庫管理”的每位同學的學號和張貼篇數。


[ 習題7-8 ]

考慮 [習題4-8] 職棒聯盟戰績的資料庫綱目，請用SQL查詢句表達以下查詢：

1.     列出' New York Yankees'總共贏幾場和輸幾場。

2.     找出’American League’的常勝軍（即那些在該聯盟且從未被打敗的球隊名稱）。 

3.     對於每一球員超過15人的球隊，列出其球隊名稱和月薪超過10萬元的球員數。

 


[ 習題7-9 ]

考慮 [習題4-9] 小型工廠的庫存系統的資料庫綱目，請用SQL查詢句表達以下查詢：

1.    對於每一供應100種以上物料的供應商，列出該供應商公司名稱和其2000年11月份的進料種類數。   

2.     找出力霸鋁條在2000年10月的總進料量。

3.     對於每一在2000年10月的總進料量超過20000的物料，列出其名稱和其在2000年10月份的總領料量。

 


[ 習題7-10 ]

考慮 [習題4-10] 錄影帶租借系統的資料庫綱目，請用SQL查詢句表達以下查詢：

1.       列出李安所導演的各影片種類的總片數。

2.       將”臥虎藏龍”(為一片名)按各媒體(VCR, DVD, VCD, etc)列出其拷貝數量。

3.       將每一影片數超過100的影片種類，列出其名稱和等級為'PG-13'的片數。

 


[ 習題7-11 ]

考慮 [習題4-11] 問卷調查系統的資料庫綱目，請用SQL查詢句表達以下查詢：

1.     對於每一問卷，列出其title和visible='Y'的單選題的題數。

2.     對於每一在2001年被填寫的次數超過50次的問卷，列出其title和單選題的題數。



[ 習題7-12 ]

考慮 [習題4-12] 拍賣網站系統的資料庫綱目，請用SQL查詢句表達以下查詢：

1.     對於一mID='m0002', seqNo='003' 的商品，列出其拍賣者姓名、底價、和目前最高叫價

2.     對於每一會員，列出其在2002年11月的總叫價次數和總成交次數。

 


[ 習題7-13 ]

考慮 [習題4-14] 餐廳點菜系統的資料庫綱目 ，

1.     對於每一個套餐，列出其名稱、價錢、和其在 2004 年 11 月被點的總次數。請注意，當一套餐完全沒有被點時，也要列出這些資訊。請用一個 SQL 查詢句表達。

2.     對於每一個由三種以上單點所組成的套餐，列出其名稱、價錢、和其在 2004 年 11 月被點的總次數。請用一個 SQL 查詢句表達。

 

[ 習題7-14 ]

考慮 [習題4-15] 醫院掛號系統的資料庫綱目，請用SQL表達出以下的查詢或資料維護。

1.     列出一般內科的醫師之編號，姓名，和職級，而且同一職級的醫師要排在一起。

2.     對於每一位一般內科醫師，列出其編號，姓名、和在2006年4月的總掛號人次。請注意沒有被掛號的醫師資料也必須列出，此時其總掛號人次為0。請用一個SQL查詢句表達。

3.     請列出每一位在2005年掛號過所有一般內科醫師的病人之病歷號，姓名，生日，和性別。請用一個SQL查詢句表達。

4.     對於每一個醫師人數超過10人的科，列出科名和2006年4月的掛號總人次。

5.     請用SQL敘述產生一個實體的資料表DoctorPatients（dId, totalNum），記載每一位醫師的編號和其總掛號人次。

6.     請產生一個Trigger，每增加一筆預約記錄時就將DoctorPatients裡該醫師的總掛號人次加1。

 


[ 習題7-15 ]

考慮 [習題4-16] 台灣總統選舉民調的資料庫綱目，請用SQL查詢句表達以下查詢：

列出每一位總統候選人之登記號，姓名，和民調得票數。

 

 


[ 習題7-16 ]

考慮 [習題4-17] 新書推薦系統的資料庫綱目，請用SQL查詢句表達以下查詢：

1.     對於每一個系所，列出系所名稱和其讀者數。

2.     假設分類階層只有三層，找出張三（讀者名）感興趣的所有書名。 

 

 

[ 習題7-17 ]

請問以下兩個CREATE INDEX敘述有何差別：

CREATE INDEX CatPrice_index
ON Product(catalog, unitPrice);

CREATE INDEX PriceCat_index
ON Product(unitPrice, catalog);

 

〔ch7 參考解答　〕