Introduction to Structured Query Language - Database Processing: Fundamentals, Design, and Implementation - page 104

InvoiceNumber CustomerNumber DateIn DateOut TotalAmount

2013001 1 04-Oct-13 06-Oct-13 $158.50

2013002 2 04-Oct-13 06-Oct-13 $25.00

2013003 1 06-Oct-13 08-Oct-13 $49.00

2013004 4 06-Oct-13 08-Oct-13 $17.50

2013005 6 07-Oct-13 11-Oct-13 $12.00

2013006 3 11-Oct-13 13-Oct-13 $152.50

2013007 3 11-Oct-13 13-Oct-13 $7.00

2013008 7 12-Oct-13 14-Oct-13 $140.50

Figure 2-39
Sample Data for the MDC
Database INVOICE Table

2013009 5 12-Oct-13 14-Oct-13 $27.00

G. List the LastName, FirstName, and Phone for all customers whose second and third
numbers ( from the right) of their phone number are 23.

h. Determine the maximum and minimum TotalAmount.

I. Determine the average TotalAmount.

J. Count the number of customers.

K. Group customers by LastName and then by FirstName.

L. Count the number of customers having each combination of LastName and 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.

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 de-
scending order.

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.

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.

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.

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.