1 / 12

Nested Subqueries in SQL

Nested Subqueries in SQL. By Kenneth Cheung CS 157A Section 2 Professor Lee. Terms. For a query, in - tests for set membership select - chooses values not in - absence of set membership. Example 1: Find all customers who have both a loan and an account at a bank.

tate
Download Presentation

Nested Subqueries in 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. Nested Subqueries in SQL By Kenneth Cheung CS 157A Section 2 Professor Lee

  2. Terms For a query, • in- tests for set membership • select- chooses values • not in - absence of set membership

  3. Example 1: Find all customers who have both a loan and an account at a bank. Start with the bank account • (selectcustomer_name fromdepositor)

  4. Example 1 Find the list of customers who also have a loan account. combine using the outer select. • selectdistinctcustomer_name fromborrower wherecustomer_namein (selectcustomer_name fromdepositor)

  5. Example 2: Find all customers who have a loan from the bank but not an account. • select distinctcustomer_name fromborrower wherecustomer_namenot in (selectcustomer_name fromdepositor)

  6. Example 2: Find the names of all customers who are not Smith nor Jones not in can also be used for enumerated sets. • selectdistinctcustomer_name fromborrower wherecustomer_namenot in (‘Smith’, ’Jones’)

  7. Example 3: Find the names of all branches that have assets greater than those of at least one in Brooklyn • Set comparison using comparing operators • Use the rename operator as to compare elements in the same table • select distinctT.branch_name frombranchasT, branchasS whereT.assets > S.assetsandS.branch_city = ‘Brooklyn’

  8. Example 4: Another way using the “some” operator some- at least one • selectbranch_name frombranch whereassets > some (selectassets frombranch wherebranch_city = ‘Brooklyn’)

  9. Example 5: Find all branches that have an asset value greater than that of each branch in Brooklyn. = some - the same as in <> all - the same as not in • select branch_name from branch where assets > all (select assets from branch where branch_city = ‘Brooklyn’)

  10. Example 6: Find the branch with the highest average balance. • SQL cannot compose of aggregate functions (function of a function), so it must be written in a certain way. • selectbranch_name fromaccount group bybranch_name having avg (balance) >= all (select avg (balance) fromaccount group bybranch_name)

  11. Example 7: find all customers who have both a loan and an account at the bank. exists - returns true if the argument in it is not empty • selectcustomer_name fromborrower where exists (select * fromdepositor wheredepositor.customer_name = borrower.customer_name)

  12. Example 8: not exists- superset operation. • To show that relation A contains relation B, use notexists (B except A)

More Related