Session 18

How to use SQL

to work with a database
Objectives
• How to select data from a single table

• How to select data from multiple tables

• How to code summary queries

• How to code sub queries

• How to insert, update, and delete rows

How to select data
from a single table
How to select columns from a
table
• The simplified syntax of the SELECT statement

• Retrieve all rows and columns from a table

How to select columns from a
table (cont.)
• Retrieve three columns and sort them by price
How to select columns from a
table (cont.)
• Retrieve rows in the specified price range

• Retrieve an empty result set

How to use an alias for a columns
• By default, a column in the result set is given
the same name as the column in the base table.
• You can specify a column alias for the column.
• Use the AS keyword to specify an alias
How to use an alias for a
columns (cont.)
• Omit the AS keyword

• Use quotes to include spaces

How to select rows with a LIMIT
clause
• You can use the LIMIT clause to limit the
number of rows that are included in the result
set.
• The syntax of the LIMIT clause

• Retrieve the first three rows of the result set

How to select rows with a LIMIT
clause (cont.)
• Another way to retrieve the first three rows

• Retrieve three rows starting at the second row

How to select rows with a
WHERE clause
• If the result of a comparison results in a TRUE
value, the row being tested is included in the
result set. If it’s FALSE or unknown, the row isn’t
included.
• The syntax of the WHERE clause with
comparison operators
How to select rows with a
WHERE clause (cont.)
• The comparison operators
How to select rows with a
WHERE clause (cont.)
• A WHERE clause that selects products where the
product…
How to use the logical operators
• Logical operators to create compound conditions
that consist of two or more conditions.
• The syntax of the WHERE clause with logical
operators

• A search condition that uses the AND operator

• A Search condition that uses the OR operator

How to use the logical operators
(cont.)
• A search condition that uses the NOT operator

• The same condition rephrased to eliminate the

NOT operator

• A compound condition without parentheses

How to use the logical operators
(cont.)

• The same compound condition with parentheses

How to use the IS NULL operator
• If the database allows NULL values in a column,
you can use the IS NULL operator to select rows
that have or don’t have NULL values in that
column
• The syntax of the WHERE clause with the IS
NULL operator
How to use the IS NULL
operator (cont. )
• Retrieve all rows

• Retrieve rows for orders that haven’t been

shipped
How to use the IS NULL
operator (cont.)
• Retrieve rows for orders that have been shipped
How to use the LIKE operator
• Use the LIKE operator to retrieve rows that
match a string pattern, called a mask.
• The syntax of the WHERE clause with the LIKE
operator

• Wildcard symbols
How to use the LIKE operator
(cont.)
• WHERE clause that use the LIKE operator
How to sort rows with an
ORDER BY clause
• The ORDER BY clause specifies how you want
the rows in the result set sorted.
• The syntax of the ORDER BY clause

• Sort by one column in ascending sequence

How to sort rows with an
ORDER BY clause (cont.)
• Sort by one column in descending sequence
How to sort rows with an
ORDER BY clause (cont.)
• Sort by two columns
 How to select data
from multiple tables
How to code an inner join
• A join is used combine columns from two or
more tables into a result set based on the join
conditions you specify.
• The explicit syntax for an inner join
 How to code an inner join
(cont.)
• A SELECT statement that joins the customers
and orders tables
When and how to use table
aliases
• A table alias is an alternative table name
assigned in the FROM clause
• The syntax for an inner join that uses table
aliases
When and how to use table
aliases (cont.)
• An inner join with aliases for all tables
When and how to use table
aliases (cont.)
• An inner join with aliases for four tables
How to code summary queries
How to code aggregate functions
• Aggregate functions perform calculation on the
values in a set of selected rows.
• The syntax of the aggregate functions
How to code aggregate
functions (cont.)
• Count all products

• Count all orders and orders that have been

shapped
How to code aggregate
functions (cont.)
• Find lowest, highest, and average prices

• Get the total of the calculated values for all

orders
How to group queries by column
• The GROUP BY clause groups rows of a result
set based on one or more columns or
expressions.
• The HAVING clause specifies a search condition
for a group or an aggregate.
• The syntax of the GROUP BY and HAVING
clauses
How to group queries by column
(cont.)
• Calculate the average list price by category
How to group queries by column
(cont.)
• Use columns from multiple tables
How to group queries by column
(cont.)
• Use a WHERE clause to filter rows before
grouping them
How to code subqueries
Where to use subqueries
• A subqueries is a SELECT statement that’s coded
within another SQL statement.
• A subquery can return a single value, a column
that contains multiple values, or multiple
columns that contain multiple values
• Four ways to introduce a subquery in a SELECT
statement
– In a WHERE clause as a search condition
– In a HAVING clause as a search condition
– In the FROM clause as a table specification
– In the SELECT clause as a column specification
Where to use subqueries (cont.)
• Use a subquery in the WHERE clause
Where to use subqueries (cont.)
• Use another subquery in the WHERE
clause
How to code correlated
subqueries
• A correlated subquery is a subquery that is
executed once for each row processed by
the outer query
• Use a correlated subquery in the SELECT
clause
How to code correlated
subqueries (cont.)
• The syntax of a subquery that uses the EXISTS
operator

• Get all categories that don’t have any products

How to insert, update, and delete rows
How to insert rows
• You use the INSERT statement to add new row
to a table
• The syntax of the INSERT statement
How to insert rows (cont.)
• The table definition

• Add a single row without using a column list

How to insert rows (cont.)
• Add a single row using a column list

• Add multiple rows

How to update rows
• You use the UPDATE statement to modify one or
more rows in the table named in the UPDATE
clause
• The syntax of the UPDATE statement

• Update one column of one row

How to update rows (cont.)
• Update multiple columns of one row

• Update one column of multiple rows

How to update rows (cont.)
• Update one column of all rows in the table

• Use a subquery to update multiple rows

How to delete rows
• You can use the DELETE statement to delete
one or more rows from the table you name in
the DELETE clause
• The syntax of the DELETE statement

• Delete one row

How to delete rows (cont.)
• Delete multiple rows

• Another way to delete multiple rows

• Use subquery to delete all order items for a

customer
Summary
• You can specify a column alias for the column
• Use the AND and OR logical operators to create
compound conditions that consist of two or
more conditions
• Use LIKE operator to retrieve rows that match a
string pattern, called a mask.
• A join is used to combine columns from two or
more tables into a result set based on the join
conditions you specify.
• A aggregate function performs a calculation on
the values in a set of selected rows
Summary (2)
• A subquery is a SELECT statement that’s coded
within another SQL statement.
• A correlated subquery is a subquery that is
executed once for each row processed by the
outer query.
Discussion