Ocean Dry Cleaning is an upscale dry cleaner in a suburban neighborhood of Upstate New York. The owner makes her business stand out from the others by providing great 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 tables above, the primary keys are underlined and the foreign keys are shown in italics.

The column characteristics for the tables are shown below:

Column Name	Type	Required
CustomerID	Integer	Yes
FirstName	Char(25)	Yes
LastName	Char(25)	Yes
Phone	Char(10)	No
Email	Varchar(100)	No
InvoiceNumber	Integer	Yes
CustomerNumber	Integer	Yes
DateIn	Date	Yes
DateOut	Date	No
TotalAmount	Decimal (2 decimal places)	No
InvoiceNumber	Integer	Yes
ItemNumber	Integer	Yes
Item	Varchar(50)	Yes
Quantity	Integer	Yes
UnitPrice	Decimal (2 decimal places)	Yes

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.

Question 1: [3 points]

Write DDL statements for the following. Include your SQL statement below each question.

1. Write SQL statements to create the three tables.

2. Update the CUSTOMER table to change the column Phone to Char(12).

3. Insert data into your tables using the Insert Data script provided to you on Moodle (No SQL Statements required here, just add the data to your schema using the script).

Question 2: [7 points]

Write DML SQL statements for the following. Include your SQL statement below each question followed by a screen shot of the data returned.

1. List the LastName, FirstName, and Phone for all customers with a FirstName of Nikki or a LastName that includes the characters cat.

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

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

4. Determine the maximum, minimum and average TotalAmount.

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

6. Show the LastName, FirstName and Phone 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.

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

8. [Extra Credit] Show the LastName, FirstName, Phone, and TotalAmount of all customer orders that include 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.

/***** CUSTOMER Data ************************************************/

INSERT INTO CUSTOMER VALUES( 1, 'Nikki', 'Kaccaton', '723-543-1233', 'Nikki.Kaccaton@somewhere.com'); INSERT INTO CUSTOMER VALUES( 2, 'Brenda', 'Catnazaro', '723-543-2344', 'Brenda.Catnazaro@somewhere.com'); INSERT INTO CUSTOMER VALUES( 3, 'Bruce', 'LeCat', '723-543-3455', 'Bruce.LeCat@somewhere.com'); INSERT INTO CUSTOMER VALUES( 4, 'Betsy', 'Miller', '725-654-3211', 'Betsy.Miller@somewhere.com'); INSERT INTO CUSTOMER VALUES( 5, 'George', 'Miller', '725-654-4322', 'George.Miller@somewhere.com'); INSERT INTO CUSTOMER VALUES( 6, 'Kathy', 'Miller', '723-514-9877', 'Kathy.Miller@somewhere.com'); INSERT INTO CUSTOMER VALUES( 7, 'Betsy', 'Miller', '723-514-8766', 'Betsy.Miller@elsewhere.com');

/***** INVOICE Data *******************************************************/

INSERT INTO INVOICE VALUES( 2015001, 1, '2015-10-04', '2015-10-06', 158.50); INSERT INTO INVOICE VALUES( 2015002, 2, '2015-10-04', '2015-10-06', 25.00); INSERT INTO INVOICE VALUES( 2015003, 1, '2015-10-06', '2015-10-08', 49.00); INSERT INTO INVOICE VALUES( 2015004, 4, '2015-10-06', '2015-10-08', 17.50); INSERT INTO INVOICE VALUES( 2015005, 6, '2015-10-07', '2015-10-11', 12.00); INSERT INTO INVOICE VALUES( 2015006, 3, '2015-10-11', '2015-10-13', 152.50); INSERT INTO INVOICE VALUES( 2015007, 3, '2015-10-11', '2015-10-13', 7.00); INSERT INTO INVOICE VALUES( 2015008, 7, '2015-10-12', '2015-10-14', 140.50); INSERT INTO INVOICE VALUES( 2015009, 5, '2015-10-12', '2015-10-14', 27.00);

/***** INVOICE_ITEM Data *************************************************/

INSERT INTO INVOICE_ITEM VALUES(2015001, 1, 'Blouse', 2, 3.50); INSERT INTO INVOICE_ITEM VALUES(2015001, 2, 'Dress Shirt', 5, 2.50); INSERT INTO INVOICE_ITEM VALUES(2015001, 3, 'Formal Gown', 2, 10.00); INSERT INTO INVOICE_ITEM VALUES(2015001, 4, 'Slacks-Mens', 10, 5.00); INSERT INTO INVOICE_ITEM VALUES(2015001, 5, 'Slacks-Womens', 10, 6.00); INSERT INTO INVOICE_ITEM VALUES(2015001, 6, 'Suit-Mens', 1, 9.00); INSERT INTO INVOICE_ITEM VALUES(2015002, 1, 'Dress Shirt', 10, 2.50); INSERT INTO INVOICE_ITEM VALUES(2015003, 1, 'Slacks-Mens', 5, 5.00); INSERT INTO INVOICE_ITEM VALUES(2015003, 2, 'Slacks-Womens', 4, 6.00); INSERT INTO INVOICE_ITEM VALUES(2015004, 1, 'Dress Shirt', 7, 2.50); INSERT INTO INVOICE_ITEM VALUES(2015005, 1, 'Blouse', 2, 3.50); INSERT INTO INVOICE_ITEM VALUES(2015005, 2, 'Dress Shirt', 2, 2.50); INSERT INTO INVOICE_ITEM VALUES(2015006, 1, 'Blouse', 5, 3.50); INSERT INTO INVOICE_ITEM VALUES(2015006, 2, 'Dress Shirt', 10, 2.50); INSERT INTO INVOICE_ITEM VALUES(2015006, 3, 'Slacks-Mens', 10, 5.00); INSERT INTO INVOICE_ITEM VALUES(2015006, 4, 'Slacks-Womens', 10, 6.00); INSERT INTO INVOICE_ITEM VALUES(2015007, 1, 'Blouse', 2, 3.50); INSERT INTO INVOICE_ITEM VALUES(2015008, 1, 'Blouse', 3, 3.50); INSERT INTO INVOICE_ITEM VALUES(2015008, 2, 'Dress Shirt', 12, 2.50); INSERT INTO INVOICE_ITEM VALUES(2015008, 3, 'Slacks-Mens', 8, 5.00); INSERT INTO INVOICE_ITEM VALUES(2015008, 4, 'Slacks-Womens', 10, 6.00); INSERT INTO INVOICE_ITEM VALUES(2015009, 1, 'Suit-Mens', 3, 9.00);

/****************************************************************************/