Case Study 2

Instructions

• Use the MDC_CH02 database
• Answer MARCIA'S DRY-CLEANING QUESTIONS at the end of the chapter (Questions A-U)
• Deliver a single sql file, along with a document that contains all the content from the sql file
• Each query should start with a comment line that looks like:
  /* *** CS2-<Q> *** */
  Example for Question A:
  /* *** CS2-A*** */
• Do not include the result table unless specifically directed to
• Include at least one line of white space between answers

Questions A-U

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 for all customers with a FirstName of ‘Nikki’.

D.) List the LastName, FirstName, Phone, DateIn, and DateOut of all orders in excess of $100.00.

E.) List the LastName, FirstName, and Phone of all customers whose first name starts with ‘B’.

F.) List the LastName, FirstName, and Phone of all customers whose last name includes the characters ‘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.

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 descend- ing 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 First-Name 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.

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.

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 and a subquery. Present results sorted by LastName in ascending order and then First-Name in descending order.

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).