Chapter 3 SQL (part II)

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!