朱政吉国立东华大学历史学系.ppt

朱政吉 國立東華大學歷史學系 July 10, 2019,Database Management Exercise 1,Edited: Cheng-Ji Chu, HIST.NDHU,2006,2,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,3,Fig 1. 分行 (branch),Fig 3. 帳目 (account),Fig 2. 客戶 (customer),Example: Banking Database,Edited: Cheng-Ji Chu, HIST.NDHU,2006,4,Fig 4. 存款 (depositor),Fig 6. 借款 (borrower),Fig 5. 貸款 (loan),Example: Banking Database (cont.),Edited: Cheng-Ji Chu, HIST.NDHU,2006,5,Query 1. From,目的: 查詢借款 x 貸款的結果,8 x 7 =56 筆,借款,貸款,Edited: Cheng-Ji Chu, HIST.NDHU,2006,6,Query 1. From (cont.),Edited: Cheng-Ji Chu, HIST.NDHU,2006,7,Query 1. From (cont.),Edited: Cheng-Ji Chu, HIST.NDHU,2006,8,Query 2. From,目的： 查詢在Redwood分行有貸款的客戶。結果直欄為客戶名、貸款編號及總計。,借款,貸款,Edited: Cheng-Ji Chu, HIST.NDHU,2006,9,Query 2. From (cont.),Edited: Cheng-Ji Chu, HIST.NDHU,2006,10,Query 3. Rename,目的： 找到所有客戶的客戶名、貸款編號及總計。並且將直欄的貸款編號更名。,借款,貸款,Edited: Cheng-Ji Chu, HIST.NDHU,2006,11,Query 3. Rename (cont.),Edited: Cheng-Ji Chu, HIST.NDHU,2006,12,Query 4. Tuple Variables,目的： 查詢在某些分行有貸款的客戶。結果直欄為客戶名、貸款編號及總計。,借款,貸款,Edited: Cheng-Ji Chu, HIST.NDHU,2006,13,Query 4. Tuple Variables (cont.),Edited: Cheng-Ji Chu, HIST.NDHU,2006,14,Query 5. Tuple Variables (cont.),目的： 查詢資產大於至少一個位於Brooklyn分行的其他分行。,Edited: Cheng-Ji Chu, HIST.NDHU,2006,15,Query 5. Tuple Variables (cont.),Edited: Cheng-Ji Chu, HIST.NDHU,2006,16,Query 6. String Operation,目的： 查詢客戶所住街道之字串中，有Main的。結果直欄為客戶名。,Edited: Cheng-Ji Chu, HIST.NDHU,2006,17,Query 6. String Operation (cont.),Edited: Cheng-Ji Chu, HIST.NDHU,2006,18,Query 7. Ordering,目的： 以客戶名作排序依據，查詢所有在Perryridge分行貸款的客戶。,借款,貸款,Edited: Cheng-Ji Chu, HIST.NDHU,2006,19,Query 7. Ordering (cont.),Edited: Cheng-Ji Chu, HIST.NDHU,2006,20,Query 8. Set Operations,目的： 查詢有貸款、存款或兩者都有的客戶。,借款,貸款,Edited: Cheng-Ji Chu, HIST.NDHU,2006,21,Query 8. Set Operations (cont.),Edited: Cheng-Ji Chu, HIST.NDHU,2006,22,Query 9. Set Operations,目的： 查詢同時有貸款及存款的客戶。,借款,貸款,Edited: Cheng-Ji Chu, HIST.NDHU,2006,23,Query 9. Set Operations (cont.),Edited: Cheng-Ji Chu, HIST.NDHU,2006,24,Query 10. Set Operations,目的： 查詢有存款，但無貸款的客戶。,借款,貸款,Edited: Cheng-Ji Chu, HIST.NDHU,2006,25,Query 10. Set Operations (cont.),Edited: Cheng-Ji Chu, HIST.NDHU,2006,26,Query 11. Aggregate Functions,目的： 查詢Perryridge分行的平均結餘。,Edited: Cheng-Ji Chu, HIST.NDHU,2006,27,Query 11. Aggregate Functions (cont.),Edited: Cheng-Ji Chu, HIST.NDHU,2006,28,Query 12. Aggregate Functions,目的： 查詢客戶數目。,Edited: Cheng-Ji Chu, HIST.NDHU,2006,29,Query 12. Aggregate Functions (cont.),Edited: Cheng-Ji Chu, HIST.NDHU,2006,30,Query 13. Aggregate Functions,目的： 查詢存款人數。,Edited: Cheng-Ji Chu, HIST.NDHU,2006,31,Query 13. Aggregate Functions (cont.),Edited: Cheng-Ji Chu, HIST.NDHU,2006,32,Query 14. Group,目的： 查詢每個分行的存款人數。,帳目,存款,Edited: Cheng-Ji Chu, HIST.NDHU,2006,33,Query 14. Group (cont.),Edited: Cheng-Ji Chu, HIST.NDHU,2006,34,Query 15. Having,目的： 查詢平均帳目結餘超過1200元的分行名。,Edited: Cheng-Ji Chu, HIST.NDHU,2006,35,Query 15. Having (cont.),Edited: Cheng-Ji Chu, HIST.NDHU,2006,36,Query 16. Set Membership,目的： 查詢同時有存款及貸款的客戶。,存款,借款,Edited: Cheng-Ji Chu, HIST.NDHU,2006,37,Query 16. Set Membership (cont.),註： 另一方法為使用intersect，類似Query9。,Edited: Cheng-Ji Chu, HIST.NDHU,2006,38,Query 17. Set Membership,目的： 查詢有貸款，但無存款的客戶。,存款,借款,Edited: Cheng-Ji Chu, HIST.NDHU,2006,39,Query 17. Set Membership (cont.),Edited: Cheng-Ji Chu, HIST.NDHU,2006,40,Query 18. Set Comparison,目的： 查詢資產大於至少一個位於Brooklyn分行的其他分行。,Edited: Cheng-Ji Chu, HIST.NDHU,2006,41,Query 18. Set Comparison (cont.),註：另一方法為 Query 5.,Edited: Cheng-Ji Chu, HIST.NDHU,2006,42,Query 19. All,目的：查詢資產大於所有位於Brooklyn分行的其他分行。,Edited: Cheng-Ji Chu, HIST.NDHU,2006,43,Query 19. All (cont.),Edited: Cheng-Ji Chu, HIST.NDHU,2006,44,Query 20. Deletion,目的： 刪除所有Perryridge分行的帳目資料。,Edited: Cheng-Ji Chu,