Chapter 4: SQL

1. Chapter 4: SQL • Complex Queries • Views • Modification of the Database • Joined Relations • Security and Authorization • Embedded SQL/ODBC/JDBC

2. Derived Relations • SQL allows a subquery expression to be used in the from clause • 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)from accountgroup by branch_name )as branch_avg ( branch_name, avg_balance )where avg_balance > 1200;

3. With Clause • The with clause provides a way of defining a temporary view whose definition is available only to the query in which the with clause occurs. • Find all accounts with the maximum balance withmax_balance (value) asselectmax (balance)fromaccountselectaccount_numberfromaccount, max_balancewhereaccount.balance = max_balance.value

4. Complex Query using 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_avg wherebranch_total.value >= branch_total_avg.value

5. Views • In some cases, it is not desirable for all users to see the entire logical model (that is, all the actual relations stored in the database.) • Consider a person who needs to know a customer’s loan number but has no need to see the loan amount. This person should see a relation described, in SQL, by (select customer_name, loan_numberfrom borrower, loanwhere borrower.loan_number = loan.loan_number )

6. View Definition • A view is defined using the create view statement which has the form create view v as < query expression > where <query expression> is any legal SQL expression. The view name is represented by v. • Once a view is defined, the view name can be used to refer to the virtual relation that the view generates.

7. Example Queries • A view consisting of branches and their customers create view all_customer as(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 ) • Find all customers of the Perryridge branch select customer_namefrom all_customerwhere branch_name = ‘Perryridge’

8. Modification of the Database – Deletion • Delete all account tuples at the Perryridge branch delete from accountwhere branch_name = ‘Perryridge’ • Delete all accounts at every branch located in the city ‘Needham’. delete from accountwhere branch_name in (select branch_namefrom branchwhere branch_city = ‘Needham’)

9. Example Query • Delete the record of all accounts with balances below the average at the bank. delete from accountwhere balance < (select avg (balance )from account ) • Problem: as we delete tuples from deposit, the average balance changes • Solution used in SQL: 1. First, compute avg balance and find all tuples to delete 2. Next, delete all tuples found above (without recomputing avg or retesting the tuples)

10. Modification of the Database – Insertion • Add a new tuple to account insert into accountvalues (‘A-9732’, ‘Perryridge’,1200) or equivalentlyinsert 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 )

11. Modification of the Database – 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’ and loan.account_number =borrower.account_number

12. Modification of the Database – 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)

13. Case Statement for Conditional 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

14. Update of a View • Create a view of all loan data in the loan relation, hiding the amount attribute create view branch_loan as select branch_name, loan_numberfrom loan • Add a new tuple to branch_loan insert into branch_loanvalues (‘Perryridge’, ‘L-307’) This insertion must be represented by the insertion of the tuple (‘L-307’, ‘Perryridge’, null ) into the loan relation

15. Updates Through Views (Cont.) • Some insertion to views 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 (without aggregates) defined on a single relation

16. Joined Relations • Join operations take two relations and return as a result another relation. • These additional operations are typically used as subquery expressions in the fromclause • Join condition – defines which tuples in the two relations match, and what attributes are present in the result of the join. • Join type – defines how tuples in each relation that do not match any tuple in the other relation (based on the join condition) are treated.

17. Joined Relations

18. Joined Relations – Datasets for Examples • Relation loan andborrower • Note: borrower information missing for L-260 and loan information missing for L-155

19. Joined Relations – Examples • loan inner join borrower onloan.loan_number = borrower.loan_number • loan left outer join borrower onloan.loan_number = borrower.loan_number

20. Joined Relations – Examples • loan natural inner joinborrower • loan natural right outer join borrower

21. Joined Relations – Examples • loan full outer join borrower using (loan_number) • Find all customers who have either an account or a loan (but not both) at the bank. select customer_name from (depositor natural full outer join borrower ) where account_number is null or loan_number is null

22. Forms of authorization on parts of the database: Select - allows reading, but not modification of data. Insert - allows insertion of new data, but not modification of existing data. Update - allows modification, but not deletion of data. Delete - allows deletion of data. Authorization

23. Authorization Forms of authorization to modify the database schema • Index - allows creation and deletion of indices. • Resources - allows creation of new relations. • Alteration - allows addition or deletion of attributes in a relation. • Drop - allows deletion of relations.

24. The grant statement is used to confer authorization grant <privilege list> on <relation name or view name> to <user list> <user list> is: a user-id Public A role Authorization Specification in SQL

25. select: allows read access to relation,or the ability to query using the view Example: grant users U1, U2, and U3select authorization on the branch relation: grant select on branch to U1, U2, U3 Privileges in SQL

26. The revokestatement is used to revoke authorization. revoke <privilege list> on <relation name or view name> from <user list> Example: revoke select on branch from U1, U2, U3 <privilege-list> may be all to revoke all privileges the revokee may hold. If <revokee-list> includes public, all users lose the privilege except those granted it explicitly. Revoking Authorization in SQL

27. Revoking Authorization in SQL • If the same privilege was granted twice to the same user by different grantees, the user may retain the privilege after the revocation. • All privileges that depend on the privilege being revoked are also revoked.

28. Examples in Oracle • grant select on branch to public; • revoke select on branch from public; • grant select, insert on branch to public; • grant all privilege on branch to public; • revoke all privilege on branch from public; • revoke select on branch from public;

29. Embedded SQL • The SQL standard defines embeddings of SQL in a variety of programming languages such as C, Java, and Cobol. • A language to which SQL queries are embedded is referred to as a host language, and the SQL structures permitted in the host language comprise embedded SQL. • EXEC SQL statement is used to identify embedded SQL request to the preprocessor EXEC SQL <embedded SQL statement > END_EXEC

30. Example Query • Specify the query in SQL and declare a cursor for it EXEC SQL declare c cursor for select customer_name, customer_cityfrom depositor, customer, accountwhere depositor.customer_name = customer.customer_name and depositor account_number = account.account_numberand account.balance > :amount END_EXEC

31. Embedded SQL (Cont.) • The open statement causes the query to be evaluated EXEC SQL opencEND_EXEC • The fetchstatement causes the values of one tuple in the query result to be placed on host language variables. EXEC SQL fetch c into :cn, :cc END_EXECRepeated calls to fetch get successive tuples in the query result • The close statement causes the database system to delete the temporary relation that holds the result of the query. EXEC SQL closec END_EXEC

32. Updates Through Cursors • Can update tuples fetched by cursor by declaring that the cursor is for update declare c cursor for select *from accountwherebranch_name = ‘Perryridge’for update • To update tuple at the current location of cursor c update accountsetbalance = balance + 100where current of c

33. Dynamic SQL • Allows programs to construct and submit SQL queries at run time. • Example of the use of dynamic SQL from within a C program.char * sqlprog = “update account set balance = balance * 1.05where account_number = ?”EXEC SQL prepare dynprog from :sqlprog;char account [10] = “A-101”;EXEC SQL execute dynprog using :account;

34. ODBC and JDBC • API (application-program interface) for a program to interact with a database server • Application makes calls to • Connect with the database server • Send SQL commands to the database server • Fetch tuples of result one-by-one into program variables • ODBC (Open Database Connectivity) works with C, C++, C#, and Visual Basic • JDBC (Java Database Connectivity) works with Java

35. JDBC • JDBC is a Java API for communicating with database systems supporting SQL • JDBC also supports metadata retrieval, such as querying about relations present in the database and the names and types of relation attributes • Model for communicating with the database: • Open a connection • Create a “statement” object • Execute queries using the Statement object to send queries and fetch results • Exception mechanism to handle errors