SQL III - 9

Card Set Information

Author:
dau108
ID:
145747
Filename:
SQL III - 9
Updated:
2012-04-04 16:50:43
Tags:
SQL III
Folders:

Description:
SQL III - 9
Show Answers:

Home > Flashcards > Print Preview

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


  1. Zeroing ln on the data you want
    • 􏰀 Specifying the tables you want to work with
    • 􏰀 Separating rows of interest from the rest
    • 􏰀 Building effective WHERE clauses
    • 􏰀 Handling null values
    • 􏰀 Building compound expressions with logical connectives
    • 􏰀 Grouping query output by column
    • 􏰀 Putting query output in order
  2. moifying clauses

    SELECT column_list
    FROM table_list
    [WHERE search_condition]
    [GROUP BY grouping_column]
    [HAVING search_condition]
    [ORDER BY ordering_condition]


    • 􏰁 The WHERE clause is a filter that passes rows that meet the search condi-
    • tion and rejects rows that don’t meet the condition.
    • 􏰁TheGROUP BYclauserearrangestherowsthattheWHEREclausepasses
    • according to the value of the grouping column.
    • 􏰁 The HAVING clause is another filter that takes each group that the
    • GROUP BYclauseformsandpassesthosegroupsthatmeetthesearch
    • condition, rejecting the rest.
    • 􏰁TheORDER BYclausesortswhateverremainsafterallthepreceding
    • clauses process the table.
  3. WHERE clauses
  4. The conditions that these WHERE clauses express are known as predicates. A predicate is an expression that asserts a fact about values.
    • 􏰁 Comparison predicates
    • 􏰁 BETWEEN
    • 􏰁 IN [NOT IN]
    • 􏰁 LIKE [NOT LIKE]
    • 􏰁 NULL
    • 􏰁 ALL, SOME, ANY
    • 􏰁 EXISTS
    • 􏰁 UNIQUE
    • 􏰁 OVERLAPS
    • 􏰁 MATCH
    • 􏰁 SIMILAR
    • 􏰁 DISTINCT
  5. IN and NOT IN


    • SELECT Company, Phone
    • FROM SUPPLIER
    • WHERE State NOT IN (‘CA’, ‘AZ’, ‘NM’) ;
  6. LIKE and NOT LIKE
    • To identify partial matches, SQL uses two wildcard characters. The percent sign (%) can stand for any string of characters that have zero or more characters. The underscore (_) stands for any single character. Table 9-3 provides
    • some examples that show how to use LIKE.








    WHERE Phone NOT LIKE ‘503%’

    This example returns all the rows in the table for which the phone number starts with something other than 503.
  7. others predicates:

    SIMILAR
    NULL
    ALL, SOME, ANY
    EXISTS
    UNIQUE
    DISTINCT
    OVERLAPS
    MATCH
  8. GROUP BY clauses
    • SELECT Salesperson, AVG(TotalSale)
    • FROM SALES
    • GROUP BY Salesperson;






    • SELECT Salesperson, SUM(TotalSale)
    • FROM SALES
    • GROUP BY Salesperson;
  9. HAVING clauses
    You can exclude Ferguson’s sales from the grouped data by using a HAVING clause as follows:

    • SELECT Salesperson, SUM(TotalSale)
    • FROM SALES
    • GROUP BY Salesperson
    • HAVING Salesperson <> ‘Ferguson’;
  10. ORDER BY clauses
    SELECT * FROM SALES ORDER BY SaleDate ;

    • All these ordering examples are in ascending (ASC) order, which is the default sort order. The last SELECT shows earlier sales first and, within a given date, shows sales for ‘Adams’ before ‘Baker’. If you prefer descending (DESC)
    • order, you can specify this order for one or more of the order columns, as follows:

    • SELECT * FROM SALES
    • ORDER BY SaleDate DESC, Salesperson ASC ;

What would you like to do?

Home > Flashcards > Print Preview