Programming Languages Chapter 4 Guide Sql Ninth Edition Page Singletable Queries Glance Instructors Manual Table

Unlock access to all the studying documents.

View Full Document

A Guide to SQL, Ninth Edition Page 4–1

Chapter 4

Single-Table Queries

At a Glance

Instructor’s Manual Table of Contents

• Overview

• Objectives

• Teaching Tips

• Quick Quizzes

• Class Discussion Topics

• Additional Projects

• Additional Resources

• Key Terms

A Guide to SQL, Ninth Edition Page 4–2

Lecture Notes

Overview

In this chapter, students learn about the SQL SELECT command that is used to retrieve data in

a database. They examine ways to sort data and use SQL functions to count rows and calculate

totals. Students also learn how to nest SELECT commands by placing one SELECT command

inside another. Finally, students learn how to group rows that have matching values in some

column.

Chapter Objectives

In this chapter, students learn about:

• Retrieving data from a database using SQL commands

• Simple and compound conditions in queries

• The BETWEEN, LIKE, and IN operators in queries

• Computed columns in queries

• Sorting data using the ORDER BY clause

• Sorting data using multiple keys and in ascending and descending order

• Aggregate functions in queries

• Subqueries

• Grouping data using the GROUP BY clause

• Selecting individual groups of data using the HAVING clause

• Retrieving columns with null values

Teaching Tips

Constructing Simple Queries

1. Define query. A query is simply a question represented in a way that the DBMS can

understand.

3. Following the SELECT clause, you list the names of the columns to display in the

query result. After the FROM clause, you list the table or tables that contain the data to

4. Point out that there are no special formatting rules in SQL.

A Guide to SQL, Ninth Edition Page 4–3

Teaching

Tip

Point out that SQL is a data definition language, a data manipulation language,

and a data control language. In Chapter 3, students learned how to use the data

definition language. In this chapter, they learn how to use the data manipulation

language.

Remind students that they query databases all the time. Using the electronic

library catalog and online search engines are just two ways that we query

databases.

Review any guidelines for submitting assignments, such as creating, saving, and

printing commands and output before starting the chapter.

Point out that the SELECT command is one of the most fundamental and

powerful SQL commands. Most relational databases support some form of SQL.

Retrieving Certain Columns and All Rows

1. Use Example 1 and Figure 4-1 to illustrate retrieving certain columns and all rows. The

2. Point out the first Note on page 99 concerning Oracle Database Express Edition. You

can change the number of rows displayed in a result.

3. If your class is using Access or SQL Server, point out the second Note on page 99

Retrieving All Columns and All Rows

2. You can use an asterisk (*) to indicate that you want to display all columns. The query

Using a WHERE Clause

1. Use Examples 3 and 4 and Figures 4-3 and 4-5 to illustrate using a WHERE clause to

2. Define simple condition. A simple condition has the form: column name, comparison

operator, and either another column name or a value.

4. Use Example 5 and Figure 4-6 to illustrate using a WHERE clause to compare two

column names.

A Guide to SQL, Ninth Edition Page 4–4

Teaching

Tip

Text strings are compared character by character from left to right. If strings are of

different lengths, the shorter is conceptually padded to the right with blanks before the

comparison is made (i.e., character difference takes precedence over length difference).

The collating sequence for characters is an extended ASCII character set as defined by

ISO standard 8859-1. For more information on this standard, see

www.htmlhelp.com/reference/charset/.

Using Compound Conditions

1. Define compound conditions. Compound conditions are formed by connecting two or

2. When the AND operator connects simple conditions, all conditions must be true in

order for the compound condition to be true.

4. Preceding a condition by the NOT operator reverses the truth of the original condition.

6. Use Example 6 and Figures 4-7 and 4-8 to illustrate the AND operator.

8. Use Example 8 and Figure 4-10 to explain the NOT operator

Teaching

Tip

You can draw a Venn diagram to illustrate the Boolean operators. You also can

create a truth table.

Be aware that students sometimes have trouble understanding the NOT operator.

