nested subqueries in sql l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Nested Subqueries in SQL PowerPoint Presentation
Download Presentation
Nested Subqueries in SQL

Loading in 2 Seconds...

play fullscreen
1 / 12
tate

Nested Subqueries in SQL - PowerPoint PPT Presentation

145 Views
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. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

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