You will then write SQL statements to solve questions A - V.

It is not necessary to save the results of the queries, you just need to turn in the queries themselves.

Assume that the database design has 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 ItemD in ITEM

A. Show all data in each of the tables.

B. List the LastName, FirstName, and Phone of all customers.

C. List the LastName, FirstName, and Phone of all customers with a FirstName of 'John'

D. List the LastName, FirstName, Phone, SaleDate, and Total of all sales in excess of $100

E. List the LastName, FirstName, and Phone of all customers whose FirstName starts with 'D'

F. List the LastName, FirstName, and Phone of all customers whose last name includes 'ne'

G. List the LastName, FirstName, and Phone of all customers whose eighth and ninth digits of their phone number are 56.

H. Determine the maximum and minimum sales Total.

I. Determine the average sales Total.

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 Total greater than $100. 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 Total greater than $100. Use a join but do not use JOIN ON syntax. Present the results sorted by LastName in ascending order and then FirstName in descending order.

O. Show the LastName, FirstName, and Phone of all customers who have had an order with Total greater than $100. Use a join using JOIN ON syntax. Present the 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 bought an item named 'Desk Lamp'. Use a subquery. Present the 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 bought an item named 'Desk Lamp'. Use a join but do not use JOIN ON syntax. Present the 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 bought an item named 'Desk Lamp'. Use a join using JOIN ON syntax. Present the results sorted by LastName in ascending order and then FirstName in descending order.

S. Show the LastName, FirstName, and Phone of all customers who have bought an item named 'Desk Lamp'. Use combination of a join using JOIN ON syntax and a subquery. Present the results sorted by LastName in ascending order and then FirstName in descending order.

T. Show the LastName, FirstName, and Phone of all customers who have bought an item named 'Desk Lamp'. Use combination of a join using JOIN ON syntax and a subquery that is different from the combination in question S. Present the results sorted by LastName in ascending order and then FirstName in descending order.

U. Show the LastName, FirstName, Phone, and ItemDescription of all customers who have bought an item named 'Desk Lamp'. Also show the LastName, FirstName, and Phone of all other customers. Present the results sorted by ItemDescription in ascending order, then LastName in ascending order and then FirstName in descending order.

V. 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 this query.