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, DateIn, 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 below:

The MDC Database

The column characteristics for the tables are shown in Figures below. The relationship between CUSTOMER and INVOICE should enforce referential integrity, but not cascade updates or deletions, while the relationship between INVOICE and INVOICE_ITEM should enforce referential integrity and cascade both updates and deletions.

We recommend that you create database in SQL Server, .

- Column Characteristics for the CUSTOMER Table

Column Characteristics for the INVOICE Table

Column Characteristics for the INVOICE_ITEM Table

Sample Data for the CUSTOMER table

Sample Data for the ORDER table

Sample Data for the ORDER_ITEM table

Write SQL statements and show the results based on the MDC data for each of the following:

1. Show all data in each of the tables.

1. List the Phone and LastName of all customers.

1. List the Phone and LastName for all customers with a FirstName of “Nikki”.

1. List the Phone, DateIn, and DateOut of all orders in excess of 100.

1. List the Phone and FirstName of all customers whose first name starts with 'B'.

1. List the Phone and FirstName of all customers whose last name includes the characters, 'cat'.

1. List the Phone, FirstName, and LastName for all customers whose second and third characters of phone number is 23.

1. Determine the maximum and minimum TotalAmounts.

1. Determine the average TotalAmount.

1. Count the number of customers.

1. Group customers by LastName and then by FirstName.

1. Count the number of customers having each combination of LastName and FirstName.

1. Show the FirstName and LastName of all customers who have had an order with TotalAmount greater than 100. Use a subquery. Present the results sorted by LastName in ascending order and then FirstName in descending order.

1. Show the FirstName and LastName of all customers who have had an order with TotalAmount greater than 100. Use a join. Present the results sorted by LastName in ascending order and then FirstName in descending order.

1. Show the FirstName and LastName of all customers who have had an order with an Item named “Dress Shirt”. Use a subquery. Present the results sorted by LastName in ascending order and then FirstName in descending order.

1. Show the FirstName and LastName of all customers who have had an order with an Item named “Dress Shirt”. Use a join. Present the results sorted by LastName in ascending order and then FirstName in descending order.

1. Show the FirstName, LastName and TotalAmount of all customers who have had an order with an Item named “Dress Shirt”. Use a join with a subquery. Present results sorted by LastName in ascending order and then FirstName in descending order.
   
   
   
   
   
   
   

Transcribed Image Text:

