SQL III - 11

The flashcards below were created by user dau108 on FreezingBlue Flashcards.

  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

    • 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

    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
    • WHERE CompID IN
    • (SELECT CompID
    • WHERE CompType = ‘Monitor’) ;
  4. subqueries introduced by the keywords NOT IN
    • SELECT Model
    • WHERE Model NOT IN
    • (SELECT Model
    • WHERE CompID IN
    • (SELECT CompID
    • 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).

    Image Upload 1

    • Image Upload 2
    • Image Upload 3
  6. the ALL, SOME, and ANY quantifiers
    Image Upload 4
  7. nested queries that are an existence test:

    • EXIST
    • Image Upload 5

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

    • Image Upload 6
  8. correlated subqueries introduced with IN
    Image Upload 7
  9. subqueries introduce with comparison operators
    Image Upload 8

    Image Upload 9
  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:

    Image Upload 10
  11. UPDATE, DELETE and INSERT statements
    Image Upload 11

    Image Upload 12

    Image Upload 13
Card Set
SQL III - 11
SQL III - 11
Show Answers