Database Management Exercise 1

Presentation on theme: "Database Management Exercise 1"— Presentation transcript:

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

2 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

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

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

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

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

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

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

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

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

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

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

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

20 Edited: Cheng-Ji Chu, HIST.NDHU,2006
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 Edited: Cheng-Ji Chu, HIST.NDHU,2006
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 Edited: Cheng-Ji Chu, HIST.NDHU,2006
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 Edited: Cheng-Ji Chu, HIST.NDHU,2006
Query 14. Group 目的： 查詢每個分行的存款人數。 存款 帳目 Edited: Cheng-Ji Chu, HIST.NDHU,2006

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

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

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

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

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

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

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

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

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

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

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