INSTRUCTOR’S MANUAL
TO ACCOMPANY
40th Anniversary Edition
DATABASE PROCESSING
Fundamentals, Design, and Implementation
15th Edition
Chapter 2
Introduction to Structured Query Language
Prepared By
Scott L. Vandenberg
Siena College
David M. Kroenke | David J. Auer | Scott L. Vandenberg | Robert C. Yoder
Instructor's Manual to accompany:
Database Processing: Fundamental, Design, and Implementation (15th Edition)
David M. Kroenke | David J. Auer | Scott L. Vandenberg | Robert C. Yoder
Chapter Two – Introduction to Structured Query Language
❖ CHAPTER OBJECTIVES
• To understand the use of extracted data sets in business intelligence (BI) systems
• To understand the use of ad-hoc queries in business intelligence (BI) systems
• To understand the history and significance of Structured Query Language (SQL)
• To understand the SQL SELECT/FROM/WHERE framework as the basis for
database queries
• To create SQL queries to retrieve data from a single table
• To create SQL queries that use the SQL SELECT, FROM, WHERE, ORDER BY,
GROUP BY, and HAVING clauses
• To create SQL queries that use the SQL DISTINCT, TOP, and TOP PERCENT
keywords
❖ IMPORTANT TEACHING NOTES – READ THIS FIRST!
Chapter 2 – Introduction to Structured Query Language is intended to be taught in
Chapter Two – Introduction to Structured Query Language
Page 2-4
❖ ERRATA
There are no known errors at this time. Any errors that are discovered in the future will
be reported and corrected in the Online DBP e15 Errata document, which will be
available at http://www.pearsonhighered.com/kroenke.
❖ TEACHING SUGGESTIONS
• Database files to illustrate the examples in the chapter and solution database
files for your use are available in the Instructor’s Resource Center on the text’s
Web site (www.pearsonhighered.com/kroenke).
• The best way for students to understand SQL is by using it. Have your students
work through the Review Questions, Exercises, the Marcia’s Dry Cleaning Case
Questions, and the Queen Anne Curiosity Shop or Morgan Importing Project
Questions in an actual database. Students can create databases in Microsoft
Access with basic tables, relationships, and data from the material in the book.
SQL scripts for Microsoft SQL Server, Oracle Database, and MySQL versions of
Cape Codd, MDC, QACS, and MI are available in the Instructor’s Resource
Center on the text’s Web site (www.pearsonhighered.com/kroenke). An Access
version of WPC is also available there.
• Microsoft Access database files for Cape Codd, together with SQL scripts for
Microsoft SQL Server, Oracle Database, and MySQL versions of Cape Codd,
MDC, QACS, and MI are available for student use in the Student Resources on
the text’s Web site (www.pearsonhighered.com/kroenke).
• The SQL processors in the various DBMSs are very fussy about character sets
used for SQL statements. They want to see plain ASCII text, not fancy fonts.
This is particularly true of the single quotation ( ' ) used to designate character
strings, but we’ve also had problems with the minus sign. If your students are
having problems getting a “properly structured SQL statement” to run, look
closely for this type of problem. It occurs most frequently when copying/pasting a
query from a word processor into a query window.
• There is a useful teaching technique which will allow you to demonstrate the SQL
queries in the text using Microsoft SQL Server if you have it available.
Chapter Two – Introduction to Structured Query Language
Chapter Two – Introduction to Structured Query Language
2017. The Microsoft Access results achieving the ORDER BY without using the
alias are also shown, so you can assign these problems with or without the
“ORDER BY alias” part of the questions.
• Microsoft Access 2016 does not support SQL wildcard characters (see Review
Questions 2.31 – 2.33), although it does have equivalent wildcard characters as
described in the chapter. The correct solutions for these questions were
obtained using Microsoft SQL Server 2017, and solutions are shown for Access
as well.
• For those students who are used to procedural languages, they may have some
initial difficulty with a language that does set processing like SQL. These
students are accustomed to processing rows (records) rather than sets. It is time
well spent to make sure they understand that SQL processes tables at a time,
not rows at a time.
• Students may have some trouble understanding the GROUP BY clause. If you
can explain it in terms of traditional control break logic (sort rows on a key then
process the rows until the value of the key changes), they will have less trouble.
This also explains why the GROUP BY clause will likely present the rows sorted
even though you do not use an ORDER BY clause.
• At this point, students familiar with Microsoft Access will wonder why they are
learning SQL. They have made queries in Microsoft Access using Microsoft
Access's version of Query-By-Example (QBE), and therefore never had to
understand the SQL. In many cases, they will not know that Microsoft Access
generates SQL code when you create a query in design view. It is worth letting
them know this is done and even showing them the SQL created for and
underlying a Microsoft Access query.
• It is also important for students to understand that, in many cases, the Query-By-
Example forms such as Microsoft Access’s design view can be very inefficient.
Also, the QBE forms are not available from within an application program such as
Java or C++ or PHP, and so SQL must be written.
• It has been our experience that a review of a Cartesian Product from an algebra
class is time well spent. Show students what will happen if a WHERE statement
Chapter Two – Introduction to Structured Query Language
❖ ANSWERS TO REVIEW QUESTIONS
2.1 What is an online transaction processing (OLTP) system? What is a business
intelligence (BI) system? What is a data warehouse?
2.2 What is an ad-hoc query?
2.3 What does SQL stand for, and what is SQL?
2.4 What does SKU stand for? What is an SKU?
Chapter Two – Introduction to Structured Query Language
2.5 Summarize how data were altered and filtered in creating the Cape Codd data
extraction.
Data from the Cape Codd operational retail sales database were used to create a retail sales
extraction database with three tables: RETAIL_ORDER, ORDER_ITEM, and SKU_DATA.
The RETAIL_ORDER table uses only a few of the columns in the operational database. The
structure of the table is:
RETAIL_ORDER (OrderNumber, StoreNumber, StoreZip, OrderMonth, OrderYear,
OrderTotal)
For this table, the original column OrderDate (in the data format MM/DD/YYYY [04/26/2018])
was converted into the columns OrderMonth (in a Character(12) format so that each month is
2.6 Explain in general terms the relationships of the RETAIL_ORDER, ORDER_ITEM,
SKU_DATA, and BUYER tables. What is the relationship of these tables to the
CATALOG_SKU_2017 and CATALOG_SKU_2018 tables?
Chapter Two – Introduction to Structured Query Language
2.7 Summarize the background of SQL.
SQL was developed by IBM in the late 1970s, and in 1992 it was endorsed as a national standard
2.8 What is SQL-92? How does it relate to the SQL statements in this chapter?
Chapter Two – Introduction to Structured Query Language
Page 2-10
2.9 What features have been added to SQL in versions subsequent to SQL-92?
2.10 Why is SQL described as a data sublanguage?
2.11 What does DML stand for? What are DML statements?
2.12 What does DDL stand for? What are DDL statements?
2.13 What is the SQL SELECT/FROM/WHERE framework?
2.14 Explain how Microsoft Access uses SQL.
Microsoft Access uses SQL, but generally hides the SQL from the user. For example, Microsoft
2.15 Explain how enterprise-class DBMS products use SQL.
Chapter Two – Introduction to Structured Query Language
Page 2-11
Enterprise-class DBMS products, which include Microsoft SQL Server, Oracle Corporation’s
Oracle Database and MySQL, and IBM’s DB2, require you to know and use SQL. All data
manipulation is expressed in SQL in these products.
The Cape Codd Outdoor Sports sale extraction database has been modified to include three
additional tables: the INVENTORY table, the WAREHOUSE table, and the
CATALOG_SKU_2016 table. The table schemas for these tables, RETAIL_ORDER,
ORDER_ITEM, SKU_DATA, BUYER, CATALOG_SKU_2017, and CATALOG_SKU_2018
tables, are as follows:
RETAIL_ORDER (OrderNumber, StoreNumber, StoreZip, OrderMonth, OrderYear,
OrderTotal)
ORDER_ITEM (OrderNumber, SKU, Quantity, Price, ExtendedPrice)
SKU_DATA (SKU, SKU_Description, Department, Buyer)
BUYER (BuyerName, Department, Position, Supervisor)
WAREHOUSE (WarehouseID, WarehouseCity, WarehouseState, Manager,
SquareFeet)
INVENTORY (WarehouseID, SKU, SKU_Description, QuantityOnHand,
QuantityOnOrder)
CATALOG_SKU_2016 (CatalogID, SKU, SKU_Description, CatalogPage,
DateOnWebSite)
CATALOG_SKU_2017 (CatalogID, SKU, SKU_Description, CatalogPage,
DateOnWebSite)
CATALOG_SKU_2018 (CatalogID, SKU, SKU_Description, CatalogPage,
DateOnWebSite)
The nine tables in the revised Cape Codd database schema are shown in Figure 2-35. The
column characteristics for the WAREHOUSE table are shown in Figure 2-36, the column
characteristics for the INVENTORY table are shown in Figure 2-37, and the column
characteristics for the CATALOG_SKU_2016 table are shown in Figure 2-38. The data for the
WAREHOUSE table are shown in Figure 2-39, the data for the INVENTORY table are shown in
Figure 2-40, and the data for the CATALOG_SKU_2016 table are shown in Figure 2-41.
Chapter Two – Introduction to Structured Query Language
Page 2-12
Figure 2-35 – The Cape Codd Database with the WAREHOUSE, INVENTORY, and
CATALOG_SKU_2016 tables
Figure 2-36 - Column Characteristics for the Cape Codd Database WAREHOUSE Table
Chapter Two – Introduction to Structured Query Language
Chapter Two – Introduction to Structured Query Language
Chapter Two – Introduction to Structured Query Language
Chapter Two – Introduction to Structured Query Language
Chapter Two – Introduction to Structured Query Language
2017. The SQL statements shown should run with little, if any, modification needed for Oracle
Database 12c Release 2, Oracle Database Express Edition 11g R2, and MySQL 5.7. A few of the
queries illustrate some minor syntactic differences between the systems. In those cases, we have
shown the minor changes necessary for Oracle Database and MySQL in this manual. In addition,
the solution files for each system of course have working queries for that system.
2.16 There is an intentional flaw in the design of the INVENTORY table used in these
exercises. This flaw was purposely included in the INVENTORY tables so that you can
answer some of the following questions using only that table. Compare the SKU and
INVENTORY tables, and determine what design flaw is included in INVENTORY.
Specifically, why did we include it?
2.17 Write an SQL statement to display SKU and SKU_Description.
SQL Solutions to Project Questions 2.17 – 2.60 are contained in the Microsoft Access database
DBP-e15-IM-CH02-Cape-Codd-RQ.accdb and in the corresponding files for SQL Server, Oracle
Database, and MySQL, which are all available on the text’s Web site
(www.pearsonhighered.com/kroenke).
SELECT SKU, SKU_Description
FROM INVENTORY;
Chapter Two – Introduction to Structured Query Language
Chapter Two – Introduction to Structured Query Language
2.18 Write an SQL statement to display SKU_Description and SKU.
SQL Solutions to Project Questions 2.17 – 2.60 are contained in the Microsoft Access database
DBP-e15-IM-CH02-Cape-Codd-RQ.accdb and in the corresponding files for SQL Server, Oracle
Database, and MySQL, which are all available on the text’s Web site
(www.pearsonhighered.com/kroenke).
SELECT SKU_Description, SKU
FROM INVENTORY;
Chapter Two – Introduction to Structured Query Language
Chapter Two – Introduction to Structured Query Language
Page 2-21
2.19 Write an SQL statement to display WarehouseID.
2.20 Write an SQL statement to display unique WarehouseIDs.
SQL Solutions to Project Questions 2.17 – 2.60 are contained in the Microsoft Access database
DBP-e15-IM-CH02-Cape-Codd-RQ.accdb and in the corresponding files for SQL Server, Oracle
Chapter Two – Introduction to Structured Query Language
2.21 Write an SQL statement to display all of the columns without using the SQL asterisk (*)
wildcard character.
SQL Solutions to Project Questions 2.17 – 2.60 are contained in the Microsoft Access database
DBP-e15-IM-CH02-Cape-Codd-RQ.accdb and in the corresponding files for SQL Server, Oracle
Chapter Two – Introduction to Structured Query Language
2.22 Write an SQL statement to display all of the columns using the SQL asterisk (*) wildcard
character.
SQL Solutions to Project Questions 2.17 – 2.60 are contained in the Microsoft Access database
Chapter Two – Introduction to Structured Query Language
2.23 Write an SQL statement to display all data on products having a QuantityOnHand
greater than 0.
Chapter Two – Introduction to Structured Query Language
2.24 Write an SQL statement to display the SKU and SKU_Description for products having
QuantityOnHand equal to 0.
SQL Solutions to Project Questions 2.17 – 2.60 are contained in the Microsoft Access database
DBP-e15-IM-CH02-Cape-Codd-RQ.accdb and in the corresponding files for SQL Server, Oracle
Chapter Two – Introduction to Structured Query Language
2.25 Write an SQL statement to display the SKU, SKU_Description, and WarehouseID for
products having QuantityOnHand equal to 0. Sort the results in ascending order by
WarehouseID.
2.26 Write an SQL statement to display the SKU, SKU_Description, and WarehouseID for
products having QuantityOnHand greater than 0. Sort the results in descending order by
WarehouseID and ascending order by SKU.
SQL Solutions to Project Questions 2.17 – 2.60 are contained in the Microsoft Access database
Chapter Two – Introduction to Structured Query Language
2.27 Write an SQL statement to display SKU, SKU_Description, and WarehouseID for all
products that have a QuantityOnHand equal to 0 and a QuantityOnOrder greater than 0.
Sort the results in descending order by WarehouseID and in ascending order by SKU.
SQL Solutions to Project Questions 2.17 – 2.60 are contained in the Microsoft Access database
Chapter Two – Introduction to Structured Query Language
2.28 Write an SQL statement to display SKU, SKU_Description, and WarehouseID for all
products that have a QuantityOnHand equal to 0 or a QuantityOnOrder equal to 0. Sort
the results in descending order by WarehouseID and in ascending order by SKU.
SQL Solutions to Project Questions 2.17 – 2.60 are contained in the Microsoft Access database
DBP-e15-IM-CH02-Cape-Codd-RQ.accdb and in the corresponding files for SQL Server, Oracle
Chapter Two – Introduction to Structured Query Language
10. Do not use the BETWEEN keyword.
SQL Solutions to Project Questions 2.17 – 2.60 are contained in the Microsoft Access database
DBP-e15-IM-CH02-Cape-Codd-RQ.accdb and in the corresponding files for SQL Server, Oracle
Database, and MySQL, which are all available on the text’s Web site
(www.pearsonhighered.com/kroenke).
Chapter Two – Introduction to Structured Query Language
10. Use the BETWEEN keyword.
SQL Solutions to Project Questions 2.17 – 2.60 are contained in the Microsoft Access database
2.31 Write an SQL statement to show a unique SKU and SKU_Description for all products
having an SKU description starting with ‘Half-dome’.
SQL Solutions to Project Questions 2.17 – 2.60 are contained in the Microsoft Access database
DBP-e15-IM-CH02-Cape-Codd-RQ.accdb and in the corresponding files for SQL Server, Oracle
Database, and MySQL, which are all available on the text’s Web site
(www.pearsonhighered.com/kroenke).
Note that, as discussed in Chapter 2, Microsoft Access 2016 uses wildcard characters that differ
from the SQL standard.
Chapter Two – Introduction to Structured Query Language
2.32 Write an SQL statement to show a unique SKU and SKU_Description for all products
having a description that includes the word 'Climb'.
SQL Solutions to Project Questions 2.17 – 2.60 are contained in the Microsoft Access database
DBP-e15-IM-CH02-Cape-Codd-RQ.accdb and in the corresponding files for SQL Server, Oracle
Database, and MySQL, which are all available on the text’s Web site
(www.pearsonhighered.com/kroenke).
2.33 Write an SQL statement to show a unique SKU and SKU_Description for all products
with a ‘d’ in the third position from the left in SKU_Description.
SQL Solutions to Project Questions 2.17 – 2.60 are contained in the Microsoft Access database
DBP-e15-IM-CH02-Cape-Codd-RQ.accdb and in the corresponding files for SQL Server, Oracle
Database, and MySQL, which are all available on the text’s Web site
(www.pearsonhighered.com/kroenke).
Note that, as discussed in Chapter 2, Microsoft Access 2016 uses wildcard characters that differ
from the SQL standard.
For Microsoft SQL Server, Oracle Database, and MySQL:
SELECT DISTINCT SKU, SKU_Description
FROM INVENTORY
WHERE SKU_Description LIKE '__d%';
For Microsoft Access:
SELECT DISTINCT SKU, SKU_Description
FROM INVENTORY
WHERE SKU_Description LIKE '??d*';
Chapter Two – Introduction to Structured Query Language
2.34 Write an SQL statement that uses all of the SQL built-in functions on the QuantityOn-
Hand column. Include meaningful column names in the result.
SQL Solutions to Project Questions 2.17 – 2.60 are contained in the Microsoft Access database
DBP-e15-IM-CH02-Cape-Codd-RQ.accdb and in the corresponding files for SQL Server, Oracle
2.36 Write an SQL statement to display the WarehouseID and the sum of QuantityOnHand
grouped by WarehouseID. Name the sum TotalItemsOnHand and display the results in
descending order of TotalItemsOnHand.
SQL Solutions to Project Questions 2.17 – 2.60 are contained in the Microsoft Access database
DBP-e15-IM-CH02-Cape-Codd-RQ.accdb and in the corresponding files for SQL Server, Oracle
2.37 Write an SQL statement to display the WarehouseID and the sum of QuantityOnHand
grouped by WarehouseID. Omit all SKU items that have three or more items on hand
from the sum, name the sum TotalItemsOnHandLT3, and display the results in
descending order of TotalItemsOnHandLT3.
SQL Solutions to Project Questions 2.17 – 2.60 are contained in the Microsoft Access database
DBP-e15-IM-CH02-Cape-Codd-RQ.accdb and in the corresponding files for SQL Server, Oracle
Database, and MySQL, which are all available on the text’s Web site
(www.pearsonhighered.com/kroenke).
For Microsoft SQL Server, Oracle Database, and MySQL:
SELECT WarehouseID, SUM(QuantityOnHand) AS TotalItemsOnHandLT3
FROM INVENTORY
Chapter Two – Introduction to Structured Query Language
2.38 Write an SQL statement to display the WarehouseID and the sum of QuantityOnHand
grouped by WarehouseID. Omit all SKU items that have three or more items on hand
from the sum, and name the sum TotalItemsOnHandLT3. Show Warehouse ID only for
warehouses having fewer than two SKUs in their TotalItemsOnHandLT3. Display the
results in descending order of TotalItemsOnHandLT3.
SQL Solutions to Project Questions 2.17 – 2.60 are contained in the Microsoft Access database
DBP-e15-IM-CH02-Cape-Codd-RQ.accdb and in the corresponding files for SQL Server, Oracle
Database, and MySQL, which are all available on the text’s Web site
(www.pearsonhighered.com/kroenke).
For Microsoft SQL Server, Oracle Database and MySQL:
SELECT WarehouseID, SUM(QuantityOnHand) AS TotalItemsOnHandLT3
FROM INVENTORY
WHERE QuantityOnHand < 3
Chapter Two – Introduction to Structured Query Language
2.39 In your answer to Review Question 2.38, was the WHERE clause or the HAVING clause
applied first? Why?
2.40 Write an SQL statement to display the SKU, SKU_Description, WarehouseID,
WarehouseCity, and WarehouseState for all items stored in the Atlanta, Bangor, or
Chicago warehouse. Do not use the IN keyword.
SQL Solutions to Project Questions 2.17 – 2.60 are contained in the Microsoft Access database
DBP-e15-IM-CH02-Cape-Codd-RQ.accdb and in the corresponding files for SQL Server, Oracle
Database, and MySQL, which are all available on the text’s Web site
(www.pearsonhighered.com/kroenke).
Chapter Two – Introduction to Structured Query Language
2.41 Write an SQL statement to display the SKU, SKU_Description, WarehouseID,
WarehouseCity, and WarehouseState for all items stored in the Atlanta, Bangor, or
Chicago warehouse. Use the IN keyword.
SQL Solutions to Project Questions 2.17 – 2.60 are contained in the Microsoft Access database
DBP-e15-IM-CH02-Cape-Codd-RQ.accdb and in the corresponding files for SQL Server, Oracle
Chapter Two – Introduction to Structured Query Language
2.42 Write an SQL statement to display the SKU, SKU_Description, WarehouseID,
WarehouseCity, and WarehouseState of all items not stored in the Atlanta, Bangor, or
Chicago warehouse. Do not use the NOT IN keyword.
SQL Solutions to Project Questions 2.17 – 2.60 are contained in the Microsoft Access database
DBP-e15-IM-CH02-Cape-Codd-RQ.accdb and in the corresponding files for SQL Server, Oracle
Database, and MySQL, which are all available on the text’s Web site
(www.pearsonhighered.com/kroenke).
NOTE: The symbol for “not equal to” is < >. Since we want the query output for warehouses
that are not Atlanta or Bangor or Chicago as a set, we must ask for warehouses that are not in the
group (Atlanta and Bangor and Chicago). This means we use AND in the WHERE clause – if
Chapter Two – Introduction to Structured Query Language
2.43 Write an SQL statement to display the SKU, SKU_Description, WarehouseID,
WarehouseCity, and WarehouseState of all items not stored in the Atlanta, Bangor, or
Chicago warehouse. Use the NOT IN keyword.
SQL Solutions to Project Questions 2.17 – 2.60 are contained in the Microsoft Access database
DBP-e15-IM-CH02-Cape-Codd-RQ.accdb and in the corresponding files for SQL Server, Oracle
Chapter Two – Introduction to Structured Query Language
2.44 Write an SQL statement to produce a single column called ItemLocation that combines
the SKU_Description, the phrase “is located in”, and WarehouseCity. Do not be
concerned with removing leading or trailing blanks.
SQL Solutions to Project Questions 2.17 – 2.60 are contained in the Microsoft Access database
DBP-e15-IM-CH02-Cape-Codd-RQ.accdb and in the corresponding files for SQL Server, Oracle
Database, and MySQL, which are all available on the text’s Web site
(www.pearsonhighered.com/kroenke).
Chapter Two – Introduction to Structured Query Language
2.45 Write an SQL statement to show the SKU, SKU_Description, and WarehouseID for all
items stored in a warehouse managed by ‘Lucille Smith’. Use a subquery.
SQL Solutions to Project Questions 2.17 – 2.60 are contained in the Microsoft Access database
DBP-e15-IM-CH02-Cape-Codd-RQ.accdb and in the corresponding files for SQL Server, Oracle
Chapter Two – Introduction to Structured Query Language
2.46 Write an SQL statement to show the SKU, SKU_Description, and WarehouseID for all
items stored in a warehouse managed by ‘Lucille Smith’. Use a join, but do not use JOIN
ON syntax.
SQL Solutions to Project Questions 2.17 – 2.60 are contained in the Microsoft Access database
DBP-e15-IM-CH02-Cape-Codd-RQ.accdb and in the corresponding files for SQL Server, Oracle
Chapter Two – Introduction to Structured Query Language
2.47 Write an SQL statement to show the SKU, SKU_Description, and WarehouseID for all
items stored in a warehouse managed by ‘Lucille Smith’. Use a join using JOIN ON
syntax.
SQL Solutions to Project Questions 2.17 – 2.60 are contained in the Microsoft Access database
DBP-e15-IM-CH02-Cape-Codd-RQ.accdb and in the corresponding files for SQL Server, Oracle
Database, and MySQL, which are all available on the text’s Web site
(www.pearsonhighered.com/kroenke).
For Microsoft SQL Server, Oracle Database, and MySQL:
2.48 Write an SQL statement to show the WarehouseID and average QuantityOnHand of all
items stored in a warehouse managed by ‘Lucille Smith’. Use a subquery.
SQL Solutions to Project Questions 2.17 – 2.60 are contained in the Microsoft Access database
DBP-e15-IM-CH02-Cape-Codd-RQ.accdb and in the corresponding files for SQL Server, Oracle
Chapter Two – Introduction to Structured Query Language
2.49 Write an SQL statement to show the WarehouseID and average QuantityOnHand of all
items stored in a warehouse managed by ‘Lucille Smith’. Use a join, but do not use
JOIN ON syntax.
SQL Solutions to Project Questions 2.17 – 2.60 are contained in the Microsoft Access database
DBP-e15-IM-CH02-Cape-Codd-RQ.accdb and in the corresponding files for SQL Server, Oracle
Database, and MySQL, which are all available on the text’s Web site
(www.pearsonhighered.com/kroenke).
Note that the “GROUP BY” clause is necessary here since warehouse manager names are not
Chapter Two – Introduction to Structured Query Language
2.50 Write an SQL statement to show the WarehouseID and average QuantityOnHand of all
items stored in a warehouse managed by ‘Lucille Smith’. Use a join using JOIN ON
syntax.
SQL Solutions to Project Questions 2.17 – 2.60 are contained in the Microsoft Access database
DBP-e15-IM-CH02-Cape-Codd-RQ.accdb and in the corresponding files for SQL Server, Oracle
Database, and MySQL, which are all available on the text’s Web site
(www.pearsonhighered.com/kroenke).
Note that the “GROUP BY” clause is necessary here since warehouse manager names are not
necessarily unique: since the question asks for warehouse ID, there should be one result for each
warehouse managed by a ‘Lucille Smith’.
For Microsoft SQL Server, Oracle Database, and MySQL:
2.51 Write an SQL statement to show the WarehouseID, WarehouseCity, WarehouseState,
Manager, SKU, SKU_Description, and QuantityOnHand of all items stored in a
warehouse managed by ‘Lucille Smith’. Use a join using JOIN ON syntax.
SQL Solutions to Project Questions 2.17 – 2.60 are contained in the Microsoft Access database
DBP-e15-IM-CH02-Cape-Codd-RQ.accdb and in the corresponding files for SQL Server, Oracle
Chapter Two – Introduction to Structured Query Language
SELECT WAREHOUSE.WarehouseID, WarehouseCity,
WarehouseState, Manager,
SKU, SKU_Description, QuantityOnHand
FROM INVENTORY INNER JOIN WAREHOUSE
ON INVENTORY.WarehouseID=WAREHOUSE.WarehouseID
WHERE Manager = 'Lucille Smith';
Note the use of the complete references to INVENTORY.WarehouseID and
WAREHOUSE.WarehouseID—the query will NOT work without them.
The above version of the query works in Access, SQL Server, Oracle Database, and MySQL.
The “INNER” keyword is required in Access, but is optional in SQL Server, Oracle, and MySQL.
In addition, this query could benefit from aliasing (range variables) for readability, but that syntax
is slightly different in Oracle than in the other three systems (the “AS” keyword is not allowed in
Oracle). Thus the most typical, preferred solutions for each system are as follows:
Chapter Two – Introduction to Structured Query Language
2.52 Write an SQL statement to display the WarehouseID, the sum of QuantityOnOrder, and
sum of QuantityOnHand, grouped by WarehouseID and QuantityOnOrder. Name the
sum of QuantityOnOrder as TotalItemsOnOrder and the sum of QuantityOnHand as
TotalItemsOnHand. Use only the INVENTORY table in your SQL statement.
2.53 Explain why you cannot use a subquery in your answer to Review Question 2.52.
In a query that contains a subquery, only data from fields in the table used in the top-level query
can be included in the SELECT statement. If data from fields from other tables are also needed, a
Chapter Two – Introduction to Structured Query Language
2.55 Write an SQL statement to join WAREHOUSE and INVENTORY and include all rows of
WAREHOUSE in your answer, regardless of whether they have any INVENTORY.
Include all columns of both tables, but do not repeat the join columns.
SQL Solutions to Project Questions 2.17 – 2.60 are contained in the Microsoft Access database
DBP-e15-IM-CH02-Cape-Codd-RQ.accdb and in the corresponding files for SQL Server, Oracle
Database, and MySQL, which are all available on the text’s Web site
(www.pearsonhighered.com/kroenke).
SELECT W.*, I.SKU, I.SKU_Description, I.QuantityOnHand,
I.QuantityOnOrder
Chapter Two – Introduction to Structured Query Language
2.56 Write an SQL statement to display the SKU, SKU_Description, and Department of all
SKUs that appear in either the Cape Codd 2016 Catalog (either in the printed catalog or
on the Web site) or the Cape Codd 2017 catalog (either in the printed catalog or on the
Web site) or both.
SQL Solutions to Project Questions 2.17 – 2.60 are contained in the Microsoft Access database
DBP-e15-IM-CH02-Cape-Codd-RQ.accdb and in the corresponding files for SQL Server, Oracle
Chapter Two – Introduction to Structured Query Language
2.57 Write an SQL statement to display the SKU, SKU_Description, and Department of all
SKUs that appear in either the Cape Codd 2016 Catalog (only in the printed catalog
itself) or the Cape Codd 2017 catalog (only in the printed catalog itself) or both.
SQL Solutions to Project Questions 2.17 – 2.60 are contained in the Microsoft Access database
DBP-e15-IM-CH02-Cape-Codd-RQ.accdb and in the corresponding files for SQL Server, Oracle
Database, and MySQL, which are all available on the text’s Web site
(www.pearsonhighered.com/kroenke).
Chapter Two – Introduction to Structured Query Language
2.58 Write an SQL statement to display the SKU, SKU_Description, and Department of all
SKUs that appear in both the Cape Codd 2016 Catalog (either in the printed catalog or
on the Web site) and the Cape Codd 2017 catalog (either in the printed catalog or on the
Web site).
SQL Solutions to Project Questions 2.17 – 2.60 are contained in the Microsoft Access database
DBP-e15-IM-CH02-Cape-Codd-RQ.accdb and in the corresponding files for SQL Server, Oracle
Database, and MySQL, which are all available on the text’s Web site
(www.pearsonhighered.com/kroenke).
2.59 Write an SQL statement to display the SKU, SKU_Description, and Department of all
SKUs that appear in both the Cape Codd 2016 Catalog (only in the printed catalog itself)
and the Cape Codd 2017 catalog (only in the printed catalog itself).
SQL Solutions to Project Questions 2.17 – 2.60 are contained in the Microsoft Access database
DBP-e15-IM-CH02-Cape-Codd-RQ.accdb and in the corresponding files for SQL Server, Oracle
Chapter Two – Introduction to Structured Query Language
Page 2-51
Note that Oracle Database and SQL Server support INTERSECT directly. In MySQL and
Access INTERSECT is not supported but can be simulated using a join.
2.60 Write an SQL statement to display the SKU, SKU_Description, and Department of all
SKUs that appear in only the Cape Codd 2016 Catalog (either in the printed catalog or
on the Web site) and not in the Cape Codd 2017 catalog (either in the printed catalog or
on the Web site).
SQL Solutions to Project Questions 2.17 – 2.60 are contained in the Microsoft Access database
DBP-e15-IM-CH02-Cape-Codd-RQ.accdb and in the corresponding files for SQL Server, Oracle
Database, and MySQL, which are all available on the text’s Web site
(www.pearsonhighered.com/kroenke).
Note that Oracle Database and SQL Server support set subtraction directly. In MySQL and
Access this operation is not supported but can be simulated using an outer join.
Chapter Two – Introduction to Structured Query Language
Page 2-52
❖ ANSWERS TO EXERCISES
For this set of project questions, we will extend the Microsoft Access 2016 database for
Wedgewood Pacific (WP) that we created in Chapter 1. Founded in 1987 in Seattle,
Washington, this company manufactures and sells consumer drone aircraft. This is an
innovative and rapidly expanding market. In January 2016, the FAA said that 181,000 drones
(out of the approximately 700,000 drones that may have been sold during the 2015 Christmas
season) had been registered under the new FAA drone registration rules.
WP currently produces three drone models: the Alpha III, the Bravo III, and the Delta IV. These
products are created by WP’s Research and Development group and produced at WP’s
production facilities. WP manufactures some of the parts used in the drones, but also purchases
some parts from other suppliers.
The company is located in two buildings. One building houses the Administration, Lega.,
Finance, Accounting, Human Resources, and Sales and Marketing departments, and the
second houses the Information Systems, Research and Development, and Production
departments. The company database contains data about employees; departments; projects;
assets, such as finished goods inventory, parts inventory, and computer equipment; and other
Chapter Two – Introduction to Structured Query Language
Chapter Two – Introduction to Structured Query Language
2.61 Figure 2-43 shows the column characteristics for the WP PROJECT table. Using the
column characteristics, create the PROJECT table in the WP.accdb database.
Chapter Two – Introduction to Structured Query Language
2.62 Create the relationship and referential integrity constraint between PROJECT and
DEPARTMENT. In the Edit Relationship dialog box, enable enforcing of referential
integrity and cascading of data updates, but do not enable cascading of data from
deleted records. We will define cascading actions in Chapter 6.
Chapter Two – Introduction to Structured Query Language
2.63 Figure 2-44 shows the data for the WP PROJECT table. Using the Datasheet view, enter
the data shown in Figure 2-44 into your PROJECT table.
2.64 Figure 2-45 shows the column characteristics for the WP ASSIGNMENT table. Using the
column characteristics, create the ASSIGNMENT table in the WP.accdb database.
Chapter Two – Introduction to Structured Query Language
2.65 Create the relationship and referential integrity constraint between ASSIGNMENT and
EMPLOYEE. In the Edit Relationship dialog box, enable enforcing of referential integrity,
Chapter Two – Introduction to Structured Query Language
2.66 Create the relationship and referential integrity constraint between ASSIGNMENT and
Chapter Two – Introduction to Structured Query Language
2.67 Figure 2-46 shows the data for the WP ASSIGNMENT table. Using the Datasheet view,
enter the data shown in Figure 2-46 into your ASSIGNMENT table.
Solutions to Project Questions 2.61 – 2.70 are contained in the Microsoft Access database DBP-
Chapter Two – Introduction to Structured Query Language
Chapter Two – Introduction to Structured Query Language
2.68 In Exercise 2.63, the table data was entered after referential integrity constraints were
created in Exercise 2.62. In Exercise 2.67, the table data was entered after referential
integrity constraints were created in Exercises 2.65 and 2.66. Why was the data entered
after the referential integrity constraints were created instead of before the constraints
were created?
Chapter Two – Introduction to Structured Query Language
Page 2-62
2.69 Using Figure 2-31 for reference, create the recursive relationship and referential integrity
constraint between Supervisor and BuyerName in BUYER. In the Edit Relationship
2.70 Using Microsoft Access SQL, create and run queries to answer the following questions.
Save each query using the query name format SQL-Query-02-##, where the ## sign is
Chapter Two – Introduction to Structured Query Language
Page 2-63
replaced by the letter designator of the question. For example, the first query will be
saved as SQL-Query-02-A.
Solutions to Project Questions 2.61 – 2.70 are contained in the Microsoft Access database DBP-
e15-IM-CH02-WP.accdb which is available on the text’s Web site
(www.pearsonhighered.com/kroenke).
A. What projects are in the PROJECT table? Show all information for each project.
/***** Question A - SQL-Query-02-A ************************/
SELECT * FROM PROJECT;
B. What are the ProjectID, ProjectName, StartDate, and EndDate values of projects in
the PROJECT table?
/***** Question B - SQL-Query-02-B ************************/
SELECT ProjectID, ProjectName, StartDate, EndDate
FROM PROJECT;
C. What projects in the PROJECT table started before August 1, 2018? Show all the
information for each project.
Chapter Two – Introduction to Structured Query Language
Page 2-64
/***** Question C - SQL-Query-02-C ************************/
SELECT *
FROM PROJECT
WHERE StartDate < #01-AUG-18#;
D. What projects in the PROJECT table have not been completed? Show all the
information for each project.
/***** Question D - SQL-Query-02-D ************************/
SELECT *
FROM PROJECT
WHERE EndDate IS NULL;
E. Who are the employees assigned to each project? Show ProjectID, Employee-
Number, LastName, FirstName, and OfficePhone.
/***** Question E - SQL-Query-02-E ************************/
SELECT ProjectID, E.EmployeeNumber, LastName, FirstName,
OfficePhone
FROM ASSIGNMENT AS A INNER JOIN EMPLOYEE AS E
ON A.EmployeeNumber=E.EmployeeNumber;
Chapter Two – Introduction to Structured Query Language
Page 2-65
F. Who are the employees assigned to each project? Show ProjectID, ProjectName,
and Department. Show EmployeeNumber, LastName, FirstName, and OfficePhone.
Note the use of the aliases ProjectDepartment, and EmployeePhone)
Chapter Two – Introduction to Structured Query Language
Page 2-66
/***** Question F - SQL-Query-02-F ************************/
SELECT P.ProjectID, ProjectName,
P.Department AS ProjectDepartment,
E.EmployeeNumber, LastName, FirstName,
OfficePhone AS EmployeePhone
FROM (ASSIGNMENT AS A INNER JOIN EMPLOYEE AS E
ON A.EmployeeNumber=E.EmployeeNumber)
INNER JOIN PROJECT AS P
ON A.ProjectID=P.ProjectID;
G. Who are the employees assigned to each project? Show ProjectID, ProjectName,
Department, and DepartmentPhone. Show EmployeeNumber, LastName,
FirstName, and OfficePhone. Sort by ProjectID in ascending order.
Note the use of the aliases ProjectDept, and EmpPhone.
Chapter Two – Introduction to Structured Query Language
/***** Question G - SQL-Query-02-G ************************/
SELECT P.ProjectID, ProjectName,
D.DepartmentName AS ProjectDept,
DepartmentPhone,
E.EmployeeNumber, LastName, FirstName,
E.OfficePhone AS EmpPhone
FROM ((ASSIGNMENT AS A INNER JOIN EMPLOYEE AS E
ON A.EmployeeNumber=E.EmployeeNumber)
INNER JOIN PROJECT AS P
ON A.ProjectID=P.ProjectID)
INNER JOIN DEPARTMENT AS D
ON P.Department=D.DepartmentName
ORDER BY P.ProjectID;
H. Who are the employees assigned to projects run by the marketing department?
Chapter Two – Introduction to Structured Query Language
Chapter Two – Introduction to Structured Query Language
Chapter Two – Introduction to Structured Query Language
Chapter Two – Introduction to Structured Query Language
Chapter Two – Introduction to Structured Query Language
exercise 2.70. Save each query using the query name format QBE-Query-02-##, where
the ## sign is replaced by the letter designator of the question. For example, the first
query will be saved as QBE-Query-02-A. HINT: In questions G and H, the default
approach of accepting all joins will not work, and you may have to delete some joins
from the initial QBE query.
Solutions to Project Questions 2.61 – 2.70 are contained in the Microsoft Access database DBP-
Chapter Two – Introduction to Structured Query Language
Page 2-73
A. What projects are in the PROJECT table? Show all information for each project.
B. What are the ProjectID, ProjectName, StartDate, and EndDate values of projects in
the PROJECT table?
Chapter Two – Introduction to Structured Query Language
Page 2-74
C. What projects in the PROJECT table started before August 1, 2018? Show all the
information for each project.
D. What projects in the PROJECT table have not been completed? Show all the
information for each project.
E. Who are the employees assigned to each project? Show ProjectID, Employee-
Number, LastName, FirstName, and OfficePhone.
Chapter Two – Introduction to Structured Query Language
Page 2-75
F. Who are the employees assigned to each project? Show ProjectID, ProjectName,
and Department. Show EmployeeNumber, LastName, FirstName, and OfficePhone.
G. Who are the employees assigned to each project? Show ProjectID, ProjectName,
Department, and Department Phone. Show EmployeeNumber, LastName,
FirstName, and OfficePhone. Sort by ProjectID in ascending order.
This question is more complicated than it seems, in that the default approach of “accepting”
all the joins in the QBE query yields an incorrect result. Without deleting the join from
EMPLOYEE to DEPARTMENT in the query window (as has been done below; right-click
on the relationship line from EMPLOYEE to DEPARTMENT and choose “Delete”), this
Chapter Two – Introduction to Structured Query Language
query will only return assignments in which an EMPLOYEE is assigned to a PROJECT that
is in the EMPLOYEE’s DEPARTMENT.
H. Who are the employees assigned to projects run by the marketing department?
Chapter Two – Introduction to Structured Query Language
Chapter Two – Introduction to Structured Query Language
Chapter Two – Introduction to Structured Query Language
Chapter Two – Introduction to Structured Query Language
Chapter Two – Introduction to Structured Query Language
Page 2-81
❖ MARCIA’S DRY CLEANING CASE QUESTIONS
Marcia Wilson owns and operates Marcia’s Dry Cleaning, which is an upscale dry cleaner in a
well-to-do suburban neighborhood. Marcia makes her business stand out from the competition
by providing superior customer service. She wants to keep track of each of her customers and
their orders. Ultimately, she wants to notify them that their clothes are ready via e-mail. To
provide this service, she has developed an initial database with several tables. Three of those
tables are as follows:
CUSTOMER (CustomerID, FirstName, LastName, Phone, EmailAddress, ReferredBy)
INVOICE (InvoiceNumber, CustomerID, DateIn, DateOut, TotalAmount)
INVOICE_ITEM (InvoiceNumber, ItemNumber, Item, Quantity, UnitPrice)
Where
ReferredBy in CUSTOMER must exist in CustomerID in CUSTOMER
CustomerID in INVOICE must exist in CustomerID in CUSTOMER
InvoiceNumber in INVOICE_ITEM must exist in InvoiceNumber in INVOICE
In this database schema, the primary keys are underlined and the foreign keys are shown in
italics. Note that CUSTOMER contains a recursive relationship between ReferredBy and
CustomerID, where ReferredBy contains the CustomerID value of the existing customer who
referred the new customer to Marcia’s Dry Cleaning. The database that Marcia has created is
named MDC, and the three tables in the MDC database schema are shown in Figure 2-47.
FIGURE 2-47 – The MDC Database
The column characteristics for the tables are shown in Figures 2-48, 2-49, and 2-50. The
relationship between CUSTOMER and INVOICE should enforce referential integrity, but not
cascade updates or deletions, while the relationship between INVOICE and INVOICE_ITEM
Chapter Two – Introduction to Structured Query Language
Page 2-82
should enforce referential integrity and cascade both updates and deletions. The data for these
tables are shown in Figures 2-51, 2-52, and 2-53.
You will need to create and setup a database named MDC_CH02 for use with these case
questions. A Microsoft Access 2016 database named MDC_CH02.accdb, and SQL scripts for
creating the MDC_CH02 database in Microsoft SQL Server, Oracle Database, and MySQL are
available on our Web site at www.pearsonhighered.com/kroenke .
If you are using the Microsoft Access 2016 MDC_CH02.accdb database, simply copy it to an
appropriate location in your Documents folder. Otherwise, you will need to use the discussion
and instructions necessary for setting up the MDC_CH02 database in the DBMS product you
are using:
• For Microsoft SQL Server 2017, see online Chapter 10A.
• For Oracle Database 12c Release 2 or Oracle Express Edition 11g Release 2, see online
Chapter 10B.
• For MySQL 5.7 Community Server, see online Chapter 10C.
Figure 2-48 - Column Characteristics for the MDC Database CUSTOMER Table
Chapter Two – Introduction to Structured Query Language
Page 2-83
Figure 2-49 - Column Characteristics for the MDC Database INVOICE Table
Figure 2-50 - Column Characteristics for the MDC Database INVOICE_ITEM Table
Figure 2-51 - Sample Data for the MDC Database CUSTOMER table
Chapter Two – Introduction to Structured Query Language
Chapter Two – Introduction to Structured Query Language
Chapter Two – Introduction to Structured Query Language
Chapter Two – Introduction to Structured Query Language
Chapter Two – Introduction to Structured Query Language
$100.00.
Solutions to Marcia’s Dry Cleaning questions are contained in the Microsoft Access database
DBP-e15-IM-CH02-MDC.accdb and in the corresponding files for Oracle Database, SQL
Chapter Two – Introduction to Structured Query Language
Page 2-89
E. List the LastName, FirstName, and Phone of all customers whose first name starts
with 'B'.
Solutions to Marcia’s Dry Cleaning questions are contained in the Microsoft Access database
DBP-e15-IM-CH02-MDC.accdb and in the corresponding files for Oracle Database, SQL
Server, and MySQL, which are all available at the Instructor’s Resource Center on the text’s
Web site (www.pearsonhighered.com/kroenke).
The correct SQL-92 statement for Oracle Database, SQL Server, and MySQL, which uses the
wildcard %, is:
/* *** SQL-Query-MDC-E *** */
SELECT LastName, FirstName, Phone
FROM CUSTOMER
WHERE FirstName LIKE 'B%';
/* *** SQL-Query-MDC-E-Access *** */
However, Microsoft Access uses the wildcard *, which gives the following SQL statement:
/* *** SQL-Query-MDC-E-Access *** */
SELECT LastName, FirstName, Phone
FROM CUSTOMER
WHERE FirstName LIKE 'B*';
F. List the LastName, FirstName, and Phone of all customers whose last name includes
the characters 'cat'.
Chapter Two – Introduction to Structured Query Language
Page 2-90
Solutions to Marcia’s Dry Cleaning questions are contained in the Microsoft Access database
DBP-e15-IM-CH02-MDC.accdb and in the corresponding files for Oracle Database, SQL
Server, and MySQL, which are all available at the Instructor’s Resource Center on the text’s
Web site (www.pearsonhighered.com/kroenke).
Note that LIKE comparisons will not always work the way you expect: You need to
understand when the comparisons are case-sensitive and when they are not. Before running
any query involving LIKE, run a small test query to determine whether your DBMS as
configured by your DBA is comparing with case sensitivity or not. If you are using Oracle
Database, MySQL, or SQL Server, there are ways to force a LIKE comparison to be case-
sensitive or case-insensitive; those details are beyond the scope of this text. Microsoft
Access, by default, is case-insensitive. To do a case-sensitive LIKE comparison in Microsoft
Access, use the “instr” function instead of “LIKE” (see DBP-e15-IM-CH02-MDC.accdb for
the solution).
The previous paragraph explains why, in general, you may get different results than those
presented below for Access (the Access results are for a default, case-insensitive query). If
you are using a DBMS in which the comparisons are case-sensitive, then only the first row in
the results below will appear.
The correct SQL-92 statement, for Oracle Database, MySQL, and SQL Server, which uses
the wildcard %, is:
/* *** SQL-Query-MDC-F *** */
SELECT LastName, FirstName, Phone
FROM CUSTOMER
WHERE LastName LIKE '%cat%';
However, Microsoft Access uses the wildcard *, which gives the following SQL statement:
/* *** SQL-Query-MDC-F-Access *** */
SELECT LastName, FirstName, Phone
FROM CUSTOMER
WHERE LastName LIKE '*cat*';
G. List the LastName, FirstName, and Phone for all customers whose second and third
digits (from the left) of their phone number are 23. For example, any phone number
with an area code of ‘723’ would meet the criteria.
Chapter Two – Introduction to Structured Query Language
Solutions to Marcia’s Dry Cleaning questions are contained in the Microsoft Access database
DBP-e15-IM-CH02-MDC.accdb and in the corresponding files for Oracle Database, SQL
Server, and MySQL, which are all available at the Instructor’s Resource Center on the text’s
Web site (www.pearsonhighered.com/kroenke).
Note that since the phone numbers in this database include the area code, we are really
finding phone numbers with ‘23’ as the second and third numbers in the area code. We
could, of course, write statements to find ‘23’ in the prefix or in the 4-digit sequence portion
of the phone number.
The correct SQL-92 statement, which uses the wildcards % and _, is:
/* *** SQL-Query-MDC-G *** */
SELECT LastName, FirstName, Phone
FROM CUSTOMER
WHERE Phone LIKE '_23%';
However, Microsoft Access uses the wildcards * and ?, which give the following SQL
statement:
/* *** SQL-Query-MDC-G-Access *** */
SELECT LastName, FirstName, Phone
FROM CUSTOMER
WHERE Phone LIKE '?23*';
H. Determine the maximum and minimum TotalAmount.
Solutions to Marcia’s Dry Cleaning questions are contained in the Microsoft Access database
DBP-e15-IM-CH02-MDC.accdb and in the corresponding files for Oracle Database, SQL
Chapter Two – Introduction to Structured Query Language
Page 2-92
MIN (TotalAmt) AS MinTotalAmount
FROM INVOICE;
I. Determine the average TotalAmount.
Solutions to Marcia’s Dry Cleaning questions are contained in the Microsoft Access database
DBP-e15-IM-CH02-MDC.accdb and in the corresponding files for Oracle Database, SQL
Server, and MySQL, which are all available at the Instructor’s Resource Center on the text’s
Web site (www.pearsonhighered.com/kroenke).
/* *** SQL-Query-MDC-I *** */
SELECT AVG (TotalAmt) AS AvgTotalAmount
FROM INVOICE;
J. Count the number of customers.
Solutions to Marcia’s Dry Cleaning questions are contained in the Microsoft Access database
DBP-e15-IM-CH02-MDC.accdb and in the corresponding files for Oracle Database, SQL
Server, and MySQL, which are all available at the Instructor’s Resource Center on the text’s
Web site (www.pearsonhighered.com/kroenke).
/* *** SQL-Query-MDC-J *** */
SELECT Count (*)AS NumberOfCustomers
FROM CUSTOMER;
K. Group customers by LastName and then by FirstName.
Solutions to Marcia’s Dry Cleaning questions are contained in the Microsoft Access database
DBP-e15-IM-CH02-MDC.accdb and in the corresponding files for Oracle Database, SQL
Chapter Two – Introduction to Structured Query Language
Page 2-93
Server, and MySQL, which are all available at the Instructor’s Resource Center on the text’s
Web site (www.pearsonhighered.com/kroenke).
/* *** SQL-Query-MDC-K *** */
SELECT LastName, FirstName
FROM CUSTOMER
GROUP BY LastName, FirstName;
L. Count the number of customers having each combination of LastName and
FirstName.
Solutions to Marcia’s Dry Cleaning questions are contained in the Microsoft Access database
DBP-e15-IM-CH02-MDC.accdb and in the corresponding files for Oracle Database, SQL
Server, and MySQL, which are all available at the Instructor’s Resource Center on the text’s
Web site (www.pearsonhighered.com/kroenke).
/* *** SQL-Query-MDC-L *** */
SELECT LastName, FirstName,
COUNT (*) AS Last_First_Combination_Count
FROM CUSTOMER
GROUP BY LastName, FirstName;
M. Show the LastName, FirstName, and Phone of all customers who have had an order
with TotalAmount greater than $100.00. Use a subquery. Present the results sorted
by LastName in ascending order and then FirstName in descending order.
Chapter Two – Introduction to Structured Query Language
Page 2-94
Solutions to Marcia’s Dry Cleaning questions are contained in the Microsoft Access database
DBP-e15-IM-CH02-MDC.accdb and in the corresponding files for Oracle Database, SQL
Server, and MySQL, which are all available at the Instructor’s Resource Center on the text’s
Web site (www.pearsonhighered.com/kroenke).
/* *** SQL-Query-MDC-M *** */
SELECT LastName, FirstName, Phone
FROM CUSTOMER
WHERE CustomerID IN
(SELECT CustomerID
FROM INVOICE
WHERE TotalAmount > 100)
ORDER BY LastName, FirstName DESC;
N. Show the LastName, FirstName and Phone of all customers who have had an order
with TotalAmount greater than $100.00. Use a join, but do not use JOIN ON syntax.
Present results sorted by LastName in ascending order and then FirstName in
descending order.
Solutions to Marcia’s Dry Cleaning questions are contained in the Microsoft Access database
DBP-e15-IM-CH02-MDC.accdb and in the corresponding files for Oracle Database, SQL
Server, and MySQL, which are all available at the Instructor’s Resource Center on the text’s
Web site (www.pearsonhighered.com/kroenke).
/* *** SQL-Query-MDC-N *** */
SELECT LastName, FirstName, Phone
FROM CUSTOMER, INVOICE
WHERE CUSTOMER.CustomerID = INVOICE.CustomerID
AND TotalAmount > 100
ORDER BY LastName, FirstName DESC;
Chapter Two – Introduction to Structured Query Language
O. Show the LastName, FirstName and Phone of all customers who have had an order
with TotalAmount greater than $100.00. Use a join using JOIN ON syntax. Present
results sorted by LastName in ascending order and then FirstName in descending
order.
Solutions to Marcia’s Dry Cleaning questions are contained in the Microsoft Access database
DBP-e15-IM-CH02-MDC.accdb and in the corresponding files for Oracle Database, SQL
Server, and MySQL, which are all available at the Instructor’s Resource Center on the text’s
Web site (www.pearsonhighered.com/kroenke).
/* *** SQL-Query-MDC-O *** */
SELECT CUSTOMER.LastName, CUSTOMER.FirstName, CUSTOMER.Phone
FROM CUSTOMER JOIN INVOICE
ON CUSTOMER.CustomerID = INVOICE.CustomerID
WHERE INVOICE.TotalAmount>100
ORDER BY LastName, FirstName DESC;
Note that for Microsoft Access, we must use the INNER JOIN syntax:
/* *** SQL-Query-MDC-O *** */
SELECT CUSTOMER.LastName, CUSTOMER.FirstName, CUSTOMER.Phone
FROM CUSTOMER INNER JOIN INVOICE
ON CUSTOMER.CustomerID = INVOICE.CustomerID
WHERE INVOICE.TotalAmount>100
ORDER BY LastName, FirstName DESC;
P. Show the LastName, FirstName and Phone of all customers who have had an order
with an Item named “Dress Shirt”. Use a subquery. Present results sorted by
LastName in ascending order and then FirstName in descending order.
Solutions to Marcia’s Dry Cleaning questions are contained in the Microsoft Access database
DBP-e15-IM-CH02-MDC.accdb and in the corresponding files for Oracle Database, SQL
Chapter Two – Introduction to Structured Query Language
Page 2-96
/* *** SQL-Query-MDC-P *** */
SELECT LastName, FirstName, Phone
FROM CUSTOMER
WHERE CustomerID IN
(SELECT CustomerID
FROM INVOICE
WHERE InvoiceNumber IN
(SELECT InvoiceNumber
FROM INVOICE_ITEM
WHERE Item = 'Dress Shirt'))
ORDER BY LastName, FirstName DESC;
Q. Show the LastName, FirstName and Phone of all customers who have had an order
with an Item named “Dress Shirt”. Use a join, but do not use JOIN ON syntax.
Present results sorted by LastName in ascending order and then FirstName in
descending order.
Solutions to Marcia’s Dry Cleaning questions are contained in the Microsoft Access database
DBP-e14-IM-CH02-MDC.accdb and in the corresponding files for Oracle Database, SQL
Server, and MySQL, which are all available at the Instructor’s Resource Center on the text’s
Web site (www.pearsonhighered.com/kroenke).
/* *** SQL-Query-MDC-Q-Access *** */
SELECT LastName, FirstName, Phone
FROM CUSTOMER, INVOICE, INVOICE_ITEM
WHERE CUSTOMER.CustomerID = INVOICE.CustomerID
AND INVOICE.InvoiceNumber = INVOICE_ITEM.InvoiceNumber
AND INVOICE_ITEM.Item = 'Dress Shirt'
ORDER BY LastName, FirstName DESC;
Chapter Two – Introduction to Structured Query Language
Page 2-97
R. Show the LastName, FirstName and Phone of all customers who have had an order
with an Item named “Dress Shirt”. Use a join using JOIN ON syntax. Present
results sorted by LastName in ascending order and then FirstName in descending
order.
Solutions to Marcia’s Dry Cleaning questions are contained in the Microsoft Access database
DBP-e15-IM-CH02-MDC.accdb and in the corresponding files for Oracle Database, SQL
Server, and MySQL, which are all available at the Instructor’s Resource Center on the text’s
Web site (www.pearsonhighered.com/kroenke).
For Oracle Database, SQL Server, and MySQL:
/* *** SQL-Query-MDC-R *** */
SELECT CUSTOMER.LastName, CUSTOMER.FirstName,
CUSTOMER.Phone
FROM (CUSTOMER JOIN INVOICE
ON CUSTOMER.CustomerID = INVOICE.CustomerID)
JOIN INVOICE_ITEM
ON INVOICE.InvoiceNumber = INVOICE_ITEM.InvoiceNumber
WHERE INVOICE_ITEM.Item='Dress Shirt'
ORDER BY LastName, FirstName DESC;
Note that for Microsoft Access, we must use the INNER JOIN syntax:
/* *** SQL-Query-MDC-R-Access *** */
SELECT CUSTOMER.LastName, CUSTOMER.FirstName,
CUSTOMER.Phone
FROM (CUSTOMER INNER JOIN INVOICE
ON CUSTOMER.CustomerID = INVOICE.CustomerID)
INNER JOIN INVOICE_ITEM
ON INVOICE.InvoiceNumber = INVOICE_ITEM.InvoiceNumber
WHERE INVOICE_ITEM.Item ='Dress Shirt'
ORDER BY LastName, FirstName DESC;
Chapter Two – Introduction to Structured Query Language
Chapter Two – Introduction to Structured Query Language
Chapter Two – Introduction to Structured Query Language
Chapter Two – Introduction to Structured Query Language
Chapter Two – Introduction to Structured Query Language
Page 2-102
❖ ANSWERS TO THE QUEEN ANNE CURIOSITY SHOP PROJECT QUESTIONS
The Queen Anne Curiosity Shop is an upscale home furnishings store in a well-to-do urban
neighborhood. It sells both antiques and current-production household items that complement or
are useful with the antiques. For example, the store sells antique dining room tables and new
tablecloths. The antiques are purchased from both individuals and wholesalers, and the new
items are purchased from distributors. The store’s customers include individuals, owners of bed-
and-breakfast operations, and local interior designers who work with both individuals and small
businesses. The antiques are unique, though some multiple items, such as dining room chairs,
may be available as a set (sets are never broken). The new items are not unique, and an item
may be reordered if it is out of stock. New items are also available in various sizes and colors
(for example, a particular style of tablecloth may be available in several sizes and in a variety of
colors).
Assume that The Queen Anne Curiosity Shop designs a database with the following tables:
CUSTOMER (CustomerID, LastName, FirstName, EmailAddress, EncryptedPassword,
Address, City, State, ZIP, Phone, ReferredBy)
ITEM (ItemID, ItemDescription, CompanyName, PurchaseDate, ItemCost,
ItemPrice)
SALE (SaleID, CustomerID, SaleDate, SubTotal, Tax, Total)
SALE_ITEM (SaleID, SaleItemID, ItemID, ItemPrice)
The referential integrity constraints are:
ReferredBy in CUSTOMER must exist in CustomerID in CUSTOMER
CustomerID in SALE must exist in CustomerID in CUSTOMER
SaleID in SALE_ITEM must exist in SaleID in SALE
ItemID in SALE_ITEM must exist in ItemID in ITEM
Assume that CustomerID of CUSTOMER, ItemID of ITEM, SaleID of SALE, and SaleItemID of
SALE_ITEM are all surrogate keys with values as follows:
CustomerID Start at 1 Increment by 1
ItemID Start at 1 Increment by 1
SaleID Start at 1 Increment by 1
The database that The Queen Anne Curiosity Shop has created is named QACS, and the four
tables in the QACS database schema are shown in Figure 2-54. Note that CUTOMER contains
a recursive relationship between ReferredBy and CustomerID, where ReferredBy contains the
CustomerID value of the existing customer who referred the new customer to the Queen Anne
Curiosity Shop.
Chapter Two – Introduction to Structured Query Language
Page 2-103
Figure 2-54 – The QACS Database
The column characteristics for the tables are shown in Figures 2-55, 2-56, 2-57, and 2-58. The
relationships CUSTOMER-to-SALE and ITEM-to-SALE_ITEM should enforce referential
integrity, but not cascade updates or deletions, whereas the relationship between SALE and
SALE_ITEM should enforce referential integrity and cascade both updates and deletions. The
data for these tables are shown in Figures 2-59, 2-60, 2-61, and 2-62.
Figure 2-55 - Column Characteristics for the QACS Database CUSTOMER Table
Chapter Two – Introduction to Structured Query Language
Page 2-104
Figure 2-56 - Column Characteristics for the QACS Database SALE Table
Figure 2-57 - Column Characteristics for the QACS Database SALE_ITEM Table
Figure 2-58 - Column Characteristics for the QACS Database ITEM Table
Chapter Two – Introduction to Structured Query Language
Page 2-105
Figure 2-59 – Sample Data for the QACS Database CUSTOMER Table
Chapter Two – Introduction to Structured Query Language
Page 2-106
Figure 2-60 - Sample Data for the QACS Database SALE Table
Chapter Two – Introduction to Structured Query Language
Page 2-107
Figure 2-61 - Sample Data for the QACS Database SALE_ITEM Table
Chapter Two – Introduction to Structured Query Language
Chapter Two – Introduction to Structured Query Language
Chapter Two – Introduction to Structured Query Language
Chapter Two – Introduction to Structured Query Language
Chapter Two – Introduction to Structured Query Language
$100.00.
Solutions to The Queen Anne Curiosity Shop questions are contained in the Microsoft Access
database DBP-e15-IM-CH02-QACS.accdb and in corresponding files for SQL Server, Oracle
Chapter Two – Introduction to Structured Query Language
Page 2-114
E. List the LastName, FirstName, and Phone of all customers whose first name starts
with 'D'.
Solutions to The Queen Anne Curiosity Shop questions are contained in the Microsoft Access
database DBP-e15-IM-CH02-QACS.accdb and in corresponding files for SQL Server, Oracle
Database, and MySQL, which are all available in the Instructor’s Resource Center on the text’s
Web site (www.pearsonhighered.com/kroenke).
For SQL Server, Oracle Database, and MySQL:
/* *** SQL-Query-QACS-E *** */
SELECT LastName, FirstName, Phone
FROM CUSTOMER
WHERE FirstName LIKE 'D%';
For Microsoft Access:
/* *** SQL-Query-QACS-E *** */
SELECT LastName, FirstName, Phone
FROM CUSTOMER
WHERE FirstName LIKE 'D*';
Chapter Two – Introduction to Structured Query Language
Page 2-115
F. List the LastName, FirstName, and Phone of all customers whose last name includes
the characters 'ne'.
Solutions to The Queen Anne Curiosity Shop questions are contained in the Microsoft Access
database DBP-e15-IM-CH02-QACS.accdb and in corresponding files for SQL Server, Oracle
Database, and MySQL, which are all available in the Instructor’s Resource Center on the text’s
Web site (www.pearsonhighered.com/kroenke).
For SQL Server, Oracle Database, and MySQL:
/* *** SQL-Query-QACS-F *** */
SELECT LastName, FirstName, Phone
FROM CUSTOMER
WHERE LastName LIKE '%ne%';
For Microsoft Access:
/* *** SQL-Query-QACS-F *** */
SELECT LastName, FirstName, Phone
FROM CUSTOMER
WHERE LastName LIKE '*ne*';
G. List the LastName, FirstName, and Phone for all customers whose eighth and ninth
digits (starting from the left) of their phone number are 56. For example, a phone
number ending in “567” would meet the criteria.
Solutions to The Queen Anne Curiosity Shop questions are contained in the Microsoft Access
database DBP-e15-IM-CH02-QACS.accdb and in corresponding files for SQL Server, Oracle
Database, and MySQL, which are all available in the Instructor’s Resource Center on the text’s
Web site (www.pearsonhighered.com/kroenke).
For SQL Server, Oracle Database, and MySQL:
/* *** SQL-Query-QACS-G *** */
SELECT LastName, FirstName, Phone
FROM CUSTOMER
WHERE Phone LIKE '%56_';
For Microsoft Access:
Chapter Two – Introduction to Structured Query Language
Page 2-116
/* *** SQL-Query-QACS-G *** */
SELECT LastName, FirstName, Phone
FROM CUSTOMER
WHERE Phone LIKE '*56?';
H. Determine the maximum and minimum sales Total.
Solutions to The Queen Anne Curiosity Shop questions are contained in the Microsoft Access
database DBP-e15-IM-CH02-QACS.accdb and in corresponding files for SQL Server, Oracle
Database, and MySQL, which are all available in the Instructor’s Resource Center on the text’s
Web site (www.pearsonhighered.com/kroenke).
/* *** SQL-Query-QACS-H *** */
SELECT MAX (Total) as MaximumTotalSales,
MIN (Total) as MinimumTotalSales
FROM SALE;
I. Determine the average sales Total.
Solutions to The Queen Anne Curiosity Shop questions are contained in the Microsoft Access
database DBP-e15-IM-CH02-QACS.accdb and in corresponding files for SQL Server, Oracle
Database, and MySQL, which are all available in the Instructor’s Resource Center on the text’s
Web site (www.pearsonhighered.com/kroenke).
/* *** SQL-Query-QACS-I *** */
SELECT AVG (Total) as AverageTotalSales
FROM SALE;
Chapter Two – Introduction to Structured Query Language
Page 2-117
J. Count the number of customers.
Solutions to The Queen Anne Curiosity Shop questions are contained in the Microsoft Access
database DBP-e15-IM-CH02-QACS.accdb and in corresponding files for SQL Server, Oracle
Database, and MySQL, which are all available in the Instructor’s Resource Center on the text’s
Web site (www.pearsonhighered.com/kroenke).
/* *** SQL-Query-QACS-J *** */
SELECT COUNT (*) AS NumberOfCustomers
FROM CUSTOMER;
K. Group customers by LastName and then by FirstName.
Solutions to The Queen Anne Curiosity Shop questions are contained in the Microsoft Access
database DBP-e15-IM-CH02-QACS.accdb and in corresponding files for SQL Server, Oracle
Database, and MySQL, which are all available in the Instructor’s Resource Center on the text’s
Web site (www.pearsonhighered.com/kroenke).
/* *** SQL-Query-QACS-K *** */
SELECT LastName, FirstName
FROM CUSTOMER
GROUP BY LastName, FirstName;
Chapter Two – Introduction to Structured Query Language
Page 2-118
L. Count the number of customers having each combination of LastName and FirstName.
Solutions to The Queen Anne Curiosity Shop questions are contained in the Microsoft Access
database DBP-e15-IM-CH02-QACS.accdb and in corresponding files for SQL Server, Oracle
Database, and MySQL, which are all available in the Instructor’s Resource Center on the text’s
Web site (www.pearsonhighered.com/kroenke).
/* *** SQL-Query-QACS-L *** */
SELECT LastName, FirstName, COUNT (*) AS NumberOfCustomers
FROM CUSTOMER
GROUP BY LastName, FirstName;
M. Show the LastName, FirstName, and Phone of all customers who have had an order
with Total greater than $100.00. Use a subquery. Present the results sorted by
LastName in ascending order and then FirstName in descending order.
Solutions to The Queen Anne Curiosity Shop questions are contained in the Microsoft Access
database DBP-e15-IM-CH02-QACS.accdb and in corresponding files for SQL Server, Oracle
Database, and MySQL, which are all available in the Instructor’s Resource Center on the text’s
Web site (www.pearsonhighered.com/kroenke).
/* *** SQL-Query-QACS-M *** */
SELECT LastName, FirstName, Phone
FROM CUSTOMER
WHERE CustomerID IN
(SELECT CustomerID
FROM SALE
WHERE Total > 100)
ORDER BY LastName, FirstName DESC;
Chapter Two – Introduction to Structured Query Language
Page 2-119
N. Show the LastName, FirstName, and Phone of all customers who have had an order
with Total greater than $100.00. Use a join, but do not use JOIN ON syntax. Present
results sorted by LastName in ascending order and then FirstName in descending order.
Solutions to The Queen Anne Curiosity Shop questions are contained in the Microsoft Access
database DBP-e15-IM-CH02-QACS.accdb and in corresponding files for SQL Server, Oracle
Database, and MySQL, which are all available in the Instructor’s Resource Center on the text’s
Web site (www.pearsonhighered.com/kroenke).
/* *** SQL-Query-QACS-N *** */
SELECT LastName, FirstName, Phone
FROM CUSTOMER, SALE
WHERE CUSTOMER.CustomerID = SALE.CustomerID
AND Total > 100
ORDER BY LastName, FirstName DESC;
/* For each CUSTOMER only once: */
SELECT DISTINCT LastName, FirstName, Phone
FROM CUSTOMER, SALE
WHERE CUSTOMER.CustomerID = SALE.CustomerID
AND Total > 100
ORDER BY LastName, FirstName DESC;
Chapter Two – Introduction to Structured Query Language
Page 2-120
O. Show the LastName, FirstName, and Phone of all customers who have had an order
with Total greater than $100.00. Use a join using JOIN ON syntax. Present results
sorted by LastName in ascending order and then FirstName in descending order.
Solutions to The Queen Anne Curiosity Shop questions are contained in the Microsoft Access
database DBP-e15-IM-CH02-QACS.accdb and in corresponding files for SQL Server, Oracle
Database, and MySQL, which are all available in the Instructor’s Resource Center on the text’s
Web site (www.pearsonhighered.com/kroenke).
/* *** SQL-Query-QACS-O *** */
SELECT LastName, FirstName, Phone
FROM CUSTOMER JOIN SALE
ON CUSTOMER.CustomerID = SALE.CustomerID
WHERE Total > 100
ORDER BY LastName, FirstName DESC;
/* For each CUSTOMER only once: */
SELECT DISTINCT LastName, FirstName, Phone
FROM CUSTOMER JOIN SALE
ON CUSTOMER.CustomerID = SALE.CustomerID
WHERE Total > 100
ORDER BY LastName, FirstName DESC;
Note that for Microsoft Access, we must use the INNER JOIN syntax:
SELECT DISTINCT LastName, FirstName, Phone
FROM CUSTOMER INNER JOIN SALE
ON CUSTOMER.CustomerID = SALE.CustomerID
WHERE Total > 100
ORDER BY LastName, FirstName DESC;
P. Show the LastName, FirstName, and Phone of all customers who who have bought an
Item named 'Desk Lamp'. Use a subquery. Present results sorted by LastName in
ascending order and then FirstName in descending order.
Chapter Two – Introduction to Structured Query Language
Page 2-121
Solutions to The Queen Anne Curiosity Shop questions are contained in the Microsoft Access
database DBP-e15-IM-CH02-QACS.accdb and in corresponding files for SQL Server, Oracle
Database, and MySQL, which are all available in the Instructor’s Resource Center on the text’s
Web site (www.pearsonhighered.com/kroenke).
/* *** SQL-Query-QACS-P *** */
SELECT LastName, FirstName, Phone
FROM CUSTOMER
WHERE CustomerID IN
(SELECT CustomerID
FROM SALE
WHERE SaleID IN
(SELECT SaleID
FROM SALE_ITEM
WHERE ItemID IN
(SELECT ItemID
FROM ITEM
WHERE ItemDescription = 'Desk Lamp')))
ORDER BY LastName, FirstName DESC;
Q. Show the LastName, FirstName, and Phone of all customers who have bought an Item
named 'Desk Lamp'. Use a join, but do not use JOIN ON syntax. Present results sorted
by LastName in ascending order and then FirstName in descending order.
Solutions to The Queen Anne Curiosity Shop questions are contained in the Microsoft Access
database DBP-e15-IM-CH02-QACS.accdb and in corresponding files for SQL Server, Oracle
Database, and MySQL, which are all available in the Instructor’s Resource Center on the text’s
Web site (www.pearsonhighered.com/kroenke).
For SQL Server, MySQL, and Microsoft Access:
/* *** SQL-Query-QACS-Q *** */
SELECT LastName, FirstName, Phone
FROM CUSTOMER AS C,
SALE AS S,
SALE_ITEM AS SI,
ITEM AS I
WHERE C.CustomerID = S.CustomerID
AND S.SaleID = SI.SaleID
AND SI.ItemID = I.ItemID
AND ItemDescription = 'Desk Lamp'
ORDER BY LastName, FirstName DESC;
Chapter Two – Introduction to Structured Query Language
Page 2-122
For Oracle Database, which doesn’t allow “AS” in alias (range variable) declarations:
/* *** SQL-Query-QACS-Q-Oracle *** */
SELECT LastName, FirstName, Phone
FROM CUSTOMER C,
SALE S,
SALE_ITEM SI,
ITEM I
WHERE C.CustomerID = S.CustomerID
AND S.SaleID = SI.SaleID
AND SI.ItemID = I.ItemID
AND ItemDescription = 'Desk Lamp'
ORDER BY LastName, FirstName DESC;
R. Show the LastName, FirstName, and Phone of all customers who have bought an Item
named 'Desk Lamp'. Use a join using JOIN ON syntax. Present results sorted by
LastName in ascending order and then FirstName in descending order.
Solutions to The Queen Anne Curiosity Shop questions are contained in the Microsoft Access
database DBP-e15-IM-CH02-QACS.accdb and in corresponding files for SQL Server, Oracle
Database, and MySQL, which are all available in the Instructor’s Resource Center on the text’s
Web site (www.pearsonhighered.com/kroenke).
For MySQL and SQL Server:
/* *** SQL-Query-QACS-R *** */
SELECT LastName, FirstName, Phone
FROM CUSTOMER AS C JOIN SALE AS S
ON C.CustomerID = S.CustomerID
JOIN SALE_ITEM AS SI
ON S.SaleID = SI.SaleID
JOIN ITEM AS I
ON SI.ItemID = I.ItemID
WHERE ItemDescription = 'Desk Lamp'
ORDER BY LastName, FirstName DESC;
For Oracle, which does not allow “AS” in alias declarations:
Chapter Two – Introduction to Structured Query Language
Page 2-123
/* *** SQL-Query-QACS-R *** */
SELECT LastName, FirstName, Phone
FROM CUSTOMER C JOIN SALE S
ON C.CustomerID = S.CustomerID
JOIN SALE_ITEM SI
ON S.SaleID = SI.SaleID
JOIN ITEM I
ON SI.ItemID = I.ItemID
WHERE ItemDescription = 'Desk Lamp'
ORDER BY LastName, FirstName DESC;
Note that for Microsoft Access, we must use the INNER JOIN syntax with grouping of the
INNER JOINS:
SELECT LastName, FirstName, Phone
FROM ((CUSTOMER AS C INNER JOIN SALE AS S
ON C.CustomerID = S.CustomerID)
INNER JOIN SALE_ITEM AS SI
ON S.SaleID = SI.SaleID)
INNER JOIN ITEM AS I
ON SI.ItemID = I.ItemID
WHERE ItemDescription = 'Desk Lamp'
ORDER BY LastName, FirstName DESC;
S. Show the LastName, FirstName, and Phone of all customers who have bought an Item
named 'Desk Lamp'. Use a combination of a join in JOIN ON syntax and a subquery.
Present results sorted by LastName in ascending order and then FirstName in
descending order.
Solutions to The Queen Anne Curiosity Shop questions are contained in the Microsoft Access
database DBP-e15-IM-CH02-QACS.accdb and in corresponding files for SQL Server, Oracle
Database, and MySQL, which are all available in the Instructor’s Resource Center on the text’s
Web site (www.pearsonhighered.com/kroenke).
For SQL Server and MySQL:
Chapter Two – Introduction to Structured Query Language
Page 2-124
/* *** SQL-Query-QACS-S *** */
SELECT LastName, FirstName, Phone
FROM CUSTOMER AS C JOIN SALE AS S
ON C.CustomerID = S.CustomerID
WHERE SaleID IN
(SELECT SaleID
FROM SALE_ITEM
WHERE ItemID IN
(SELECT ItemID
FROM ITEM
WHERE ItemDescription = 'Desk Lamp'))
ORDER BY LastName, FirstName DESC;
For Oracle Database, which disallows “AS” in alias declarations:
/* *** SQL-Query-QACS-S *** */
SELECT LastName, FirstName, Phone
FROM CUSTOMER C JOIN SALE S
ON C.CustomerID = S.CustomerID
WHERE SaleID IN
(SELECT SaleID
FROM SALE_ITEM
WHERE ItemID IN
(SELECT ItemID
FROM ITEM
WHERE ItemDescription = 'Desk Lamp'))
ORDER BY LastName, FirstName DESC;
For Microsoft Access, which requires “INNER” in the join syntax:
/* *** SQL-Query-QACS-S *** */
SELECT LastName, FirstName, Phone
FROM CUSTOMER AS C INNER JOIN SALE AS S
ON C.CustomerID = S.CustomerID
WHERE SaleID IN
(SELECT SaleID
FROM SALE_ITEM
WHERE ItemID IN
(SELECT ItemID
FROM ITEM
WHERE ItemDescription = 'Desk Lamp'))
ORDER BY LastName, FirstName DESC;
Chapter Two – Introduction to Structured Query Language
Page 2-125
T. Show the LastName, FirstName, and Phone of all customers who have bought an Item
named 'Desk Lamp'. Use a combination of a join in JOIN ON syntax and a subquery that
is different from the combination used for question S. Present results sorted by
LastName in ascending order and then FirstName in descending order.
Solutions to The Queen Anne Curiosity Shop questions are contained in the Microsoft Access
database DBP-e15-IM-CH02-QACS.accdb and in corresponding files for SQL Server, Oracle
Database, and MySQL, which are all available in the Instructor’s Resource Center on the text’s
Web site (www.pearsonhighered.com/kroenke).
For MySQL and SQL Server:
/* *** SQL-Query-QACS-T *** */
SELECT LastName, FirstName, Phone
FROM CUSTOMER AS C JOIN SALE AS S ON C.CustomerID = S.CustomerID
JOIN SALE_ITEM AS SI ON S.SaleID = SI.SaleID
WHERE ItemID IN
(SELECT ItemID
FROM ITEM AS I
WHERE ItemDescription = 'Desk Lamp')
ORDER BY LastName, FirstName DESC;
For Oracle Database, which does not allow “AS” in alias declarations:
/* *** SQL-Query-QACS-T *** */
SELECT LastName, FirstName, Phone
FROM CUSTOMER C JOIN SALE S ON C.CustomerID = S.CustomerID
JOIN SALE_ITEM SI ON S.SaleID = SI.SaleID
WHERE ItemID IN
(SELECT ItemID
FROM ITEM I
WHERE ItemDescription = 'Desk Lamp')
ORDER BY LastName, FirstName DESC;
For Microsoft Access, which requires “INNER” in join syntax and parenthesization of multiple
joins performed using JOIN syntax:
/* *** SQL-Query-QACS-T *** */
SELECT LastName, FirstName, Phone
FROM (CUSTOMER AS C INNER JOIN SALE AS S ON C.CustomerID = S.CustomerID)
INNER JOIN SALE_ITEM AS SI ON S.SaleID = SI.SaleID
WHERE ItemID IN
(SELECT ItemID
FROM ITEM AS I
WHERE ItemDescription = 'Desk Lamp')
ORDER BY LastName, FirstName DESC;
Chapter Two – Introduction to Structured Query Language
Page 2-126
U. Show the LastName, FirstName, Phone, and ItemDescription for customers who have
bought an Item named 'Desk Lamp'. Also show the LastName, FirstName, and Phone of
all the other customers. Present results sorted by Item in ascending order, then
LastName in ascending order, and then FirstName in descending order. HINT: In
Microsoft Access 2016 you will either need to use a UNION statement or a sequence of
two queries to solve this, because Microsoft Access disallows nesting an INNER join
inside a LEFT or RIGHT join. The other DBMS products can do it with one query (not a
UNION statement).
Solutions to The Queen Anne Curiosity Shop questions are contained in the Microsoft Access
database DBP-e15-IM-CH02-QACS.accdb and in corresponding files for SQL Server, Oracle
Database, and MySQL, which are all available in the Instructor’s Resource Center on the text’s
Web site (www.pearsonhighered.com/kroenke).
Note that this is a very challenging question! The best solution involves adding the ‘Desk Lamp’
restriction to the inner JOINs before performing the LEFT JOIN, otherwise (if we put the ‘Desk
Lamp’ restriction in the WHERE clause) every customer will have a sale so the LEFT JOIN will
not produce any NULLs, and we will get an incorrect result from the query. Examples of this are
not covered in the text, but at the same time, the text does not say you can’t do it either.
The LEFT JOIN solution for Oracle Database, MySQL, and SQL Server:
SELECT LastName, FirstName, Phone, ItemDescription
FROM CUSTOMER LEFT JOIN (SALE
JOIN SALE_ITEM
ON SALE.SaleID = SALE_ITEM.SaleID
JOIN ITEM
ON SALE_ITEM.ItemID = ITEM.ItemID
AND ITEM.ItemDescription = 'Desk Lamp')
ON CUSTOMER.CustomerID = SALE.CustomerID
ORDER BY ItemDescription, LastName, FirstName DESC;
Note that Microsoft Access does not allow nesting an INNER JOIN inside a LEFT or RIGHT
JOIN. It also disallows adding the non-join condition to the “ON” clause. So in order to create a
solution in Access, we must either (1) use a more complicated version of the query with a
UNION but without an OUTER JOIN or (2) create and save an intermediate query (view) to be
used in the final query. Note that these two approaches will also work with Oracle, SQL Server,
or MySQL.
Chapter Two – Introduction to Structured Query Language
Page 2-127
/* *** SQL-Query-QACS-U-UNION *** */
SELECT LastName, FirstName, Phone, ItemDescription
FROM CUSTOMER C, SALE S, SALE_ITEM SI, ITEM I
WHERE C.CustomerID = S.CustomerID
AND S.SaleID = SI.SaleID
AND SI.ItemID = I.ItemID
AND ItemDescription = 'Desk Lamp'
UNION
SELECT LastName, FirstName, Phone, NULL
FROM CUSTOMER
WHERE CustomerID NOT IN
(SELECT CustomerID FROM SALE
WHERE SaleID IN
(SELECT SaleID FROM SALE_ITEM
WHERE ItemID IN
(SELECT ItemID FROM ITEM
WHERE ItemDescription = 'Desk Lamp')))
ORDER BY ItemDescription, LastName, FirstName DESC;
The other approach using Access involves writing and saving an intermediate query (also
called a “view”; see Chapter 7). We first write and save a query that produces the
CustomerNumber and ItemDescription for all sales involving a ‘Desk Lamp’:
/* *** SQL-Query-QACS-U-Temp *** */
SELECT CustomerID, ItemDescription
FROM SALE AS S, SALE_ITEM AS SI, ITEM AS I
WHERE S.SaleID = SI.SaleID
AND SI.ItemID = I.ItemID
AND ItemDescription = 'Desk Lamp';
Now we can use that temporary query as if it were just another table to produce the final
result:
/* *** SQL-Query-QACS-U-Final *** */
SELECT LastName, FirstName, Phone, ItemDescription
FROM CUSTOMER C LEFT OUTER JOIN [SQL-Query-QACS-U-TEMP] T
ON C.CustomerID = T.CustomerID
ORDER BY ItemDescription, LastName, FirstName DESC;
The results below are the same for all correct versions of this query, with the possible
exception of where the NULL ItemDescriptions are presented: In Access, NULL comes
before all values; in Oracle, it comes last, etc.
Chapter Two – Introduction to Structured Query Language
Chapter Two – Introduction to Structured Query Language
❖ ANSWERS TO MORGAN IMPORTING PROJECT QUESTIONS
James Morgan owns and operates Morgan Importing, which purchases antiques and home
furnishings in Asia, ships those items to a warehouse facility in Los Angeles, and then sells
these items in the United States. James tracks the Asian purchases and subsequent shipments
of these items to Los Angeles by using a database to keep a list of items purchased, shipments
of the purchased items, and the items in each shipment. His database includes the following
tables:
ITEM (ItemID, Description, PurchaseDate, Store, City, Quantity, LocalCurrencyAmount,
ExchangeRate)
SHIPMENT (ShipmentID, ShipperName, ShipperInvoiceNumber, DepartureDate, ArrivalDate,
InsuredValue)
SHIPMENT_ITEM (ShipmentID, ShipmentItemID, ItemID, Value)
In the database schema, the primary keys are underlined and the foreign keys are shown in
italics. The database that James has created is named MI, and the three tables in the MI
database schema are shown in Figure 2-63.
Figure 2-63 – The MI Database
The column characteristics for the tables are shown in Figures 2-64, 2-65, and 2-66. The data
for the tables are shown in Figures 2-67, 2-68, and 2-69. The relationship between ITEM and
SHIPMENT_ITEM should enforce referential integrity, and although it should cascade updates,
it should not cascade deletions. The relationship between SHIPMENT and SHIPMENT_ITEM
should enforce referential integrity and cascade both updates and deletions.
You will need to create and set up a database named MI_CH02 for use with the Morgan
Importing case questions. A Microsoft Access 2016 database named MI_CH02.accdb and
Chapter Two – Introduction to Structured Query Language
Page 2-130
product you are using:
■
For Microsoft SQL Server 2017, see online Chapter 10A.
■
For Oracle Database 12c Release 2 or Oracle Express Edition 11g Release 2, see
online Chapter 10B.
■
For MySQL 5.7 Community Server, see online Chapter 10C.
Once you have set up your MI_CH02 database, create an SQL script named MI-CH02-
PQ.sql, and use it to record and store SQL statements that answer each of the following
questions (if the question requires a written answer, use an SQL comment to record your
answer):
Figure 2-64 - Column Characteristics for the MI Database ITEM Table
Chapter Two – Introduction to Structured Query Language
Page 2-131
Figure 2-65 - Column Characteristics for the MI Database SHIPMENT Table
Figure 2-66 - Column Characteristics for the MI Database SHIPMENT_ITEM Table
Figure 2-67 - Sample Data for the MI Database ITEM Table
Figure 2-68 - Sample Data for the MI Database SHIPMENT Table
Chapter Two – Introduction to Structured Query Language
Page 2-132
Figure 2-69 - Sample Data for the MI Database SHIPMENT_ITEM Table
A. Show all data in each of the tables.
Solutions to Morgan Importing questions are contained in the Microsoft Access database
DBP-e15-IM-CH02-MI.accdb and in the corresponding files for Oracle Database, MySQL,
and SQL Server, which are all available in the Instructor’s Resource Center on the text’s Web
site (www.pearsonhighered.com/kroenke).
/* *** SQL-Query-MI-A-ITEM *** */
SELECT *
FROM ITEM;
/* *** SQL-Query-MI-A-SHIPMENT *** */
SELECT *
FROM SHIPMENT;
/* *** SQL-Query-MI-A-SHIPMENT-ITEM *** */
SELECT *
FROM SHIPMENT_ITEM;
Chapter Two – Introduction to Structured Query Language
B. List the ShipmentID, ShipperName, and ShipperInvoiceNumber of all shipments.
Solutions to Morgan Importing questions are contained in the Microsoft Access database
DBP-e15-IM-CH02-MI.accdb and in the corresponding files for Oracle Database, MySQL,
and SQL Server, which are all available in the Instructor’s Resource Center on the text’s Web
site (www.pearsonhighered.com/kroenke).
/* *** SQL-Query-MI-B *** */
SELECT ShipmentID, ShipperName, ShipperInvoiceNumber
FROM SHIPMENT;
C. List the ShipmentID, ShipperName, and ShipperInvoiceNumber for all shipments that
have an insured value greater than $10,000.00.
Solutions to Morgan Importing questions are contained in the Microsoft Access database
DBP-e15-IM-CH02-MI.accdb and in the corresponding files for Oracle Database, MySQL,
Chapter Two – Introduction to Structured Query Language
Page 2-134
D. List the ShipmentID, ShipperName, and ShipperInvoiceNumber of all shippers
whose name starts with “AB”.
Solutions to Morgan Importing questions are contained in the Microsoft Access database
DBP-e15-IM-CH02-MI.accdb and in the corresponding files for Oracle Database, MySQL,
and SQL Server, which are all available in the Instructor’s Resource Center on the text’s Web
site (www.pearsonhighered.com/kroenke).
The correct SQL-92 statement, which uses the wildcard %, is:
/* *** SQL-Query-MI-D *** */
SELECT ShipmentID, ShipperName, ShipperInvoiceNumber
FROM SHIPMENT
WHERE ShipperName LIKE 'AB%';
However, Microsoft Access uses the wildcard *, which gives the following SQL statement:
/* *** SQL-Query-MI-D-Access *** */
SELECT ShipmentID, ShipperName, ShipperInvoiceNumber
FROM SHIPMENT
WHERE ShipperName LIKE 'AB*';
Chapter Two – Introduction to Structured Query Language
Page 2-135
E. List the ShipmentID, ShipperName, ShipperInvoiceNumber, and ArrivalDate of all
shipments that departed in December. HINT: For the DBMS you are using, research
how to extract a month or day from a date value so it can be compared to a number.
Solutions to Morgan Importing questions are contained in the Microsoft Access database
DBP-e15-IM-CH02-MI.accdb and in the corresponding files for Oracle Database, MySQL,
and SQL Server, which are all available in the Instructor’s Resource Center on the text’s Web
site (www.pearsonhighered.com/kroenke).
Microsoft Access stores dates as strings so we can use the wildcard *, which gives the
following SQL statement:
/* *** SQL-Query-MI-E-Access *** */
SELECT ShipmentID, ShipperName, ShipperInvoiceNumber, ArrivalDate
FROM SHIPMENT
WHERE DepartureDate LIKE '12*';
Oracle does not store date data type values as strings, so the following Oracle-specific form
of the query must be used to extract the month:
/* *** SQL-Query-MI-E-Oracle *** */
SELECT ShipmentID, ShipperName, ShipperInvoiceNumber, ArrivalDate
FROM SHIPMENT
WHERE EXTRACT (MONTH FROM DepartureDate) = 12;
MySQL and SQL Server also do not store date data type values as strings, so the following
form of the query must be used to extract the month. This version of the query also works
with Access:
/* *** SQL-Query-MI-E *** */
SELECT ShipmentID, ShipperName, ShipperInvoiceNumber, ArrivalDate
FROM SHIPMENT
WHERE MONTH (DepartureDate) = 12;
F. List the ShipmentID, ShipperName, ShipperInvoiceNumber, and ArrivalDate of all
shipments that departed on the tenth day of any month. HINT: For the DBMS you are
using, research how to extract a month or day from a date value so it can be
compared to a number.
Solutions to Morgan Importing questions are contained in the Microsoft Access database
DBP-e15-IM-CH02-MI.accdb and in the corresponding files for Oracle Database, MySQL,
Chapter Two – Introduction to Structured Query Language
Page 2-136
and SQL Server, which are all available in the Instructor’s Resource Center on the text’s Web
site (www.pearsonhighered.com/kroenke).
Microsoft Access stores dates as strings so we can use the wildcards * and ?, which give the
following SQL statement:
/* *** SQL-Query-MI-F-Access-A *** */
SELECT ShipmentID, ShipperName, ShipperInvoiceNumber, ArrivalDate
FROM SHIPMENT
WHERE DepartureDate LIKE '???10*';
Further, Microsoft Access does NOT show the leading zero in MM, so we must add a
compound WHERE clause to get months without the leading zeros:
/* *** SQL-Query-MI-F-Access-B *** */
SELECT ShipmentID, ShipperName, ShipperInvoiceNumber, ArrivalDate
FROM SHIPMENT
WHERE DepartureDate LIKE '???10*'
OR DepartureDate LIKE '??10*';
Oracle does not store date data type values as strings, so the following Oracle-specific form
of the query must be used to extract the day of the month:
/* *** SQL-Query-MI-F-Oracle *** */
SELECT ShipmentID, ShipperName, ShipperInvoiceNumber, ArrivalDate
FROM SHIPMENT
WHERE EXTRACT (DAY FROM DepartureDate) = 10;
MySQL and SQL Server also do not store date data type values as strings, so the following
form of the query must be used to extract the day of the month. This query also works in
Access:
/* *** SQL-Query-MI-F *** */
SELECT ShipmentID, ShipperName, ShipperInvoiceNumber, ArrivalDate
FROM SHIPMENT
WHERE DAY (DepartureDate) = 10;
Chapter Two – Introduction to Structured Query Language
G. Determine the maximum and minimum InsuredValue.
Solutions to Morgan Importing questions are contained in the Microsoft Access database
DBP-e15-IM-CH02-MI.accdb and in the corresponding files for Oracle Database, MySQL,
and SQL Server, which are all available in the Instructor’s Resource Center on the text’s Web
site (www.pearsonhighered.com/kroenke).
/* *** SQL-Query-MI-G *** */
SELECT MAX (InsuredValue) AS MaxInsuredValue,
MIN (InsuredValue) AS MinInsuredValue,
FROM SHIPMENT;
H. Determine the average InsuredValue.
Solutions to Morgan Importing questions are contained in the Microsoft Access database
DBP-e15-IM-CH02-MI.accdb and in the corresponding files for Oracle Database, MySQL,
and SQL Server, which are all available in the Instructor’s Resource Center on the text’s Web
site (www.pearsonhighered.com/kroenke).
/* *** SQL-Query-MI-H *** */
SELECT AVG (InsuredValue) AS AvgInsuredValue
FROM SHIPMENT;
I. Count the number of shipments.
Solutions to Morgan Importing questions are contained in the Microsoft Access database
DBP-e15-IM-CH02-MI.accdb and in the corresponding files for Oracle Database, MySQL,
Chapter Two – Introduction to Structured Query Language
J. Show ItemID, Description, Store, and a calculated column named
USCurrencyAmount that is equal to LocalCurrencyAmount multiplied by the
ExchangeRate for all rows of ITEM.
Solutions to Morgan Importing questions are contained in the Microsoft Access database
DBP-e15-IM-CH02-MI.accdb and in the corresponding files for Oracle Database, MySQL,
and SQL Server, which are all available in the Instructor’s Resource Center on the text’s Web
site (www.pearsonhighered.com/kroenke).
/* *** SQL-Query-MI-J *** */
SELECT ItemID, Description, Store,
LocalCurrencyAmount * ExchangeRate AS USCurrencyAmount
FROM ITEM;
K. Group item purchases by City and Store.
Solutions to Morgan Importing questions are contained in the Microsoft Access database
DBP-e15-IM-CH02-MI.accdb and in the corresponding files for Oracle Database, MySQL,
and SQL Server, which are all available in the Instructor’s Resource Center on the text’s Web
site (www.pearsonhighered.com/kroenke).
/* *** SQL-Query-MI-K *** */
SELECT City, Store
FROM ITEM
GROUP BY City, Store;
Chapter Two – Introduction to Structured Query Language
Chapter Two – Introduction to Structured Query Language
Chapter Two – Introduction to Structured Query Language
Chapter Two – Introduction to Structured Query Language
Chapter Two – Introduction to Structured Query Language
Chapter Two – Introduction to Structured Query Language
Chapter Two – Introduction to Structured Query Language