Marcia Wilson owns and operates Marcia's Dry Cleaning, which is an upscale dry cleaner...

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 the following:

CUSTOMER (CustomerID. FirstName, LastName, Phone, Email)

INVOICE (InvoiceNumber. CustomerNumber, Dateln, DateOut, TotalAmount) INVOICE_ITEM (InvoiceNumber. ItemNumber, Item, Quantity, UnitPrice)

In the database schema above, the primary keys are underlined and the foreign keys are shown in italics. The database that Marcia has created is named MDC, and the three tables in the MDC database schema are shown in Figure 1

Figure 1 The MDC Database

The column characteristics for the tables are shown in Figures 2, 3, and 4. The relationship between CUSTOMER and INVOICE should enforce referential integrity, but not cascade updates nor deletions, while the relationship between INVOICE and INVOICE^ ITEM should enforce referential integrity and cascade both updates and deletions. The data for these tables are shown in Figures 5,6, and 7.

Figure 2 Column Characteristics for the MDC Database CUSTOMER Table

Figure 3 Column Characteristics for the MDC Database INVOICE Table

Figure 4 Column Characteristics for the MDC Database INVOICE_ITEM Table

Figure 5 Sample Data for the MDC Database Customer Table

Figure 6 Sample Data for the MDC Database INVOICE Table

Figure 7 Sample Data for the MDC Database INVOICE_ITEM Table

Once you have setup your MDC_CH02 database, create an SQL script named MDC-CH02-CQsql, 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):

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, Dateln, 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 Total Amount.

---

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

---

S. 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 FirstName in descending order.

---

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