SQL Programming. Student Workbook

(1)

(2)

Published by

i

t

courseware, Inc., 7245 South Havana Street, Suite 100, Englewood, CO 80112

Contributing Authors: Denise Geller, Rob Roselius, Danielle Waleri.

Editor: Rob Roselius, Rick Sussenbach.

Editorial Assistant: Jan Waleri

Special thanks to: Many instructors whose ideas and careful review have contributed to

the quality of this workbook, including Elizabeth Boss, Jennifer James, Roger Jones, Joe McGlynn, Jim McNally, and Kevin Smith, and the many students who have offered comments, suggestions, criticisms, and insights.

i

t

courseware, Inc. All rights reserved. No part of this book may be reproduced or utilized in any form or by any means, electronic or mechanical, including photo-copying, recording, or by an information storage retrieval system, without permission in writing from the publisher. Inquiries should be addressed to

i

t

courseware, Inc., 7245 South Havana Street, Suite 100, Englewood, CO 80112. (303) 302-5280.

All brand names, product names, trademarks, and registered trademarks are the property of their respective owners.

(3)

Chapter Objectives

Retrieve data from a table using the SQL SELECT statement.

Sort the output of a SELECT statement.

Filter the returned rows with a WHERE clause.

Locate fields which have not yet been populated.

Combine multiple search criteria in a WHERE clause using logical

operators.

Use subqueries to generate values for use in WHERE clause

conditions.

Use the IN and BETWEEN operators to match a column against

multiple values.

(10)

The SELECT statement may query one or many tables. The data that is retrieved may be thought of as a virtual table (i.e., one that exists in memory only until the query completes). This virtual table is also referred to as the query’s result set.

The SELECT statement can be used to retrieve data from any or all columns from a table. For example, the following SELECT will display a list of everything about every store in the database:

SELECT * FROM store;

The query below will list each state (once) in which BizWare has stores: SELECT DISTINCT state

FROM store;

This query will retrieve each store’s number and location. SELECT store_number, city, state FROM store;

Expressions within the SELECT clause are calculated for each row in the result set. The default column heading on the display will match the expression. The heading may be overridden with a column alias. A column alias is a word used to replace the default heading. The following will retrieve the original and sale prices of products:

SELECT description name, price orig_price,

price * .75 sales_price FROM product;

Oracle allows column aliases to contain spaces. You must use double quotes if the alias includes one or more spaces.

SELECT description AS name, price AS orig_price,

price * .75 AS "Sales Price" FROM product;

(11)

The SELECT Statement

SQL data retrievals are done using the SELECT statement.

Data retrievals are also called queries.

A SELECT statement may make use of several clauses, but

minimally must include the following two:

SELECT [DISTINCT] {item_list | *}

FROM table

The SELECT clause specifies which data values will be retrieved,

and will be followed by either an item_list or a *.

* represents all the columns in table.

item_list is a column, an expression, or a comma separated

list of any combination of these.

Each item in item_list will display to the screen under a

column heading of the same name, unless an alias is specified.

Use DISTINCT to eliminate duplicate rows from the displayed

results.

The FROM clause specifies a table, or comma separated list of

tables, from which to retrieve data.

(12)

To display each store’s number and location in descending order by state, and ascending order by city in each state, enter the following:

SELECT store_number, city, state FROM store

ORDER BY state DESC, city ASC;

The original and sale price of an item may be displayed in descending price order with the following:

SELECT description, price, price * .75

FROM product ORDER BY 3 DESC;

An equivalent, and usually more readable, form of the above makes use of column aliases: SELECT description Name, price Original_Price,

price * .75 sales_price FROM product

(13)

Creating Some Order

Data is not physically stored in a table in any specified order.

Unless the RDBMS supports certain types of indexes, records

are usually appended to the end of the table.

The SELECT statement’s ORDER BY clause is the only way to

enforce sequencing on the result set.

The ORDER BY clause may be included in the SELECT statement:

SELECT {item_list | *}

FROM table

[ORDER BY column [ASC | DESC],...]

column can either be the name or the numeric position of the

column in the item_list.

