1 / 41

Chapter 3 SQL (part II)

Chapter 3 SQL (part II). Hankz Hankui Zhuo. SQL part I. Data definition Query & Update Tables !. This part. Nested Subqueries Views: definition/operations Modification. Nested Subqueries : in. Find all customers who have both an account and a loan at the bank:.

ling
Download Presentation

Chapter 3 SQL (part II)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Chapter 3 SQL (part II) HankzHankuiZhuo

  2. SQL part I • Data definition • Query & Update Tables!

  3. This part • Nested Subqueries • Views: definition/operations • Modification

  4. Nested Subqueries: in • Find all customers who have both an account and a loan at the bank: branch (branch_name, branch_city, assets) customer (customer_name, customer_street, customer_city) account (account_number, branch_name, balance) loan (loan_number, branch_name, amount) depositor (customer_name, account_number) borrower(customer_name, loan_number)

  5. Nested Subqueries: in • Find all customers who have both an account and a loan at the bank: select distinctcustomer_namefrom borrowerwhere customer_namein(selectcustomer_namefromdepositor ) branch (branch_name, branch_city, assets) customer (customer_name, customer_street, customer_city) account (account_number, branch_name, balance) loan (loan_number, branch_name, amount) depositor (customer_name, account_number) borrower(customer_name, loan_number)

  6. Nested Subqueries: not in • Find all customers who have a loan at the bank but do not have an account at the bank: branch (branch_name, branch_city, assets) customer (customer_name, customer_street, customer_city) account (account_number, branch_name, balance) loan (loan_number, branch_name, amount) depositor (customer_name, account_number) borrower(customer_name, loan_number)

  7. Nested Subqueries: not in • Find all customers who have a loan at the bank but do not have an account at the bank: select distinct customer_name from borrower where customer_namenot in(select customer_namefrom depositor ) branch (branch_name, branch_city, assets) customer (customer_name, customer_street, customer_city) account (account_number, branch_name, balance) loan (loan_number, branch_name, amount) depositor (customer_name, account_number) borrower(customer_name, loan_number)

  8. Nested Subqueries: some • Find all branches that have greater assets than some branch located in Brooklyn. branch (branch_name, branch_city, assets) customer (customer_name, customer_street, customer_city) account (account_number, branch_name, balance) loan (loan_number, branch_name, amount) depositor (customer_name, account_number) borrower(customer_name, loan_number)

  9. Nested Subqueries: some • Find all branches that have greater assets than some branch located in Brooklyn. selectbranch_namefrom branchwhere assets > some (select assets from branchwherebranch_city = 'Brooklyn') branch (branch_name, branch_city, assets) customer (customer_name, customer_street, customer_city) account (account_number, branch_name, balance) loan (loan_number, branch_name, amount) depositor (customer_name, account_number) borrower(customer_name, loan_number) How to write a simple expression?

  10. Nested Subqueries: some • Find all branches that have greater assets than some branch located in Brooklyn. select distinct T.branch_namefrombranch as T, branch as Swhere T.assets > S.assetsand S.branch_city= 'Brooklyn' branch (branch_name, branch_city, assets) customer (customer_name, customer_street, customer_city) account (account_number, branch_name, balance) loan (loan_number, branch_name, amount) depositor (customer_name, account_number) borrower(customer_name, loan_number) How to write a simple expression?

  11. 0 5 6 Nested Subqueries: some • F <comp> some r t r such that (F <comp> t ) • <comp> :      (5 < some ) = true (read: 5 < some tuple in the relation) 0 (5 < some ) = false 5 0 ) = true (5 = some 5 0 (5 some ) = true (since 0  5) 5 (= some)  in However, ( some)  not in

  12. Nested Subqueries: all • Find the names of all branches that have greater assets than all branches located in Brooklyn. branch (branch_name, branch_city, assets) customer (customer_name, customer_street, customer_city) account (account_number, branch_name, balance) loan (loan_number, branch_name, amount) depositor (customer_name, account_number) borrower(customer_name, loan_number)

  13. Nested Subqueries: all • Find the names of all branches that have greater assets than all branches located in Brooklyn. select branch_namefrom branchwhere assets > all(select assets frombranchwhere branch_city = 'Brooklyn') branch (branch_name, branch_city, assets) customer (customer_name, customer_street, customer_city) account (account_number, branch_name, balance) loan (loan_number, branch_name, amount) depositor (customer_name, account_number) borrower(customer_name, loan_number)

  14. 0 5 6 Nested Subqueries: all • F <comp> all r t r (F <comp> t) (5 < all ) = false 6 ) = true (5 < all 10 4 ) = false (5 = all 5 4 ) = true (since 5  4 and 5  6) (5 all 6 (all)  not in However, (= all)  in

  15. Nested Subqueries: exists/not exists • exists r  r  Ø • not exists r  r = Ø • Example: Select … From … Whereexists r

  16. Nested Subqueries: exists/not exists • Find all customers who have an account at all branches located in Brooklyn. branch (branch_name, branch_city, assets) customer (customer_name, customer_street, customer_city) account (account_number, branch_name, balance) loan (loan_number, branch_name, amount) depositor (customer_name, account_number) borrower(customer_name, loan_number)

  17. Nested Subqueries: exists/not exists • Find all customers who have an account at all branches located in Brooklyn. select distinct S.customer_namefrom depositor as Swhere not exists ((select branch_namefrom branchwhere branch_city = 'Brooklyn') except(select R.branch_namefromdepositor as T, account as Rwhere T.account_number = R.account_numberandS.customer_name = T.customer_name)) branch (branch_name, branch_city, assets) customer (customer_name, customer_street, customer_city) account (account_number, branch_name, balance) loan (loan_number, branch_name, amount) depositor (customer_name, account_number) borrower(customer_name, loan_number)

  18. Nested Subqueries: unique • Find all customers who have at most one account at the Perryridge branch branch (branch_name, branch_city, assets) customer (customer_name, customer_street, customer_city) account (account_number, branch_name, balance) loan (loan_number, branch_name, amount) depositor (customer_name, account_number) borrower(customer_name, loan_number)

  19. Nested Subqueries: unique • Find all customers who have at most one account at the Perryridge branch select T.customer_namefrom depositor as T where unique(select R.customer_namefrom account, depositor as RwhereT.customer_name = R.customer_nameandR.account_number= account.account_numberandaccount.branch_name = 'Perryridge') branch (branch_name, branch_city, assets) customer (customer_name, customer_street, customer_city) account (account_number, branch_name, balance) loan (loan_number, branch_name, amount) depositor (customer_name, account_number) borrower(customer_name, loan_number)

  20. Nested Subqueries: unique • Find all customers who have at least two accounts at the Perryridge branch. select distinct T.customer_name from depositor as T where not unique( select R.customer_name from account, depositor as R where T.customer_name = R.customer_nameand R.account_number = account.account_numberand account.branch_name ='Perryridge')

  21. Nested Subqueries: Derived Relations • Find the average account balance of those branches where the average account balance is greater than $1200.

  22. Nested Subqueries: Derived Relations • Find the average account balance of those branches where the average account balance is greater than $1200. select branch_name, avg_balancefrom (select branch_name, avg(balance)fromaccountgroup bybranch_name)as branch_avg( branch_name, avg_balance)where avg_balance > 1200 Still remember the having clause???

  23. Nested Subqueries: With Clause • Find all accounts with the maximum balance withmax_balance(value) asselectmax (balance)fromaccountselectaccount_numberfromaccount, max_balancewhereaccount.balance = max_balance.value

  24. Nested Subqueries: With Clause • Find all branches where the total account deposit is greater than the average of the total account deposits at all branches.

  25. Nested Subqueries: With Clause • Find all branches where the total account deposit is greater than the average of the total account deposits at all branches. withbranch_total (branch_name, value) asselectbranch_name, sum (balance)fromaccountgroupbybranch_namewithbranch_total_avg(value) asselectavg(value)frombranch_totalselect branch_namefrombranch_total, branch_total_avgwherebranch_total.value >= branch_total_avg.value

  26. Views

  27. Views • No need to see all relations • Consider a person who needs to know a customer’s name, loan number and branch name, but has no need to see the loan amount. • A view provides a mechanism to hide certain data • Any relation that is not of the conceptual model but is made visible to a user as a “virtual relation” is called a view. (select customer_name, borrower.loan_number, branch_namefrom borrower, loanwhere borrower.loan_number = loan.loan_number)

  28. Views definition • Defined by: • create view v as < query expression > • Once views are defined, the view names can be used. • Corresponding to a view, the query expression is stored; once the view name is used, it is substituted by its query expression.

  29. Views examples • A view consisting of branches and their customers create view all_customeras(select branch_name, customer_namefrom depositor, accountwhere depositor.account_number = account.account_number) union(select branch_name, customer_namefrom borrower, loanwhere borrower.loan_number = loan.loan_number)

  30. Views examples • Find all customers of the Perryridge branch select customer_namefrom all_customerwhere branch_name = 'Perryridge'

  31. Views Defined Using Other Views • One view may be used in the expression defining another view • A view relation v1 is said to depend directlyon a view relation v2 if v2 is used in the expression defining v1 • A view relation v1 is said to depend on view relation v2if either v1 depends directly to v2 or there is a path of dependencies from v1 to v2 • A view relation v is said to be recursive if it depends on itself.

  32. View Expansion • A way to define the meaning of views defined in terms of other views. • Let view v1 be defined by an expression e1 that may itself contain uses of view relations. • View expansion of an expression repeats the following replacement step: repeatFind any view relation vi in e1 Replace the view relation vi by the expression defining viuntilno more view relations are present in e1 • As long as the view definitions are not recursive, this loop will terminate

  33. Modification

  34. Modification: deletion • Delete all account tuples at the Perryridge branch delete from accountwherebranch_name = 'Perryridge' • Delete all accounts at every branch located in the city ‘Needham’. delete from accountwhere branch_namein (select branch_namefrom branchwhere branch_city = 'Needham')

  35. Modification: insertion • Add a new tuple to account insert into accountvalues ('A-9732', 'Perryridge', 1200) or equivalently insert into account (branch_name, balance, account_number)values ('Perryridge', 1200, 'A-9732') • Add a new tuple to account with balance set to null insert into accountvalues ('A-777','Perryridge', null )

  36. Modification: insertion • Provide as a gift for all loan customers of the Perryridge branch, a $200 savings account. Let the loan number serve as the account number for the new savings account insert into accountselect loan_number, branch_name, 200from loanwhere branch_name = 'Perryridge' insert into depositorselect customer_name, loan_numberfrom loan, borrowerwhere branch_name = 'Perryridge' andloan.account_number = borrower.account_number

  37. Modification: updates • Increase all accounts with balances over $10,000 by 6%, all other accounts receive 5%. • Write two update statements: update accountset balance = balance  1.06where balance > 10000 update accountset balance = balance  1.05where balance  10000 • The order is important • Can be done better using the case statement (next slide)

  38. Modification: updates • Same query as before: Increase all accounts with balances over $10,000 by 6%, all other accounts receive 5%. updateaccountsetbalance = casewhenbalance <= 10000 thenbalance *1.05elsebalance * 1.06end

  39. Modification: views • Create a view of all loan data in the loan relation, hiding the amount attribute create view loan_branchas select loan_number, branch_namefrom loan • Add a new tuple to branch_loan insert into branch_loanvalues ('L-37‘, 'Perryridge‘) This insertion must be represented by the insertion of the tuple ('L-37', 'Perryridge', null ) into the loan relation

  40. Modification: views • Some updates cannot be translated uniquely insert into all_customer values ('Perryridge', 'John') • Have to choose loan or account, and create a new loan/account number! • Most SQL implementations allow updates only on simple views defined on a single relation

  41. The End!

More Related