Using the BETWEEN Operator

1. Use Example 9 and Figures 4-11 and 4-12 to illustrate the BETWEEN operator. The

2. Point out that the command in Figure 4-11 is equivalent to the command in Figure 4-12.

4. Mention that the BETWEEN operator is inclusive, meaning that a value equal to either

end would be selected.

A Guide to SQL, Ninth Edition Page 4–5

Using Computed Columns

1. Define computed column. A computed column is a column that does not exist in the

3. Make sure that students understand the order of precedence for mathematical operators.

4. Use Examples 10 and 11 and Figures 4-14 through 4-16 to illustrate using computed

columns. Point out the use of the AS operator to name a computed column in Figure 4-

5. Review the Note on using spaces in computed column names on page 108.

Using the LIKE Operator

1. Explain that the LIKE operator is used in conditions that do not involve exact matches.

3. If you are using Access, use Figure 4-18 to point out that Access uses different wildcard

symbols.

Using the IN Operator

1. Explain that the IN operator provides a concise way of phrasing compound conditions.

3. Point out that the equivalent condition:

Quick Quiz 1

1. A(n) _____ is a question represented in a way that the DBMS can understand.

2. In SQL, the _____ command is used to query a database.

3. _____ conditions are formed by connecting two or more simple conditions.

4. The _____ operator lets you specify a range of values in a condition.

5. You can assign a name to a computed column by following the computation with the

word _____ and the desired name.

Sorting

1. In a relational database, the order of the rows is immaterial. If the order in which the

Using the ORDER BY Clause

1. Use the ORDER BY clause to list data in a specific order.

3. Point out that, if a sort order is not specified, the default is ascending.

Additional Sorting Options

1. When data is sorted on more than one column, the more important key is called the

3. Point out that the major sort key immediately follows the words ORDER BY.

4. To sort records in descending order, use the DESC operator following the name of the

sort key.

Teaching

Tip

Students often have problems sorting on multiple keys because they do not

understand the phrasing of sort questions. For example, in the phrase city within

state, the major sort key is state, which follows the word within.

Quick Quiz 2

1. To list data in a specific order, use the _____ clause.

2. To sort in descending order, follow the name of the sort key with the _____ operator.

A Guide to SQL, Ninth Edition Page 4–7

Using Functions

1. SQL includes special functions called aggregate functions to calculate sums, averages,

2. Review the SQL aggregate functions in Figure 4-22.

Using the COUNT Function

1. The COUNT function counts the number of rows in a table.

3. Point out that a column name or the asterisk must be included in parentheses following

the function.

4. Mention that you can use the COUNT function with both numeric and character data

types.

Using the SUM Function

2. Use Example 17 and Figure 4-24 to illustrate using both the SUM function and the

COUNT function.

Using the AVG, MAX, and MIN Functions

1. Use Example 18 and Figure 4-25 to point out that using the AVG, MAX, and MIN

functions is similar to using SUM.

3. Use the Note on page 114 to emphasize that null values are ignored when using the

SUM, AVG, MAX, or MIN functions.

4. Use the Note on page 115 to mention that you can use an AS clause with a function.

Using the DISTINCT Operator

1. The DISTINCT operator eliminates duplicate results in a query. It is often used when

you only want to know that a value is present, not how many times it occurs.

3. Figure 4-26 shows duplicate results. These duplicates will be eliminated when the

DISTINCT operator is included.

5. Use Example 21 and Figures 4-28 and 4-29 to illustrate the relationship between

COUNT and DISTINCT.

6. Use the embedded Q&A on page 117 to make sure students understand when to use the

DISTINCT operator.

Teaching

Tip

An aggregate function is different from a calculated field. A calculated field is a

mathematical operation performed on individual records. An aggregate function

is performed on groups of records.

A Guide to SQL, Ninth Edition Page 4–8

Quick Quiz 3

1. To determine how many parts are in item class SG, use the _____ function.

2. To determine the total of all customer balances, use the _____ function.