The result set is sorted by the first column name in the ORDER BY

clause.

If there are duplicate values in the first sort column, the sort is

repeated on the second column in the list, and so on.

The rows may be sorted in ascending (the default) or descending

order.

(14)

To display all information for each store with a store number less than 4, enter the following: SELECT *

FROM store

WHERE state < 4;

To display each store’s number, location, and phone in the state of Nevada, enter the following:

SELECT store_number, street, city, area_code, phone_number FROM store

(15)

Use the WHERE clause when only a subset of all the rows in a table is

required.

SELECT {item_list | *}

FROM

table

[WHERE

conditions]

The WHERE clause is evaluated once for each row in table.

conditions will be one or more expressions that will evaluate to

either true, false or neither (null).

If the WHERE clause evaluates to true, then the current row is

returned in the result set.

Operators that may be used in the WHERE clause include:

=

equal to

!=, ^=, <>

not equal to

>, <

greater than, less than

>=, <=

greater than or equal to, less than or equal to

Any character string values in conditions must be enclosed within

single quotes.

(16)

To display a sorted list of people who have middle initials, enter the following:

SELECT lastname, firstname, mi, area_code, phone_number FROM person

WHERE mi IS NOT NULL

(17)

NULL Values

A null is the absence of a value.

Null values are not equal to 0, blanks, or empty strings.

Compare null values with the IS operator.

SELECT *

FROM person

WHERE phone_number IS NULL;

The IS operator will result in either true or false.

To search for any non-null values, use IS NOT NULL.

SELECT *

FROM person

WHERE phone_number IS NOT NULL;

Any other operation with NULL yields a null value.

SELECT *

FROM person

WHERE phone_number = NULL;

This will never be true, even for rows with null values in the

phone_number column!

(18)

The query below will find all accounts having a minimum $1000 credit limit that have a non-zero balance:

SELECT account_number "ACCT NUMBER", account_name name,

credit_limit limit, balance

FROM account

WHERE credit_limit >= 1000 AND balance > 0;

When an employee enters data into the system, a field for which there is no data may be skipped or have a 0 entered. When checking on the status of a store’s inventory, the query below will display any products at each store that may be running low in inventory and haven’t yet been reordered:

SELECT product_id, store_number FROM inventory

WHERE quantity_on_hand < 20 AND (quantity_on_order = 0 OR quantity_on_order IS NULL);

(19)

Compound Expressions

Use logical operators to group conditions in a WHERE clause.

NOT will negate the condition following it.

AND will result in true if both conditions are true for a row.

OR will result in true if either condition is true for a row.

The logical operators are evaluated based on precedence: NOT has

the highest precedence, then AND, and then OR.

If a WHERE clause contains both an AND and an OR, the AND will

always be evaluated first.

SELECT *

FROM person

WHERE lastname = 'Johnson'

OR lastname = 'Johanson'

AND firstname = 'Carole'

;

-

This query will find Carole Johanson as well as

anyone with the last name of Johnson.

Use parentheses to override the precedence of these operators.

SELECT *

(20)

Report all personal orders made by the manager of store 4: SELECT invoice_number, amount_due

FROM order_header

WHERE customer_id = (SELECT manager_id FROM store WHERE store_number = 4); Show the name of the customer on invoice #2345:

SELECT lastname, firstname FROM person

WHERE id = (SELECT customer_id FROM order_header WHERE invoice_number = 2345);

(21)

Subqueries

A subquery is a SELECT statement that appears as part of another

SQL statement.

You may use any number of subqueries and can even nest a

subquery in other subquery.

The subquery must be enclosed in parentheses.

The subquery can have any SQL feature except for a set

operation or an ORDER BY clause.

A single-row subquery returns exactly one record, while a

multi-row subquery returns two or more records.

In a WHERE clause, use a subquery to retrieve values to use in a

condition.

SELECT lastname, firstname, area_code, phone_number

FROM person

WHERE state != (SELECT state

FROM store

WHERE store_number = 4);

A subquery must return the correct number and type of columns and

rows.

(22)

To display the same information for both Washington and Colorado, enter the following: SELECT store_number, street, city, area_code, phone_number FROM store

