Database Management Exercise 1 朱政吉 國立東華大學歷史學系 May 8, 2019

Edited: Cheng-Ji Chu, HIST.NDHU,2006 Contents Example: Banking Database Query 1. From Query 2. From Query 3. Rename Query 4. Tuple Variables Query 5. Tuple Variables Query 6. String Operation Query 7. Ordering Query 8. Set Operations Query 9. Set Operations Query 10. Set Operations Query 11. Aggregate Functions Query 12. Aggregate Functions Query 13. Aggregate Functions Query 14. Group Query 15. Having Query 16. Set Membership Query 17. Set Membership Query 18. Set Comparison Query 19. All Query 20. Deletion Query 21. Deletion Edited: Cheng-Ji Chu, HIST.NDHU,2006

Example: Banking Database Fig 1. 分行 (branch) Fig 2. 客戶 (customer) Edited: Cheng-Ji Chu, HIST.NDHU,2006 Fig 3. 帳目 (account)

Example: Banking Database (cont.) Fig 4. 存款 (depositor) Fig 5. 貸款 (loan) Edited: Cheng-Ji Chu, HIST.NDHU,2006 Fig 6. 借款 (borrower)

Edited: Cheng-Ji Chu, HIST.NDHU,2006 Query 1. From 目的: 查詢「借款 x 貸款」的結果 借款 貸款 8 x 7 =56 筆 Edited: Cheng-Ji Chu, HIST.NDHU,2006

Edited: Cheng-Ji Chu, HIST.NDHU,2006 Query 1. From (cont.) Edited: Cheng-Ji Chu, HIST.NDHU,2006

Edited: Cheng-Ji Chu, HIST.NDHU,2006 Query 1. From (cont.) Edited: Cheng-Ji Chu, HIST.NDHU,2006

Edited: Cheng-Ji Chu, HIST.NDHU,2006 Query 2. From 目的： 查詢在Redwood分行有「貸款」的客戶。結果直欄為「客戶名」、「貸款編號」及「總計」。 借款 貸款 Edited: Cheng-Ji Chu, HIST.NDHU,2006

Edited: Cheng-Ji Chu, HIST.NDHU,2006 Query 2. From (cont.) Edited: Cheng-Ji Chu, HIST.NDHU,2006

Edited: Cheng-Ji Chu, HIST.NDHU,2006 Query 3. Rename 目的： 找到所有客戶的「客戶名」、「貸款編號」及「總計」。並且將直欄的「貸款編號」更名。 借款 貸款 Edited: Cheng-Ji Chu, HIST.NDHU,2006

Edited: Cheng-Ji Chu, HIST.NDHU,2006 Query 3. Rename (cont.) Edited: Cheng-Ji Chu, HIST.NDHU,2006

Edited: Cheng-Ji Chu, HIST.NDHU,2006 Query 4. Tuple Variables 目的： 查詢在某些分行有「貸款」的客戶。結果直欄為「客戶名」、「貸款編號」及「總計」。 借款 貸款 Edited: Cheng-Ji Chu, HIST.NDHU,2006

Query 4. Tuple Variables (cont.) Edited: Cheng-Ji Chu, HIST.NDHU,2006

Query 5. Tuple Variables (cont.) 目的： 查詢「資產大於至少一個位於Brooklyn分行」的其他分行。 Edited: Cheng-Ji Chu, HIST.NDHU,2006

Query 5. Tuple Variables (cont.) Edited: Cheng-Ji Chu, HIST.NDHU,2006

Query 6. String Operation 目的： 查詢「客戶所住街道」之字串中，有「Main」的。結果直欄為「客戶名」。 Edited: Cheng-Ji Chu, HIST.NDHU,2006

Query 6. String Operation (cont.) Edited: Cheng-Ji Chu, HIST.NDHU,2006

Edited: Cheng-Ji Chu, HIST.NDHU,2006 Query 7. Ordering 目的： 以「客戶名」作排序依據，查詢所有在Perryridge分行「貸款」的客戶。 借款 貸款 Edited: Cheng-Ji Chu, HIST.NDHU,2006

Edited: Cheng-Ji Chu, HIST.NDHU,2006 Query 7. Ordering (cont.) Edited: Cheng-Ji Chu, HIST.NDHU,2006

Edited: Cheng-Ji Chu, HIST.NDHU,2006 Query 8. Set Operations 目的： 查詢有「貸款」、「存款」或「兩者都有」的客戶。 借款 貸款 Edited: Cheng-Ji Chu, HIST.NDHU,2006

Query 8. Set Operations (cont.) Edited: Cheng-Ji Chu, HIST.NDHU,2006

