1 / 29

International Computer Institute, Izmir, Turkey SQL

This website provides information on types of joins in SQL, join conditions, and examples with SQL queries and MySQL. It also includes a schema diagram for the banking enterprise joins.

campbellt
Download Presentation

International Computer Institute, Izmir, Turkey SQL

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. 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

More Related