WHERE state = 'WA' OR state = 'CO'; An equivalent query makes use of the IN operator:

WHERE state IN ('WA', 'CO');

To list all store locations in California outside the 415 and 213 area codes:

WHERE area_code NOT IN ('213', '415') AND state = 'CA';

To display a specific vendor’s products that fall in the 100 to 500 dollar price range: SELECT description, price

FROM product

WHERE vendor_id = 'IBM'

AND price BETWEEN 100 AND 500;

To display a specific vendor’s products that fall in the 100 to 500 dollar price range: SELECT description, price

FROM product

WHERE vendor_id = 'IBM' AND price BETWEEN 100 AND 500; To display all orders placed between April 1, 1999 and March 31, 2000:

SELECT invoice_number, customer_id, order_date FROM order_header

(23)

Use the IN (or NOT IN) operator to compare a column against

several possible values.

SELECT lastname, firstname

FROM person

WHERE state IN ('CA', 'CO');

IN is equivalent to ORing several conditions together.

SELECT lastname, firstname

FROM person

WHERE state = 'CA'

OR state = 'CO';

You may use a multi-row subquery to provide a list of values.

SELECT lastname, firstname, area_code, phone_number

FROM person

WHERE state NOT IN (SELECT state FROM store);

The subquery can return several values.

Use the BETWEEN operator to compare a column against a range of

inclusive values.

SELECT lastname, firstname

FROM person

(24)

SELECT id, firstname, lastname FROM person

WHERE lastname LIKE 'M%ll_'; This query will match names such as:

ID FIRSTNAME LASTNAME --- --- 6873 L. E. McAnally 8993 Madonna Mullally 9166 Angie Matilla 9412 Bruce McNally 9845 Neil Montville

Or, to find names that end in 'son':

SELECT id, lastname, firstname FROM person

(25)

The LIKE Operator

The LIKE operator provides pattern matching for character data.

With the LIKE operator, you can use wildcards:

%

Matches zero or more characters.

_

Matches exactly one character and is position-dependent.

SELECT vendor_id, name

FROM vendor

WHERE name LIKE '%Software%';

The string containing wildcards must be enclosed in quotes.

Wildcards work only with the LIKE operator.

If you need to include the % or _ characters in the search pattern,

specify an escape character.

An escape character removes the special meaning of the

character following it.

For example, to locate vendor company names that contain an

underscore, modify the query with an escape parameter:

SELECT name

FROM vendor

(26)

How a query is processed:

1. Form a product set consisting of every row of the table in the FROM clause. 2. If there is a WHERE clause, apply the condition to each row in the product set. A. If the condition is true for a row, retain the row.

B. If the condition is false or null for a row, discard the row.

3. For each row retained, calculate the value of each item in the SELECT clause, producing a row in the result set.

4. If the DISTINCT operator was used, eliminate any duplicate rows in the result set. 5. If there is an ORDER BY clause, sort the result set rows as specified.

(27)

Labs

Write queries to:

1. Retrieve a list of all store locations and their manager’s ids.

2. Find the name of the person who manages the store in Las Vegas. 3. Find the names of all employees supervised by the manager of the Las

Vegas store.

4. List the states in which there are stores, listing each state only once. 5. List the states in which there are people, listing each state only once. 6. List the people who live in Glendale, California.

7. List the last name, first name, city, and state of everyone who lives in a city that starts with “Las” or “Los” or “San.”

(28)

(29)

(30)

(31)

Chapter Objectives

Collect data from multiple tables with a single query.

Use the relational aspects of your database in queries.

Relate columns within the same table.

Define the term outer join.

Use outer joins and recognize outer join syntax in different

RDBMSs.

(32)

When you join two tables, the result set consists of all of the rows that matched the join condition (and any other conditions). All rows that do not have a corresponding row in the other table are thrown out. This is called an inner join.

The following query will return information only for employees; no other people from the person table will have a matching id in the employee table.

SELECT p.id, lastname, title FROM person p, employee e WHERE p.id = e.id;

In fact, we know that we will get information for all employees because of the foreign key from employee.id to person.id.