The CUSTOMER table The INVOICE table The INVOICE_ITEM table CUSTOMER B CustomerlD FirstName LastName Phone Email 00 INVOICE InvoiceNumber CustomerNumber Dateln DateOut TotalAmount INVOICE ITEM InvoiceNumber ItemNumber Item Quantity UnitPrice The CUSTOMER table The INVOICE table The INVOICE_ITEM table CUSTOMER B CustomerlD FirstName LastName Phone Email 00 INVOICE InvoiceNumber CustomerNumber Dateln DateOut TotalAmount INVOICE ITEM InvoiceNumber ItemNumber Item Quantity UnitPrice The CUSTOMER table The INVOICE table The INVOICE_ITEM table CUSTOMER B CustomerlD FirstName LastName Phone Email 00 INVOICE InvoiceNumber CustomerNumber Dateln DateOut TotalAmount INVOICE ITEM InvoiceNumber ItemNumber Item Quantity UnitPrice The CUSTOMER table The INVOICE table The INVOICE_ITEM table CUSTOMER B CustomerlD FirstName LastName Phone Email 00 INVOICE InvoiceNumber CustomerNumber Dateln DateOut TotalAmount INVOICE ITEM InvoiceNumber ItemNumber Item Quantity UnitPrice The CUSTOMER table The INVOICE table The INVOICE_ITEM table CUSTOMER B CustomerlD FirstName LastName Phone Email 00 INVOICE InvoiceNumber CustomerNumber Dateln DateOut TotalAmount INVOICE ITEM InvoiceNumber ItemNumber Item Quantity UnitPrice The CUSTOMER table The INVOICE table The INVOICE_ITEM table CUSTOMER B CustomerlD FirstName LastName Phone Email 00 INVOICE InvoiceNumber CustomerNumber Dateln DateOut TotalAmount INVOICE ITEM InvoiceNumber ItemNumber Item Quantity UnitPrice The CUSTOMER table The INVOICE table The INVOICE_ITEM table CUSTOMER B CustomerlD FirstName LastName Phone Email 00 INVOICE InvoiceNumber CustomerNumber Dateln DateOut TotalAmount INVOICE ITEM InvoiceNumber ItemNumber Item Quantity UnitPrice The CUSTOMER table The INVOICE table The INVOICE_ITEM table CUSTOMER B CustomerlD FirstName LastName Phone Email 00 INVOICE InvoiceNumber CustomerNumber Dateln DateOut TotalAmount INVOICE ITEM InvoiceNumber ItemNumber Item Quantity UnitPrice The CUSTOMER table The INVOICE table The INVOICE_ITEM table CUSTOMER B CustomerlD FirstName LastName Phone Email 00 INVOICE InvoiceNumber CustomerNumber Dateln DateOut TotalAmount INVOICE ITEM InvoiceNumber ItemNumber Item Quantity UnitPrice The CUSTOMER table The INVOICE table The INVOICE_ITEM table CUSTOMER B CustomerlD FirstName LastName Phone Email 00 INVOICE InvoiceNumber CustomerNumber Dateln DateOut TotalAmount INVOICE ITEM InvoiceNumber ItemNumber Item Quantity UnitPrice The CUSTOMER table The INVOICE table The INVOICE_ITEM table CUSTOMER B CustomerlD FirstName LastName Phone Email 00 INVOICE InvoiceNumber CustomerNumber Dateln DateOut TotalAmount INVOICE ITEM InvoiceNumber ItemNumber Item Quantity UnitPrice The CUSTOMER table The INVOICE table The INVOICE_ITEM table CUSTOMER B CustomerlD FirstName LastName Phone Email 00 INVOICE InvoiceNumber CustomerNumber Dateln DateOut TotalAmount INVOICE ITEM InvoiceNumber ItemNumber Item Quantity UnitPrice The CUSTOMER table The INVOICE table The INVOICE_ITEM table CUSTOMER B CustomerlD FirstName LastName Phone Email 00 INVOICE InvoiceNumber CustomerNumber Dateln DateOut TotalAmount INVOICE ITEM InvoiceNumber ItemNumber Item Quantity UnitPrice The CUSTOMER table The INVOICE table The INVOICE_ITEM table CUSTOMER B CustomerlD FirstName LastName Phone Email 00 INVOICE InvoiceNumber CustomerNumber Dateln DateOut TotalAmount INVOICE ITEM InvoiceNumber ItemNumber Item Quantity UnitPrice The CUSTOMER table The INVOICE table The INVOICE_ITEM table CUSTOMER B CustomerlD FirstName LastName Phone Email 00 INVOICE InvoiceNumber CustomerNumber Dateln DateOut TotalAmount INVOICE ITEM InvoiceNumber ItemNumber Item Quantity UnitPrice The CUSTOMER table The INVOICE table The INVOICE_ITEM table CUSTOMER B CustomerlD FirstName LastName Phone Email 00 INVOICE InvoiceNumber CustomerNumber Dateln DateOut TotalAmount INVOICE ITEM InvoiceNumber ItemNumber Item Quantity UnitPrice The CUSTOMER table The INVOICE table The INVOICE_ITEM table CUSTOMER B CustomerlD FirstName LastName Phone Email 00 INVOICE InvoiceNumber CustomerNumber Dateln DateOut TotalAmount INVOICE ITEM InvoiceNumber ItemNumber Item Quantity UnitPrice The CUSTOMER table The INVOICE table The INVOICE_ITEM table CUSTOMER B CustomerlD FirstName LastName Phone Email 00 INVOICE InvoiceNumber CustomerNumber Dateln DateOut TotalAmount INVOICE ITEM InvoiceNumber ItemNumber Item Quantity UnitPrice The CUSTOMER table The INVOICE table The INVOICE_ITEM table CUSTOMER B CustomerlD FirstName LastName Phone Email 00 INVOICE InvoiceNumber CustomerNumber Dateln DateOut TotalAmount INVOICE ITEM InvoiceNumber ItemNumber Item Quantity UnitPrice The CUSTOMER table The INVOICE table The INVOICE_ITEM table CUSTOMER B CustomerlD FirstName LastName Phone Email 00 INVOICE InvoiceNumber CustomerNumber Dateln DateOut TotalAmount INVOICE ITEM InvoiceNumber ItemNumber Item Quantity UnitPrice

Answer rating: 100% (QA)

given that Ms Marcia Wilson owns and operates Marcias Dry Cleaning which is an upscale dry cleaner i... View the full answer