Edited: Cheng-Ji Chu, HIST.NDHU,2006 Query 9. Set Operations 目的： 查詢同時有「貸款」及「存款」的客戶。 借款 貸款 Edited: Cheng-Ji Chu, HIST.NDHU,2006

Query 9. Set Operations (cont.) Edited: Cheng-Ji Chu, HIST.NDHU,2006

Edited: Cheng-Ji Chu, HIST.NDHU,2006 Query 10. Set Operations 目的： 查詢有「存款」，但無「貸款」的客戶。 借款 貸款 Edited: Cheng-Ji Chu, HIST.NDHU,2006

Query 10. Set Operations (cont.) Edited: Cheng-Ji Chu, HIST.NDHU,2006

Query 11. Aggregate Functions 目的： 查詢Perryridge分行的平均結餘。 Edited: Cheng-Ji Chu, HIST.NDHU,2006

Query 11. Aggregate Functions (cont.) Edited: Cheng-Ji Chu, HIST.NDHU,2006

Query 12. Aggregate Functions 目的： 查詢「客戶」數目。 Edited: Cheng-Ji Chu, HIST.NDHU,2006

Query 12. Aggregate Functions (cont.) Edited: Cheng-Ji Chu, HIST.NDHU,2006

Query 13. Aggregate Functions 目的： 查詢「存款」人數。 Edited: Cheng-Ji Chu, HIST.NDHU,2006

Query 13. Aggregate Functions (cont.) Edited: Cheng-Ji Chu, HIST.NDHU,2006

Edited: Cheng-Ji Chu, HIST.NDHU,2006 Query 14. Group 目的： 查詢每個分行的存款人數。 存款 帳目 Edited: Cheng-Ji Chu, HIST.NDHU,2006

Edited: Cheng-Ji Chu, HIST.NDHU,2006 Query 14. Group (cont.) Edited: Cheng-Ji Chu, HIST.NDHU,2006

Edited: Cheng-Ji Chu, HIST.NDHU,2006 Query 15. Having 目的： 查詢「平均帳目結餘」超過1200元的分行名。 Edited: Cheng-Ji Chu, HIST.NDHU,2006

Edited: Cheng-Ji Chu, HIST.NDHU,2006 Query 15. Having (cont.) Edited: Cheng-Ji Chu, HIST.NDHU,2006

Edited: Cheng-Ji Chu, HIST.NDHU,2006 Query 16. Set Membership 目的： 查詢同時有「存款」及「貸款」的客戶。 存款 借款 Edited: Cheng-Ji Chu, HIST.NDHU,2006

Query 16. Set Membership (cont.) 註： 另一方法為使用「intersect」，類似Query9。 Edited: Cheng-Ji Chu, HIST.NDHU,2006

Edited: Cheng-Ji Chu, HIST.NDHU,2006 Query 17. Set Membership 目的： 查詢有「貸款」，但無「存款」的客戶。 存款 借款 Edited: Cheng-Ji Chu, HIST.NDHU,2006

Query 17. Set Membership (cont.) Edited: Cheng-Ji Chu, HIST.NDHU,2006

Edited: Cheng-Ji Chu, HIST.NDHU,2006 Query 18. Set Comparison 目的： 查詢「資產大於至少一個位於Brooklyn分行」的其他分行。 Edited: Cheng-Ji Chu, HIST.NDHU,2006

Query 18. Set Comparison (cont.) Edited: Cheng-Ji Chu, HIST.NDHU,2006

Edited: Cheng-Ji Chu, HIST.NDHU,2006 Query 19. All 目的：查詢「資產大於所有位於Brooklyn分行」的其他分行。 Edited: Cheng-Ji Chu, HIST.NDHU,2006

Edited: Cheng-Ji Chu, HIST.NDHU,2006 Query 19. All (cont.) Edited: Cheng-Ji Chu, HIST.NDHU,2006

Edited: Cheng-Ji Chu, HIST.NDHU,2006 Query 20. Deletion 目的： 刪除所有Perryridge分行的「帳目」資料。 Edited: Cheng-Ji Chu, HIST.NDHU,2006

Edited: Cheng-Ji Chu, HIST.NDHU,2006 Query 20. Deletion (cont.) 刪除前 刪除後 Edited: Cheng-Ji Chu, HIST.NDHU,2006

Edited: Cheng-Ji Chu, HIST.NDHU,2006 Query 21. Deletion 目的： 刪除所有位於Brooklyn的所有分行之「帳目」資料。 Edited: Cheng-Ji Chu, HIST.NDHU,2006

Edited: Cheng-Ji Chu, HIST.NDHU,2006 Query 21. Deletion (cont.) 刪除前 刪除後 Edited: Cheng-Ji Chu, HIST.NDHU,2006