Chapter Two – Introduction to Structured Query Language
Page 2-81
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
Page 2-82
Figure 2-52 – Sample Data for the MDC Database INVOICE table
Chapter Two – Introduction to Structured Query Language
Page 2-83
Figure 2-53 – Sample Data for the MDC Database INVOICE_ITEM table
Once you have setup your MDC_CH02 database, create an SQL script name MDC-
CH02-CQ.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 and SQL comment to
record your answer):
A. Show all data in each of the tables.
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-84
Note there are two customers named Betsy Miller.
/* *** SQL-Query-MDC-A-INVOICE *** */
SELECT *
FROM INVOICE;
/* *** SQL-Query-MDC-A-INVOICE-ITEM *** */
SELECT *
FROM INVOICE_ITEM;
Chapter Two – Introduction to Structured Query Language
Page 2-85
B. List the LastName, FirstName, and Phone of all 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
Chapter Two – Introduction to Structured Query Language
Page 2-86
C. List the LastName, FirstName, and Phone for all customers with a FirstName of
“Nikki”.
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
D. List the LastName, FirstName, Phone, DateIn, and DateOut of all orders in excess of
$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-87
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
However, Microsoft Access uses the wildcard *, which gives the following SQL statement:
/* *** SQL-Query-MDC-E-Access *** */
SELECT LastName, FirstName, Phone
FROM CUSTOMER
F. List the LastName, FirstName, and Phone of all customers whose last name includes
the characters ‘cat’.
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-88
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:
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
Page 2-89
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.
However, Microsoft Access uses the wildcards * and ?, which give the following SQL
statement:
/* *** SQL-Query-MDC-G-Access *** */
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-90
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
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
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-91
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
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.
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
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
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
Chapter Two – Introduction to Structured Query Language
Page 2-93
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
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.
/* *** SQL-Query-MDC-P *** */
SELECT LastName, FirstName, Phone
FROM CUSTOMER
WHERE CustomerID IN
(SELECT CustomerID
FROM INVOICE
Chapter Two – Introduction to Structured Query Language
WHERE InvoiceNumber IN
(SELECT InvoiceNumber
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
Chapter Two – Introduction to Structured Query Language
Page 2-95
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
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
S. Who referred each customer to Marcia’s Dry Cleaning? Show columns named
CustomerLastName, CustomerFirstName, ReferredByLastName, and
ReferredByFirstName. Include the names of customers who were not referred by any
other customer in the results of the query.
Chapter Two – Introduction to Structured Query Language
Page 2-96
Solutions to Marcia’s Dry Cleaning questions are contained in the Microsoft Access database
T. Show the LastName, FirstName, and Phone of all customers who have had an order
with an Item named “Dress Shirt”. Use a combination of a join using JOIN ON
syntax with 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
U. Show the LastName, FirstName, Phone, and TotalAmount of all customer orders that
included an Item named “Dress Shirt”. Also show the LastName, FirstName, and
Phone of all other customers. Present results sorted by TotalAmount 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 OUTER or RIGHT OUTER join. The other DBMS products
can complete this question with one query (not a UNION statement).
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
Chapter Two – Introduction to Structured Query Language
Page 2-98
SELECT LastName, FirstName, Phone, TotalAmount
FROM CUSTOMER C LEFT JOIN (INVOICE I JOIN INVOICE_ITEM II
ON I.InvoiceNumber = II.InvoiceNumber AND II.Item = ‘Dress
/* *** SQL-Query-MDC-U-UNION *** */
SELECT LastName, FirstName, Phone, TotalAmount
FROM CUSTOMER C, INVOICE I, INVOICE_ITEM II
WHERE C.CustomerID = I.CustomerID AND I.InvoiceNumber =
II.InvoiceNumber AND II.Item = ‘Dress Shirt’
UNION SELECT LastName, FirstName, Phone, NULL
FROM CUSTOMER
WHERE CustomerID NOT IN
(SELECT CustomerID
SELECT LastName, FirstName, Phone, TotalAmount
FROM CUSTOMER AS C LEFT OUTER JOIN [SQL-Query-MDC-U-Temp] AS T
ON C.CustomerID = T.CustomerID
ORDER BY TotalAmount, LastName, FirstName DESC;
The results below are the same for all correct versions of this query, with the possible
exception of where the NULL TotalAmounts are presented: In Access, NULL comes before
all values; in Oracle, it comes last, etc.
Chapter Two – Introduction to Structured Query Language
Page 2-99
Chapter Two – Introduction to Structured Query Language
Page 2-100
❖ 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
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:
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.