(33)

Selecting from Multiple Tables

To retrieve data from more than one table in the same SELECT

statement, the tables are

joined together.

Specify the tables in the FROM clause.

SELECT lastname, title

FROM person, employee

WHERE person.id = employee.id;

Use conditions in the WHERE clause to match rows from the

tables.

-

These conditions are called join conditions.

If you reference a column whose name appears in more than one

table, you must precede the column name with the table name.

WHERE person.id = employee.id;

Using a table alias can save you some typing.

SELECT p.id, lastname, title

FROM person p, employee e

WHERE p.id = e.id;

Joins make full use of the relational aspects of a database; records

from multiple tables are related through column values.

(34)

When using qualified notation, your queries will involve a lot more typing. You can reduce the amount of typing by using table aliases. A table alias is a shorthand notation for a table name in the FROM clause. Qualified notation is also used to select from a table in another schema:

SELECT p.firstname, p.lastname, st.city, st.state FROM kris.person p, jody.store st

WHERE st.manager_id = p.id; Other kinds of join types are:

CROSS — A join done without a WHERE clause (usually a bad idea). This joins each row of one table to each row of the other table. This is also called a Cartesian product.

INNER — Only rows that were matched are returned (the default).

OUTER — Returns all matched rows from both tables, plus unmatched rows from the first table (LEFT), or unmatched rows from the second table (RIGHT) or a combination of both (FULL).

Qualified Notation:

SELECT lastname, firstname,

p.area_code || p.phone_number "NUMBER", pay_amount,

credit_limit, balance

FROM person p, account a, employee e WHERE p.id = e.id

AND e.id = a.customer_id

AND pay_amount < credit_limit AND balance > pay_amount / 2;

(This example finds all employees who take advantage of their inflated credit limit. Although qualified notation is only required to clarify certain columns, like id, this query might be easier to read if every column were qualified.)

(35)

Joining Tables

Join conditions are often formed using parent/child relationships.

Parent/child relationships are implemented using PRIMARY

and FOREIGN keys.

Most joins are equi-joins which require an exact match between

two columns.

There is no limit on the number of join conditions.

There is no limit on the number of tables involved in a query.

The more tables, the greater the hit on performance.

When using a join, it is recommended that you precede every

selected column with the corresponding table name.

This is called qualified notation or dot notation.

This is never necessary in queries involving only one table.

When a column name appears in more than one

of the queried tables, you must resolve ambiguity.

All fields from one table can be selected:

(36)

1. Form the product of the tables in the FROM clause. If there is only one table, then the product set consists of all the rows of that table. Otherwise,

A. If a cross join is specified (that is, no join conditions), then for each row in the first table, include a row in the product for each row of the second table.

B. If an inner join is specified, then for each row in the first table with a matching row in the second table, include a row in the product consisting of the matching rows.

2. If there is a WHERE clause, apply the condition to each row in the product set. If the condition contains a subquery, perform the subquery for each row as it is tested. A. If the condition is true for a row, retain the row.

4. If the DISTINCT operator was used, discard duplicate rows from the result set. 5. If there is an ORDER BY clause, sort the result set rows as specified.

(37)

Joining More than Two Tables

You can join three or more tables with normal join syntax.

SELECT oh.invoice_number, oi.product_id,

p.description, oi.quantity

FROM order_header oh, order_item oi, product p

WHERE customer_id = 7042

AND oi.invoice_number = oh.invoice_number

AND p.product_id = oi.product_id;

Be sure each table in the FROM clause has a join condition in the

WHERE clause.

You need at least the number of tables minus 1 join conditions to

avoid a cross join.

-

4 tables, 3 conditions.

-

5 tables, 4 conditions.

(38)

Remember how a query is processed: the database engine first forms the product of the tables in the FROM clause, then applies the WHERE conditions to generate rows in the result set. For a self-join, simply name the same table twice in the FROM clause and the database engine will generate the cross product of the table with itself. Give the table two different aliases so that you can unambiguously reference columns in the SELECT list and the WHERE clause.

SELECT ep.lastname, ep.firstname, emp.title,

