International Computer Institute, Izmir, Turkey SQL

1. International Computer Institute, Izmir, TurkeySQL Asst.Prof.Dr.İlker KocabaşUBİ502 at http://ube.ege.edu.tr/~ikocabas

2. Overview • Joins: types and conditions • Example 1: our languages • relational algebra • tuple relational calculus • domain relational calculus • SQL • Examples 2, 3 • joins, aggregates, MySQL • Join tricks

3. Schema Diagram for the Banking Enterprise

4. Joins • Join types: • Inner join • Left outer join • Right outer join • Full outer join • Join conditions: • Natural • On <predicate> • Using (A1, …, An)

5. The loan and borrower Relations

6. loan INNER JOIN borrowerON loan.loan-number = borrower.loan-number

7. loan LEFT OUTER JOIN borrowerUSING (loan-number)

8. Join Conditions • a INNER JOIN b USING (c1, ..., cn) SELECT * FROM loan INNER JOIN borrower USING (loan_number); +-------------+-------------+--------+---------------+-------------+ | loan_number | branch_name | amount | customer_name | loan_number | +-------------+-------------+--------+---------------+-------------+ | L-16 | Perryridge | 1300 | Adams | L-16 | | L-93 | Mianus | 500 | Curry | L-93 | ... | L-17 | Downtown | 1000 | Williams | L-17 | +-------------+-------------+--------+---------------+-------------+ • Equivalent:a INNER JOIN b ON (a.c1 = b.c1, …, a.cn = b.cn)

9. Join Conditions • Suppose (c1, …, cn) is a complete list of attributes common to A and B • Then:a INNER JOIN b USING (c1, …, cn)is equivalent to:a NATURAL JOIN b • E.g. since there is only one common attribute between loan and borrower, the following queries give the same result:SELECT * FROM loan INNER JOIN borrower USING (loan_number);SELECT * FROM loan NATURAL JOIN borrower;

10. Example 1: Our languages Query: What branches are in Brooklyn? branch-name (branch-city = “Brooklyn” (branch)) {t |  s branch (t[branch-name] = s[branch-name] s [branch-city] = “Brooklyn”)} { n  | c, a ( n, c, a   branch  c = “Brooklyn”)} SELECT branch-name FROM branchWHERE branch_city = “Brooklyn”

