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 Write SQL statements to create the three tables Update the CUSTOMER table to change the column Phone to Char(12) 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 List the LastName, FirstName, and Phone for all customers with a FirstName of Nikki or a LastName that includes the characters cat 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 List the LastName, FirstName, Phone, DateIn, and DateOut of all orders in excess of $100 00 Determine the maximum, minimum and average TotalAmount 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 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 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 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)

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Aug 11, 2024

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

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.

Write SQL statements to create the three tables.

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

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.

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

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.

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

Determine the maximum, minimum and average TotalAmount.

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.

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.

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.

[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);

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