Chapter 3 Single Table Queries

1
Chapter 3Single Table Queries
2
Objectives

• Retrieve data from a database using SQL commands
• Use compound conditions
• Use computed columns
• Use the SQL LIKE operator
• Use the SQL IN operator

3
Objectives

• Sort data using the ORDER BY command
• Sort data using multiple keys and in ascending
  and descending order
• Use SQL functions
• Use nested subqueries
• Group data using the GROUP BY command

4
Objectives

• Select individual groups using the HAVING clause
• Retrieve columns with null values

5
Simple Queries

• Query - a question represented in a way that the
  DBMS can understand
• Basic format
• SELECT-FROM
• Optional
• SELCT-FROM-WHERE

6
SELECT Command

• 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

7
SELECT Command

• There are no special formatting rules for SQL
• FROM command and WHERE command appear on separate
  lines for readability and understanding only

8
Example 1

• List the customer number, last name, and balance
  of every customer

9
SELECT Command to Select Certain Columns
10
Example 2

• List the complete PART table

11
SELECT Command to Select All Customers
12
Use of the WHERE Clause Simple Conditions

• A simple condition has the form
• column name, comparison operator, either another
  column name or a value

13
Example 3

• What is the name of customer number 124?

14
SELECT Command With a Condition
15
Comparison Operators
16
Example 4

• Find the customer number for every customer whose
  last name is Adams

17
SELECT Command With a Condition That Retrieves
Multiple Rows
18
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

19
Example 5

• Find the customer number, last name, first name,
  and current balance for every customer whose
  balance exceeds the credit limit

20
SELECT Command Involving a Comparison
21
Compound Conditions

• 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

22
Example 6

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

23
SELECT Command Involving an AND Condition
24
SELECT Command With WHERE Clause and AND
Condition on a Single Line
25
Example 7

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

26
SELECT Command Involving an OR Condition
27
Example 8

• List the description of every part that is not in
  warehouse number 3

28
SELECT Command Involving a NOT Condition
29
Example 9

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

30
SELECT Command Involving an AND Condition on a
Single Column
31
Use of BETWEEN

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

32
SELECT Command Involving a BETWEEN Condition
33
Computed Columns

• Computed columns
• Do not exist in the database
• Can be computed using data in existing columns
• Use arithmetic operators

34
Arithmetic Operators
35
Example 10

• Find the customer number, last name, first name,
  and available credit for every customer who has a
  credit limit of at least 1,500

36
SELECT Column Involving a Computed Column
37
Example 11

• Find the customer number, last name, first name,
  and available credit for every customer who has
  at least 1,000 of available credit

38
SELECT Command With a Computation in the Condition
39
Use of LIKE

• LIKE operator is used when exact matches will not
  work
• Use LIKE with a wildcard symbol

40
Example 12

• List the customer number, last name, first name,
  and complete address of every customer who lives
  on Pine that is, whose address contains the
  letters Pine

41
SELECT Command With Wildcards
42
Wildcard Symbols

• Percent symbol ()
• represents any collection of characters
• Pine
• Underscore (_)
• Represents any individual character
• T_m

43
Use of IN

• The IN clause provides concise way of phrasing
  certain conditions

44
Example 13

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

45
SELECT Command Involving an IN Condition
46
Sorting

• Generally, the order of rows is immaterial to the
  DBMS
• There is no defined order in which results are
  displayed
• Rows can displayed in the order in which they
  were entered

47
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

48
Example 14

• List the customer number, last name, first name,
  and balance of every customer
• Order the output in ascending (increasing) order
  by balance

49
SELECT Command to Order Rows
50
Sorting with Multiple Keys in Descending Order

• 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

51
Example 15

• 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

52
SELECT Command With Multiple Sort Keys
53
Using Functions

• SQL has functions to calculate
• Sums
• Averages
• Counts
• Maximum values
• Minimum values

54
SQL Functions
55
Use of COUNT Function

• 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

56
Example 16

• How many parts are in item class HW?

57
SELECT Command to Count Rows
58
Count without the Asterisk

• SELECT COUNT(PART_NUMBER)
• FROM PART
• WHERE ITEM_CLASS HW

59
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

60
Example 17

• Find the number of customers and the total of
  their balance

61
SELECT Command to Calculate a COUNT and a SUM
62
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

63
SELECT Command With Several Functions
64
Use of DISTINCT

• DISTINCT operator is not a function
• Useful when used in conjunction with COUNT
  function

65
Example 18

• Find the customer number of every customer who
  currently has an open order (that is, an order
  currently in the ORDERS table).

66
Results With Repeated Customer Numbers
67
Example 19

• Find the customer number of every customer who
  currently has an open order
• List each customer only once

68
Results Without Repeated Customer Numbers
69
Example 20

• Count the number of customers who currently have
  open orders

70
Count That Includes Repeated Customer Numbers
71
Count Without Repeated Customer Numbers
72
Nesting Queries

• Sometimes obtaining the results you need is a
  two-step process (or more).

73
Example 21

• What is the largest credit limit given to any
  customer of sales rep 06?

74
Selecting the Maximum Credit Limit
75
Example 22

• Display the customer number, last name, and first
  name of every customer in the Premiere Products
  database who has the credit limit found in
  Example 21

76
Query Using Previous Result
77
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

78
Example 23

• Find the answer to Examples 21 and 22 in one step

79
Using IN and a Subquery
80
Query Using an EQUAL Condition and a Subquery
81
Example 24

• List the customer number, first name, last name,
  and balance for every customer whose balance is
  greater than the average balance

82
Query Using Greater Than Operator and a Subquery
83
Note

• SQL will not allow the use of the condition
  BALANCE gt AVG(BALANCE) in the WHERE clause
• A subquery must be used to obtain the average
  balance
• Results of the subquery can be used in the
  condition as shown in Figure 3.29

84
Grouping

• Grouping creates groups of rows that share some
  common characteristics
• Calculations are performed for the entire group
• Use the GROUP BY command

85
Using GROUP BY

• GROUP BY command allows data to be grouped in a
  particular order
• Statistics are calculated on the groups

86
Example 25

• List the total for each order

87
Grouping Column
88
HAVING

• HAVING command is used for groups

89
Example 26

• List the total for those orders over 200

90
Query Using a HAVING Clause
91
HAVING vs. WHERE

• WHERE clause limits rows
• HAVING clause limits groups

92
Example 27

• List each credit limit and the number of
  customers having each credit limit

93
Counting the Rows in a Group
94
Example 28

• Repeat Example 27, but list only those credit
  limits held by more than one customer

95
Displaying Groups That Contain More Than One Row
96
Example 29

• List each credit limit and the total number of
  customers of sales rep 03 who have this limit

97
Restricting the Rows to Be Grouped
98
Example 30

• Repeat Example 29, but list only those credit
  limits held by more than one customer

99
Restricting the Rows and Groups
100
Example 31

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

101
Selecting Rows Containing Null Values
102
SQL Query Clauses and Operators
103
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 , gt, gt, lt, lt, or ltgt or
  ! (not equal to).

104
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

105
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

106
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

107
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