Docstoc

STQ Acsu Buffalo

Document Sample
STQ Acsu Buffalo Powered By Docstoc
					Single Table Queries




                       1
             Objectives


 SELECT, WHERE
 AND / OR / NOT conditions
 Computed columns
 LIKE, IN, BETWEEN operators
 ORDER BY, GROUP BY, HAVING
 SUM, COUNT, AVG, MIN, MAX, DISTINCT
  functions
 Subqueries and Nulls


                                        2
                      Simple Queries


 Query - a question represented in a way that the DBMS can
  understand
 SELECT clause
      Followed by the columns to be included in the query.
 FROM clause
      followed by name of the table that contains the data to query
 WHERE clause (optional)
      followed by conditions that apply to the data to be retrieved




                                                                       3
                             Note


 Generally SQL is not case sensitive
 Exception:
      Values within quotation marks
          Use the correct case for these values.
 Example:
      WHERE LAST ‘adams’ will not select any rows if the
       stored value is “Adams”



                                                            4
             Compound Conditions


Formed by connecting two or more simple conditions
 Uses AND, OR, and NOT operators
     AND: all conditions must be true
     OR: any one of conditions is true
     NOT: reverses the truth of the original condition




                                                          5
       AND / OR / NOT Conditions


 List the description of every part that is in
  warehouse number 3 and that has more than 100
  units on hand
 List the description of every part that is in
  warehouse number 3 or that has more than 100
  units on hand
 List the description of every part that is not in
  warehouse number 3


                                                      6
                Use of BETWEEN


 BETWEEN operator
      Not an essential feature
      Can arrive at same answer without it using AND
      Does make certain SELECT commands simpler


 List the customer number, last name, first name,
  and balance for every customer whose balance is
  between $500 and $1000


                                                        7
               Computed Columns


 Computed columns
      Do not exist in the database
      Can be computed using data in existing columns
      Use arithmetic operators
 Find the customer number, last name, first name,
  and available credit for every customer who has a
  credit limit of at least $1,500



                                                        8
                        Use of LIKE


 LIKE operator is used when exact matches will not work
 Use LIKE with a wildcard symbol
 Percent symbol (%)
      represents any collection of characters
      ‘%Pine%’
 Underscore (_)
      Represents any individual character
      ‘T_m’




                                                           9
                   Use of IN


 The IN clause provides concise way of phrasing
  certain conditions

 List the customer number, last name, and first name
  for every customer with a credit limit of $1,000,
  $1,500, or $2,000



                                                    10
             Use of ORDER BY


 Use the ORDER BY command to list data in a
  specific order
 The column on which data is to be sorted is called a
  sort key or simply key
 List the customer number, last name, first name,
  and balance of every customer
 Order the output in ascending (increasing) order by
  balance


                                                     11
          Sorting with Multiple Keys


 When sorting more than one column
      the more important column is called the major key (or
       primary sort key)
      the less important column is called the minor key (or
       secondary sort key)
 List keys in the order of importance in the ORDER
  BY clause
 Sort descending by using the DESC operator

                                                               12
        Sorting with Multiple Keys


 List the customer number, last name, first name,
  and credit limit of every customer, ordered by credit
  liming in descending order and by last name within
  credit limit
 The output should be sorted by credit limit in
  descending order
 Sort the output by last name within each group of
  customers with the same credit limit


                                                     13
                Using Functions


 SQL has functions to calculate
      Sums
      Averages
      Counts
      Maximum values
      Minimum values




                                   14
         Use of COUNT Function


 Counts the number of rows in a table
 The specific row to be counted is not important
  because each count should provide the same answer
 Most implementations of SQL allow the use of the
  asterisk (*) to represent any column




                                                  15
           Use of the SUM Function


   The SUM function is used to calculate totals
   The column to be totaled must be specified
   The column to be totaled must be numeric
   Find the number of customers and the total of their
    balance




                                                          16
        Using AVG, MAX, and MIN


 AVG, MAX and MIN functions are similar to the
  SUM
 SUM, AVG, MAX and MIN functions ignore
  (eliminate) null values
     Null values can cause strange results when calculated




                                                              17
             Use of DISTINCT


 DISTINCT operator is not a function
 Useful when used in conjunction with COUNT
  function
 Find the customer number of every customer who
  currently has an open order (List each customer
  only once)
 Count the number of customers who currently have
  open orders


                                                 18
               Nesting Queries


 Sometimes obtaining the results you need is a two-
  step process (or more).
 What is the largest credit limit given to any
  customer of sales rep 06?
 Display the customer number, last name, and first
  name of every customer in the Premiere Products
  database who has the above determined credit limit


                                                   19
                    Subqueries


 It is possible to place one query inside another
 Inner query is called a subquery and it is evaluated first
 Outer query can use the results of the subquery to find its
  results
 Rewrite the previous query in one step
 List the customer number, first name, last name, and balance
  for every customer whose balance is greater than the average
  balance




                                                            20
                    Note


 SQL will not allow the use of the condition
  BALANCE > AVG(BALANCE) in the WHERE
  clause
 A subquery must be used to obtain the average
  balance




                                                  21
                 Grouping


 Grouping creates groups of rows that share some
  common characteristics
 Calculations are performed for the entire group
 GROUP BY command allows data to be grouped in
  a particular order
 Statistics are calculated on the groups




                                                22
          HAVING vs. WHERE


 WHERE clause limits rows
 HAVING clause limits groups

 List each credit limit and the total number of
  customers of sales rep 03 who have this limit
 Repeat above, but list only those credit limits
  held by more than one customer


                                                23
               Null Values


 List the customer number, last name, and
  first name of every customer whose street
  value is null (unknown)




                                              24
                      Summary


 The basic form of a SQL command is SELECT-FROM.
  Specify the columns to be listed after the word SELECT (or
  type * to select all columns), and then specify the table name
  that contains these columns after the word FROM.
  Optionally, you can include conditions after the word
  WHERE
 Simple conditions are written in the form: column name,
  comparison operator, column name or value. Simple
  conditions can involve any of the comparison operators: =,
  >, >=, <, <=, or <> or != (not equal to).


                                                              25
                      Summary


 You can form compound conditions by combining in simple
  conditions, using the operators AND, OR, or NOT.
 Use the BETWEEN operator to indicate a range of values in
  a condition
 Use computed columns in SQL commands by using
  arithmetic operators and writing the computation in place of
  a column name




                                                             26
                       Summary


 To check for a value in a character column that is similar to a
  particular string of characters, use the LIKE clause
    The % wildcard represents any collection of characters

    The _ wildcard represents any single character

 To check whether a column contains one of a particular set of
  values, use the IN clause
 Use the ORDER BY clause to sort data
    List sort keys in order of importance

    To sort in descending order, follow the sort key with DESC




                                                                    27
                         Summary


 SQL contains the functions COUNT, SUM, AVG, MAX,
  AND MIN
 To avoid duplicates, either when listing or counting values,
  precede the column name with the DISTINCT operator
 When one SQL query is placed inside another, it is called a
  nested query
      The inner query, called a subquery, is evaluated first.
 Use the GROUP BY clause to group data



                                                                 28
                    Summary


 Use the HAVING clause to restrict the output to certain
  groups
 Use the phrase IS NULL in the WHERE clause to find rows
  containing a null value in some column




                                                        29

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:0
posted:4/20/2013
language:Latin
pages:29