1 / 11

SQL -- SubSelects

SQL -- SubSelects. SQL -- SubSelects. SQL -- SubSelects. SQL -- SubSelects. SQL -- SubSelects. Data Manipulation includes hot topic -- sub-selects !. Sub-selects or subqueries as they are sometimes called: - SQL expressions that begin with the SELECT

jaden
Télécharger la présentation

SQL -- SubSelects

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

  2. SQL -- SubSelects Data Manipulation includes hot topic -- sub-selects! Sub-selects or subqueries as they are sometimes called: - SQL expressions that begin with the SELECT -- Subselects give you the capability to nest a SELECT statement within WHERE or HAVING clauses. Outer select -- refers to the SELECT statement that is executing a nested SELECT statement. Inner select -- refers to the nested SELECT statement is the inner SELECT statement.

  3. SQL -- SubSelects Sub-queries -- provide the ability to build a list of values dynamically and to perform a test against that list. Code example: SELECT CATLG#, CATDSC FROM CATALOG WHERE YR1$ < (SELECT AVG(YR1$) FROM CATALOG) When building the subselect you can use only the following clauses: SELECT Clause FROM Clause WHERE Clause GROUP BY Clause HAVING Clause

  4. SQL -- SubSelects When you code a subselect using a relational operator, you must ensure that the result is a single record. Expression {relational operator} (Sub-SELECT) You must be comparing like values -- numeric expression subselect must provide a numeric value. Quantified Relational Testing Quantified relational test lets you perform a relational operator test over a list of values. Expression {relational operator} [SOME | ANY | ALL] (Sub-SELECT)

  5. SQL -- SubSelects ALLoperator -- determines if the condition is true for all the values in the list. Example: you use the Greater than operator and the subselect builds a list with 10 values, the “>” test must be true for every value in the list. SOMEoperator -- determines if the condition is true for some of the values in the list. Test is true for a least one value in the list. Anyoperator -- same as the SOME operator. EXISTSOperator -- test to see whether any records exist in a subselect. If any rows were selected by the subselect, the condition is true. If you use the NOT keyword the test is true if no rows were selected by the subselect. [NOT] EXISTS (Sub-SELECT)

  6. SQL -- SubSelects List of Books that have the same publisher as 'Shyness‘? Write the code to find the answer to above question! Let's see? One way would be: select pub_code from book where book_title = 'Shyness' The above query retrieves: BB The next SELECT statement would be: SELECT book_title, pub_code FROM book WHERE pub_code = 'BB'

  7. SQL -- SubSelects List of Books that have the same publisher as 'Shyness‘? Write the code to find the answer to above question! Let's see? Another method would be with a SubQuery/SubSelect SELECT book_title, pub_code FROM book WHERE pub_code = (select pub_code from book where book_title = 'Shyness' ) The subquery retrieves: BB In which the outer query uses!

  8. SQL -- SubSelects Let suppose person does not know the name of the book? But knows it start with the letter S? This query statement will not work!! Let's see? This query statement will not work! How come? SELECT book_title, pub_code FROM book WHERE pub_code = (select pub_code from book where book_title like 'S%' ) The subquery retrieves: BB & PB In which the outer query is looking for one value!

  9. SQL -- SubSelects Let suppose person does not know the name of the book? But knows it start with the letter S? This query statement will not work!! Let's see? How could we re-write to get results? SELECT book_title, pub_code FROM book WHERE pub_code IN (select pub_code from book where book_title like 'S%' ) The subquery retrieves: BB & PB IN will do it!!

  10. SQL -- SubSelects select distinct(book_title), units_on_hand from book b, invent i where b.book_code = i.book_code and units_on_hand >= (select avg(units_on_Hand) from invent) What does this query give you? Gives list of book titles and unit on hand providing the on hand units is greater than or equal to the avg units on hand in inventory.

  11. Select distinct(name) from president order by name Joining a table to itself! Did we have a president that served more than one term non-constitutively (not back to back terms in office)? Or Do we have a president that is listed in our table multiple times? select p1.name, p1.noorder from president p1, president p2 where p1.name = p2.name and p1.noorder < p2.noorder Grover Cleveland

More Related