SQL III - 11

Card Set Information

Author:
dau108
ID:
145839
Filename:
SQL III - 11
Updated:
2012-04-05 12:55:23
Tags:
SQL III 11
Folders:

Description:
SQL III - 11
Show Answers:

Home > Flashcards > Print Preview

The flashcards below were created by user dau108 on FreezingBlue Flashcards. What would you like to do?


  1. Delving Deep With nested queries
    • 􏰀 Pulling data from multiple tables with a single SQL statement
    • 􏰀 Comparing a value from one table with a set of values from another table
    • 􏰀 Using the SELECT statement to compare a value from a table with a value from another
    • table



    • 􏰀 Comparing a value from one table with all the corresponding values in another table
    • 􏰀 Making queries that correlate two corresponding rows in tables
    • 􏰀 Determining which rows to update, delete, or insert by using a subquery
  2. subqueries
    Subqueries are invariably SELECT statements, but the outermost enclosing statement may also be an INSERT, UPDATE, or DELETE

    SELECT D.Deptno, D.Name, E.Name, E.Age

    • FROM DEPT D, EMPLOYEE E
    • WHERE D.ManagerID = E.ID AND E.Age > 50 ;

    Next, suppose that you’re interested in the same set of rows but you want only the columns from the DEPT table. In other words, you’re interested in the departments whose managers are 50 or older, but you don’t care who those managers are or exactly how old they are. You could then write the query with a subquery rather than a join:

    • SELECT D.Deptno, D.Name
    • FROM DEPT D
    • WHERE EXISTS (SELECT * FROM EMPLOYEE E

    WHERE E.ID = D.ManagerID AND E.Age > 50) ;
  3. subqueries introduced by the keyword IN
    • SELECT column_list
    • FROM table
    • WHERE expression IN (subquery) ;

    • SELECT Model
    • FROM COMP_USED
    • WHERE CompID IN
    • (SELECT CompID
    • FROM COMPONENT
    • WHERE CompType = ‘Monitor’) ;
  4. subqueries introduced by the keywords NOT IN
    • SELECT Model
    • FROM COMP_USED
    • WHERE Model NOT IN
    • (SELECT Model
    • FROM COMP_USED
    • WHERE CompID IN
    • (SELECT CompID
    • FROM COMPONENT
    • WHERE CompType = ‘Monitor’)) ;
  5. Nested queries that return a single value
    • Introducing a subquery with one of the six comparison operators (=, <>, <,<=, >, >=) is often useful. In such a case, the expression preceding the operator evaluates to a single value, and the subquery following the operator must
    • also evaluate to a single value. An exception is the case of the quantified comparison operator, which is a comparison operator followed by a quantifier (ANY, SOME, or ALL).



  6. the ALL, SOME, and ANY quantifiers
  7. nested queries that are an existence test:

    EXIST
    NOT EXIST
    • EXIST

    • The CustID column links the CONTACT table to the CUSTOMER table. SQL looks at the first record in the CONTACT table, finds the row in the CUSTOMER
    • table that has the same CustID, and checks that row’s CustState field. If CUSTOMER.CustState = ‘CA’, then the current CONTACT row is added to the result table. The next CONTACT record is then processed in the same way,
    • and so on, until the entire CONTACT table has been processed. Because the query specifies SELECT * FROM CONTACT, all the contacttable’s fields are
    • returned, including the contact’s name and phone number.

    • NON EXIST
  8. correlated subqueries introduced with IN
  9. subqueries introduce with comparison operators


  10. subqueries in a HAVING clause
    • You can have a correlated subquery in a HAVING clause just as you can in a WHERE clause. As I mention in Chapter 9, a HAVING clause is usually preceded by a GROUP BYclause. The HAVING clause acts as a filter to restrict the groups created by the GROUP BYclause. Groups that don’t satisfy the condition of the HAVING clause are not included in the result. When used in
    • this way, the HAVING clause is evaluated for each group created by the GROUP BYclause.

    • In the absence of a GROUP BYclause, the HAVING clause is evaluated for the set of rows passed by the WHERE clause, which is considered to be a single
    • group. If neither a WHERE clause nor a GROUP BYclause is present, the HAVING clause is evaluated for the entire table:

  11. UPDATE, DELETE and INSERT statements




What would you like to do?

Home > Flashcards > Print Preview