'Supervisor: ' || sp.firstname || ' ' || sp.lastname, 'Sup. Title: ' || sup.title

FROM person ep, employee emp, employee sup, person sp WHERE emp.supervisor_id = sup.id

AND emp.id = ep.id AND sup.id = sp.id;

In other words, in a self join, you treat a table as though it were two separate tables and just do an ordinary join.

(39)

Self Joins

A

self join queries a single table as though it were two separate

tables.

You must use table aliases to perform a self join.

SELECT emp.id, emp.title,

sup.id, sup.title

FROM employee emp, employee sup

WHERE emp.supervisor_id = sup.id;

(40)

Syntax for Outer Joins

The syntax for outer joins varies widely among database vendors. All of these perform the same query — a list of all orders and the charge account on which the order was placed. If the order was not charged (that is, has no account number on it), we still see the order.

Oracle:

SELECT oh.invoice_number, oh.order_date, a.account_name FROM order_header oh, account a

WHERE oh.account_number = a.account_number(+); Informix:

SELECT oh.invoice_number, oh.order_date, a.account_name FROM order_header oh, OUTER account a

WHERE oh.account_number = a.account_number; Sybase SQL Server (and Microsoft SQL Server):

SELECT oh.invoice_number, oh.order_date, a.account_name FROM order_header oh, account a

WHERE oh.account_number *= a.account_number; Sybase SQL Anywhere:

SELECT oh.invoice_number, oh.order_date, a.account_name FROM order_header oh LEFT OUTER JOIN account a

ON oh.account_number = a.account_number;

The last example adheres to the SQL2 standard syntax for joins, which is more complete and flexible and better prevents ambiguities than the others.

(41)

Outer Joins

An

outer join retrieves all rows from one table, plus the matching

rows from the second table.

The column values for the non-matched (which would have

come from the second table) will be null.

Standard join syntax specifies which table, the left or right, will have

all of its rows returned.

SELECT oh.invoice_number, oh.order_date,

a.account_name

FROM order_header oh LEFT OUTER JOIN account a

ON oh.account_number = a.account_number;

All records from the LEFT table will be returned, the NULLs

where information from the right table wasn't available.

(42)

The SQL2 standard provides a complete and flexible syntax for joins, which, unfortunately, few vendors actually support. In the SQL2 syntax, joins are expressed in the FROM clause rather than the WHERE clause.

SELECT oh.invoice_number, oh.order_date, a.account_name, a.balance

FROM order_header oh INNER JOIN account a ON (a.account_number = oh.account_number);

FROM order_header oh NATURAL JOIN account a; The following left outer join:

FROM order_header oh LEFT OUTER JOIN account a ON (oh.account_number = a.account_number)

WHERE oh.amount_paid = 0; Can be turned into this right outer join:

FROM account a RIGHT OUTER JOIN order_header oh ON (a.account_number = oh.account_number)

(43)

Types of Outer Joins

Left Outer Join — All matching rows from the two tables, plus

rows from the first table that have no matching rows in the second.

Right Outer Join — All matching rows from the two tables, plus

rows from the second table that have no matching rows in the first.

Full Outer Join — All matching rows from the two tables, plus

rows from each table that have no matching rows in the other.

(44)

1. Form the product of the tables in the FROM clause. If there is only one table, then the product set consists of all the rows of that table. Otherwise,

A. If a cross join is specified (that is, no join conditions), then for each row in the first table, include a row in the product for each row of the second table.

B. If an inner join is specified, then for each row in the first table with a matching row in the second table, include a row in the product consisting of the matching rows.

C. If an outer join is specified, start with the inner join of the two tables; then,

I. For a left outer join, for each row in the first table with no match in the second, add a row to the product consisting of that row and a row of nulls for the second table. II. For a right outer join, for each row in the second table with no match in the first, add

a row to the product consisting of that row and a row of nulls for the first table. III. For a full outer join, do both I and II.

2. If there is a WHERE clause, apply the condition to each row in the product set. If the condition contains a subquery, perform the subquery for each row as it is tested. A. If the condition is true for a row, retain the row.