3. To calculate the smallest value in a column, use the _____ function.

Nesting Queries

Subqueries

2. Use Example 24 and Figure 4-32 to illustrate the use of a nested query to replace the

two queries shown in Examples 22 and 23.

4. Use the Note on page 120 to further illustrate the use of nested queries.

A Guide to SQL, Ninth Edition Page 4–9

Grouping

1. Define Grouping. Grouping is the process of creating groups (or collections) of records

that share some common characteristic. The number of groups depends on the number

Using the GROUP BY Clause

1. The GROUP BY clause groups records on a particular column, and the ORDER BY

clause orders records in the desired order.

3. Use the embedded Q&As on pages 11 and 122 to make sure students understand

grouping.

Using a HAVING Clause

1. To add a restriction to a group, you use the HAVING clause.

HAVING vs. WHERE

1. Just as the WHERE clause is used to limit the rows that are included in a query result,

2. Use Examples 28 and 29 and Figures 4-36 and 4-37 to illustrate using the HAVING

4. Use Example 31 and Figure 4-39 to illustrate using both the HAVING and the WHERE

clauses.

NULLS

1. Sometimes a condition involves a column that can be null.

3. Note that the correct format is actually IS NULL. This finds all records where there are

Summary of SQL Clauses, Functions, and Operators

1. Review the SQL query clauses and operators in Figure 4-41.

A Guide to SQL, Ninth Edition Page 4–10

Class Discussion Topics

1. In Example 26 on page 121, the records are both grouped by and ordered by rep_num.

What would the results look like if you group by rep_num and order by average

balance?

2. The arithmetic operators on page 106 do not include exponentiation. Do you think you

can do exponentiation in a SELECT statement? How would you do it?

3. Sorting means ordering records in a particular way. When would it be useful to sort data

in ascending order? Why? When would it be useful to sort data in descending order?

Why?

Additional Projects

1. Place students in teams and assign them some of the TAL Distributors exercises to code

2. Place the students in teams. Assign each team a different DBMS, for example, Firebird,

Oracle, Access, PostgreSQL, SQL Server. Have the teams research the DBMSs to

Additional Resources

1. Concepts of Database Management, Eighth Edition by Philip Pratt and Mary Last

Key Terms

➢ aggregate function: Special SQL functions that apply to groups of rows and are used to

calculate sums, averages, counts, maximum values, and minimum values

➢ AND: operator that connects simple conditions and requires all conditions to be true for the

compound condition to be true

➢ BETWEEN: Operator that specifies a range of values in a condition

➢ compound condition: A condition formed by connecting two or more simple conditions

➢ computed column: A column that does not exist in the database but can be computed using

data in existing columns

A Guide to SQL, Ninth Edition Page 4–11

except for use as permitted in a license distributed with a certain product or service or otherwise on a password–

protected website for classroom use.

➢ COUNT: Function that counts the number of rows in a table

➢ DESC: Operator included in an ORDER BY clause when results are to be sorted in

descending order

➢ DISTINCT: Operator that eliminates duplicate values in the results of a query

➢ FROM clause: The clause that indicates the table from which to retrieve the specified

columns

➢ GROUP BY clause: The clause that groups rows based on the specified column

➢ grouping: Creates groups of rows that share some common characteristic

➢ HAVING clause: The clause that limits a condition to the groups that are included

➢ major sort key: The more important column when data on two columns needs to be sorted

➢ MAX: Function that calculates the maximum value in a numeric range

➢ MIN: Function that calculates the minimum value in a numeric range

➢ minor sort key: The less important column when data on two columns needs to be sorted

➢ NOT: Operator that reverses the truth of the original condition

➢ NOT condition: A WHERE clause that uses the NOT operator to connect simple

➢ SELECT clause: The clause that specifies the columns to retrieve in the query

➢ simple condition: A condition that has the form: column name, comparison operator, and

either another column name or a value

➢ sort key: The column on which data is to be sorted when the ORDER BY clause is used

➢ subquery: The inner query in nested queries