11. Example 2 Who has a loan with a greater value than every loan at Perryridge branch? t1 ← mp(max)( gmax(amount)(branch-name = “Perryridge” (loan))) (customer-name(loan-number(amount > t1.max (loan)) ⋈ borrower)

12. Example 2 (cont) branch-name = “Perryridge” (loan) mysql> SELECT * FROM loan -> WHERE branch_name = "Perryridge"; +-------------+-------------+--------+ | loan_number | branch_name | amount | +-------------+-------------+--------+ | L-15 | Perryridge | 1500 | | L-16 | Perryridge | 1300 | +-------------+-------------+--------+

13. Example 2 (cont) gmax(amount)(branch-name = “Perryridge” (loan)) mysql> SELECT MAX(amount) FROM loan -> WHERE branch_name = "Perryridge"; +-------------+ | max(amount) | +-------------+ | 1500 | +-------------+

14. Example 2 (cont) mp(max)( gmax(amount)(branch-name = “Perryridge” (loan))) mysql> SELECT MAX(amount) AS max FROM loan -> WHERE branch_name = "Perryridge"; +------+ | max | +------+ | 1500 | +------+

15. Example 2 (cont) t1 ← mp(max)( gmax(amount)(branch-name = “Perryridge” (loan))) mysql> CREATE TEMPORARY TABLE t1 -> SELECT MAX(amount) AS max FROM loan -> WHERE branch_name = "Perryridge";

16. Example 2 (cont) amount > t1.max (loan) mysql> SELECT * FROM loan, t1 -> WHERE amount > t1.max; +-------------+-------------+--------+------+ | loan_number | branch_name | amount | max | +-------------+-------------+--------+------+ | L-23 | Redwood | 2000 | 1500 | | L-20 | North Town | 7500 | 1500 | +-------------+-------------+--------+------+

17. Example 2 (cont) (amount (amount > t1.max (loan)) mysql> SELECT amount FROM loan, t1 -> WHERE amount > t1.max; +--------+ | amount | +--------+ | 2000 | | 7500 | +--------+

18. Example 2 (cont) customer-name(amount (amount > t1.max (loan)) ⋈ borrower) mysql> SELECT customer_name -> FROM loan NATURAL JOIN borrower, t1 -> WHERE amount > t1.max; +---------------+ | customer_name | +---------------+ | McBride | | Smith | +---------------+

19. Example 3 Query: What is the average loan amount held by customers in each city? branch-citygavg(amount)(loan ⋈ branch)

20. Example 3 (cont) mysql> select * -> from loan natural join branch; +-------------+-------------+--------+-------------+-------------+---------+ | loan_number | branch_name | amount | branch_name | branch_city | assets | +-------------+-------------+--------+-------------+-------------+---------+ | L-17 | Downtown | 1000 | Downtown | Brooklyn | 900000 | | L-23 | Redwood | 2000 | Redwood | Palo Alto | 2100000 | | L-15 | Perryridge | 1500 | Perryridge | Horseneck | 1700000 | | L-14 | Downtown | 1500 | Downtown | Brooklyn | 900000 | | L-93 | Mianus | 500 | Mianus | Horseneck | 400200 | | L-11 | Round Hill | 900 | Round Hill | Horseneck | 8000000 | | L-16 | Perryridge | 1300 | Perryridge | Horseneck | 1700000 | | L-20 | North Town | 7500 | North Town | Rye | 3700000 | | L-21 | Central | 570 | Central | Rye | 400280 | +-------------+-------------+--------+-------------+-------------+---------+ • Would like to see just the branch_name and branch_city columns

21. Example 3 (cont) mysql> select branch_city, amount -> from loan natural join branch; +-------------+--------+ | branch_city | amount | +-------------+--------+ | Brooklyn | 1000 | | Palo Alto | 2000 | | Horseneck | 1500 | | Brooklyn | 1500 | | Horseneck | 500 | | Horseneck | 900 | | Horseneck | 1300 | | Rye | 7500 | | Rye | 570 | +-------------+--------+ • Would like to see this table sorted by branch_city

22. Example 3 (cont) mysql> select branch_city, amount -> from loan natural join branch -> order by branch_city; +-------------+--------+ | branch_city | amount | +-------------+--------+ | Brooklyn | 1000 | | Brooklyn | 1500 | | Horseneck | 1300 | | Horseneck | 1500 | | Horseneck | 500 | | Horseneck | 900 | | Palo Alto | 2000 | | Rye | 7500 | | Rye | 570 | +-------------+--------+ • Next: compute average for each group

23. Example 3 (cont) mysql> select branch_city, avg(amount) -> from loan natural join branch -> group by branch_city; +-------------+-------------+ | branch_city | avg(amount) | +-------------+-------------+ | Brooklyn | 1250.0000 | | Horseneck | 1050.0000 | | Palo Alto | 2000.0000 | | Rye | 4035.0000 | +-------------+-------------+ • Suppose we wanted to impose a constraint on the result – use a HAVING clause

24. Example 3 (cont) mysql> select branch_city as city, avg(amount) as average -> from loan natural join branch -> group by branch_city -> having (avg(amount) > 1500); +-----------+-----------+ | city | average | +-----------+-----------+ | Palo Alto | 2000.0000 | | Rye | 4035.0000 | +-----------+-----------+ • Note: we also renamed the columns

25. Join tricks • Restrict a to just those rows compatible with b • SELECT DISTINCT a.* FROM a NATURAL JOIN b; • E.g. list tuples from the borrower table only for those borrowers who are depositors: • SELECT DISTINCT borrower.*FROM borrower NATURAL JOIN depositor;

26. Join tricks (cont) • Compute set difference without using EXCEPT(MySQL 3.23 doesn’t have set operations)i.e.remove from a those tuples compatible with b • SELECT DISTINCT a.* FROM a NATURAL LEFT OUTER JOIN b WHERE b.x is NULL • E.g. list those tuples from the borrower table only for those borrowers who are not depositors: • SELECT DISTINCT borrower.*FROM borrower NATURAL LEFT OUTER JOIN depositorWHERE depositor.customer_name is NULLS

27. Review • A NATURAL JOIN uses common column headings, and it is not possible to attach an ON or USING clause • An INNER JOIN results in a table where each tuple is a combination of information from both argument tables • An OUTER JOIN is an INNER JOIN padded with additional tuples • The ON clause lists common attributes which must be equal on both sides of the join • The USING clause permits us to enforce equality between attributes having different names, e.g. ON a.x = b.y • It also permits us to use inequalities, e.g. ON a.x > b.y

28. PS: Theta Join A ⋈C B ≝C (A ⋈ B) • For notational convenience, we permit ourselves to write a select condition on the join operator • This is called “theta join” because the condition is sometimes represented as a theta subscript:⋈θ

29. READING MySQL manual chapter 3: Tutorial Introduction http://www.mysql.com/documentation/mysql/bychapter/manual_Tutorial.html