4. If the DISTINCT operator was used, discard duplicate rows from the result set. 5. If there is an ORDER BY clause, sort the result set rows as specified.

(45)

Labs

1. Using joins, write queries to:

a. List of all of the store locations and their manager’s first name and last name.

b. Find the name of the person who manages the store in Las Vegas. c. Find the names of all employees supervised by the manager of the Las

Vegas store.

d. List the names of everyone living in Glendale, California, who have accounts.

Compare your solutions to your solutions from the earlier chapter on queries. 2. Using joins, write queries to:

a. List the first name and last name of each employee.

b. List the first name, last name, and pay type name of each employee. 3. Create a view called phonelist that will list the name and phone number of

every employee.

4. Modify phonelist, if necessary, so that you can retrieve phone listings based on city.

5. Write a query to list orders. It should retrieve invoice number, name of the person who placed the order, product ids and quantities.

6. Modify the above query, if necessary, to include the product description of each item.

(46)

(47)

Labs

8. The product table lists the stock threshold for everything we sell. If this value is 0, thenthe product is a special-order item that is not normally carried in stock. If the value of the stock threshold is greater than zero, then the product must be reordered whenever the quantity on hand drops below that value.

Find all products that are low on inventory and must be reordered for store 1.

(Optional) How would you write a DML statement that updates the on-order quantity for such items?

9. Write a query that lists all stores and their manager’s names, including those which do not currently have managers.

10. Write a query that will list the first name and last name, along with the supervisor’s id and title, of each employee.

11. (Optional) Modify the above query, if necessary, so that it lists all employees, even those with no supervisor.

12. (Optional) Modify the above query, if necessary, so that it also lists the supervisor’s first name and last name.

(48)

SQL Programming. Student Workbook

i

t

i

t

i

t

Contents

Chapter Objectives

Retrieve data from a table using the SQL SELECT statement.

Sort the output of a SELECT statement.

Filter the returned rows with a WHERE clause.

Locate fields which have not yet been populated.

Combine multiple search criteria in a WHERE clause using logical

operators.

Use subqueries to generate values for use in WHERE clause

conditions.

Use the IN and BETWEEN operators to match a column against

multiple values.

The SELECT Statement

SQL data retrievals are done using the SELECT statement.

Data retrievals are also called queries.

A SELECT statement may make use of several clauses, but

minimally must include the following two:

SELECT [DISTINCT] {item_list | *}

FROM table

The SELECT clause specifies which data values will be retrieved,

and will be followed by either an item_list or a *.

* represents all the columns in table.

item_list is a column, an expression, or a comma separated

list of any combination of these.

Each item in item_list will display to the screen under a

column heading of the same name, unless an alias is specified.

Use DISTINCT to eliminate duplicate rows from the displayed

results.

The FROM clause specifies a table, or comma separated list of

tables, from which to retrieve data.

Creating Some Order

Data is not physically stored in a table in any specified order.

Unless the RDBMS supports certain types of indexes, records

are usually appended to the end of the table.

The SELECT statement’s ORDER BY clause is the only way to

enforce sequencing on the result set.

The ORDER BY clause may be included in the SELECT statement:

SELECT {item_list | *}

FROM table

[ORDER BY column [ASC | DESC],...]

column can either be the name or the numeric position of the

column in the item_list.

The result set is sorted by the first column name in the ORDER BY

clause.

If there are duplicate values in the first sort column, the sort is

repeated on the second column in the list, and so on.

The rows may be sorted in ascending (the default) or descending

order.

Use the WHERE clause when only a subset of all the rows in a table is

required.

SELECT {item_list | *}

FROM

table

[WHERE

conditions]

The WHERE clause is evaluated once for each row in table.

conditions will be one or more expressions that will evaluate to

either true, false or neither (null).

If the WHERE clause evaluates to true, then the current row is

returned in the result set.

Operators that may be used in the WHERE clause include:

=

equal to

!=, ^=, <>

not equal to

>, <

greater than, less than

>=, <=

greater than or equal to, less than or equal to

Any character string values in conditions must be enclosed within

single quotes.

NULL Values

A null is the absence of a value.