SERVICE INVOICE ServiceID Service Description UnitPrice INVOICE_ITEM InvoiceNumber ItemNumber ServiceID InvoiceNumber CustomerID CUSTOMER CustomerID FirstName...

Transcribed Image Text:

SERVICE INVOICE ServiceID Service Description UnitPrice INVOICE_ITEM InvoiceNumber ItemNumber ServiceID InvoiceNumber CustomerID • CUSTOMER CustomerID FirstName Subtotal Tax LastName Phone quantity UnitPrice TotalAmount EmailAddress ExtendedPrice Based on the above relations, create following tables (Question 1.1-1.4). 1.1 Create table CUSTOMER based on the following requirements: 3 points for the primary key column, (1) automatically generate integers that start from 100 and increase by 1 for each new row, and (2) add primary key constraint. Assign appropriate data type for each column. All columns are not allowed to be NULL 1.3 Create table INVOICE based on the following requirements: 3 points for the primary key column, (1) automatically generate integers that start from 2018001 and increase by 1 for each new row, and (2) add primary key constraint. Assign appropriate data type for each column. All columns are not allowed to be NULL Add foreign key constraint for all foreign keys. 1.4 Create table INVOICE_ITEM based on the following requirements: 3 points add primary key constraint. Assign appropriate data type for each column. Add foreign key constraint for all foreign keys. . . . The following questions are based on the tables you created in question 1.1-1.4. 1.5 executing the following codes. If you got errors, explain why do you get errors. [for this question, you don't need to submit your screenshot] 2 points INSERT INTO CUSTOMER VALUES( 'Nikki', Kaccaton", "723-543-1233', 'Nikki Kaccaton@somewhere.com'); INSERT INTO CUSTOMER VALUES( 'Brenda', 'Catnazaro', 723-543-2344, Brenda Catnazaro@somewhere.com'); INSERT INTO CUSTOMER VALUES( 'Bruce', 'LeCat', 723-543-3455', 'Bruce.LeCat@somewhere.com'); INSERT INTO CUSTOMER VALUES( 'Betsy', 'Miller', '723-654-3211", "Betsy Miller@somewhere.com'); INSERT INTO CUSTOMER VALUES( 'George', 'Miller, 723-654-4322', 'George Miller@somewhere.com'); INSERT INTO CUSTOMER VALUES( 'Kathy', 'Miller', 723-514-9877, Kathy Miller@somewhere.com'); INSERT INTO CUSTOMER VALUES( 'Betsy', 'Miller'. 723-514-8766', 'Betsy Miller@elsewhere.com): INSERT INTO SERVICE VALUES(10, Mens Shirt, 1.50); INSERT INTO SERVICE VALUES(11, 'Dress Shirt', 2.50): INSERT INTO SERVICE VALUES(15, Women's Shirt, 1.50): INSERT INTO SERVICE VALUES(16, Blouse", 3.50); INSERT INTO SERVICE VALUES (20, "Slacks-Men's', 5.00): INSERT INTO SERVICE VALUES(25, Slacks-Women's", 6.00); INSERT INTO SERVICE VALUES(30, "Skirt, 5.00); INSERT INTO SERVICE VALUES(31, Dress Skirt', 6.00): INSERT INTO SERVICE VALUES(40, "Sull-Men's', 9.00): INSERT INTO SERVICE VALUES(45, "Suit-Women's', 8.50) INSERT INTO SERVICE VALUES(50, Tuxedo', 10.00); INSERT INTO SERVICE VALUES(60, Formal Gown', 11.00): INSERT INTO INVOICE VALUES( 100, 158.50, 12.52, 171.02), INSERT INTO INVOICE VALUES( 101. 25.00, 1.98, 26.98): INSERT INTO INVOICE VALUES( 100, 49.00, 3.87, 52.87); INSERT INTO INVOICE VALUES( 103, 17.50, 1.38, 18.88): INSERT INTO INVOICE VALUES( 105, 12.00, 0.95, 12.95); INSERT INTO INVOICE VALUES( 102, 152.50, 12.05, 164.55): INSERT INTO INVOICE VALUES( 102, 7.00, 0.55, 7.55): INSERT INTO INVOICE VALUES( 106, 140.50, 11.10, 151.60): INSERT INTO INVOICE VALUES( 104, 27.00, 2.13, 29.13); INSERT INTO INVOICE ITEM VALUES(2018001, 1, 16, 2, 3.50, 7.00); INSERT INTO INVOICE ITEM VALUES (2018001, 2, 11, 5, 2.50, 12.50); INSERT INTO INVOICE ITEM VALUES(2018001, 3, 50, 2, 10.00, 20.00); INSERT INTO INVOICE ITEM VALUES(2018001, 4, 20, 10, 5.00, 50.00): INSERT INTO INVOICE ITEM VALUES(2018001, 5, 25, 10, 6.00, 60.00); INSERT INTO INVOICE ITEM VALUES (2018001, 6, 40, 1, 9.00, 9.00): INSERT INTO INVOICE ITEM VALUES(2018002, 1. 11, 10, 2.50, 25.00); INSERT INTO INVOICE ITEM VALUES (2018003, 1, 20, 5, 5.00, 25.00); INSERT INTO INVOICE ITEM VALUES (2018003, 2, 25, 4, 6.00, 24.00): INSERT INTO INVOICE ITEM VALUES(2018004, 1, 11, 7, 2.50, 17.50); INSERT INTO INVOICE ITEM VALUES (2018005, 1., 16, 2, 3.50, 7.00): INSERT INTO INVOICE ITEM VALUES(2018005, 2. 11, 2, 2.50, 5.00): INSERT INTO INVOICE_ITEM VALUES(2018006, 1, 16, 5, 3.50, 17.50); INSERT INTO INVOICE_ITEM VALUES(2018006, 2, 11, 10, 2.50, 25.00); INSERT INTO INVOICE ITEM VALUES(2018006, 3, 20, 10, 5.00, 50.00): INSERT INTO INVOICE ITEM VALUES(2018006, 4, 25, 10, 6.00, 60.00): INSERT INTO INVOICE_ITEM VALUES(2018007, 1, 16, 2, 3.50, 7.00): INSERT INTO INVOICE ITEM VALUES(2018008, 1, 16, 3, 3.50, 10.50); INSERT INTO INVOICE ITEM VALUES(2018008, 2, 11, 12, 2.50, 30.00): INSERT INTO INVOICE ITEM VALUES(2018008, 3, 20, 8, 5.00, 40.00): INSERT INTO INVOICE ITEM VALUES(2018008, 4, 25, 10, 6.00, 60.00): INSERT INTO INVOICE ITEM VALUES(2018009, 1, 40, 3, 9.00, 27.00); 1.6 Executing the following codes. 6 points (1) How many errors in the following codes? Why do you get these errors? INSERT INTO CUSTOMER VALUES('Nike', "Kaccaton', 'Nikki Kaccaton@somewhere.com'); (2) How many errors in the following codes? Why do you get these errors? INSERT INTO CUSTOMER VALUES(Bread". "White', '505-543-2344"); 1.7 Add a constraint to Tax in table INVOICE, make sure that the value of tax is less than 15 (tax<15). 5 points 1.8 Execute following codes. If you get errors, please explain why do you get errors. If your codes are successful, please submit your screenshot which should show all rows of the final table. 5 points INSERT INTO INVOICE ITEM VALUES (2018001, 1, 2, 2, 3.50, 7.00); SERVICE ServiceID Service Description UnitPrice INVOICE_ITEM INVOICE InvoiceNumber ItemNumber ServiceID quantity InvoiceNumber CustomerID Subtotal Tax CUSTOMER CustomerID FirstName LastName Phone UnitPrice TotalAmount EmailAddress ExtendedPrice 1.9 Divide the INVOICE_ITEM table based on the ItemNumber. Please also calculate the average UnitPrice, minimal ExtendedPrice and maximal quantity for each group. (for this question, please submit codes and screenshot of results after executing your codes) 10 points SERVICE INVOICE ServiceID Service Description UnitPrice INVOICE_ITEM InvoiceNumber ItemNumber ServiceID InvoiceNumber CustomerID • CUSTOMER CustomerID FirstName Subtotal Tax LastName Phone quantity UnitPrice TotalAmount EmailAddress ExtendedPrice Based on the above relations, create following tables (Question 1.1-1.4). 1.1 Create table CUSTOMER based on the following requirements: 3 points for the primary key column, (1) automatically generate integers that start from 100 and increase by 1 for each new row, and (2) add primary key constraint. Assign appropriate data type for each column. All columns are not allowed to be NULL 1.3 Create table INVOICE based on the following requirements: 3 points for the primary key column, (1) automatically generate integers that start from 2018001 and increase by 1 for each new row, and (2) add primary key constraint. Assign appropriate data type for each column. All columns are not allowed to be NULL Add foreign key constraint for all foreign keys. 1.4 Create table INVOICE_ITEM based on the following requirements: 3 points add primary key constraint. Assign appropriate data type for each column. Add foreign key constraint for all foreign keys. . . . The following questions are based on the tables you created in question 1.1-1.4. 1.5 executing the following codes. If you got errors, explain why do you get errors. [for this question, you don't need to submit your screenshot] 2 points INSERT INTO CUSTOMER VALUES( 'Nikki', Kaccaton", "723-543-1233', 'Nikki Kaccaton@somewhere.com'); INSERT INTO CUSTOMER VALUES( 'Brenda', 'Catnazaro', 723-543-2344, Brenda Catnazaro@somewhere.com'); INSERT INTO CUSTOMER VALUES( 'Bruce', 'LeCat', 723-543-3455', 'Bruce.LeCat@somewhere.com'); INSERT INTO CUSTOMER VALUES( 'Betsy', 'Miller', '723-654-3211", "Betsy Miller@somewhere.com'); INSERT INTO CUSTOMER VALUES( 'George', 'Miller, 723-654-4322', 'George Miller@somewhere.com'); INSERT INTO CUSTOMER VALUES( 'Kathy', 'Miller', 723-514-9877, Kathy Miller@somewhere.com'); INSERT INTO CUSTOMER VALUES( 'Betsy', 'Miller'. 723-514-8766', 'Betsy Miller@elsewhere.com): INSERT INTO SERVICE VALUES(10, Mens Shirt, 1.50); INSERT INTO SERVICE VALUES(11, 'Dress Shirt', 2.50): INSERT INTO SERVICE VALUES(15, Women's Shirt, 1.50): INSERT INTO SERVICE VALUES(16, Blouse", 3.50); INSERT INTO SERVICE VALUES (20, "Slacks-Men's', 5.00): INSERT INTO SERVICE VALUES(25, Slacks-Women's", 6.00); INSERT INTO SERVICE VALUES(30, "Skirt, 5.00); INSERT INTO SERVICE VALUES(31, Dress Skirt', 6.00): INSERT INTO SERVICE VALUES(40, "Sull-Men's', 9.00): INSERT INTO SERVICE VALUES(45, "Suit-Women's', 8.50) INSERT INTO SERVICE VALUES(50, Tuxedo', 10.00); INSERT INTO SERVICE VALUES(60, Formal Gown', 11.00): INSERT INTO INVOICE VALUES( 100, 158.50, 12.52, 171.02), INSERT INTO INVOICE VALUES( 101. 25.00, 1.98, 26.98): INSERT INTO INVOICE VALUES( 100, 49.00, 3.87, 52.87); INSERT INTO INVOICE VALUES( 103, 17.50, 1.38, 18.88): INSERT INTO INVOICE VALUES( 105, 12.00, 0.95, 12.95); INSERT INTO INVOICE VALUES( 102, 152.50, 12.05, 164.55): INSERT INTO INVOICE VALUES( 102, 7.00, 0.55, 7.55): INSERT INTO INVOICE VALUES( 106, 140.50, 11.10, 151.60): INSERT INTO INVOICE VALUES( 104, 27.00, 2.13, 29.13); INSERT INTO INVOICE ITEM VALUES(2018001, 1, 16, 2, 3.50, 7.00); INSERT INTO INVOICE ITEM VALUES (2018001, 2, 11, 5, 2.50, 12.50); INSERT INTO INVOICE ITEM VALUES(2018001, 3, 50, 2, 10.00, 20.00); INSERT INTO INVOICE ITEM VALUES(2018001, 4, 20, 10, 5.00, 50.00): INSERT INTO INVOICE ITEM VALUES(2018001, 5, 25, 10, 6.00, 60.00); INSERT INTO INVOICE ITEM VALUES (2018001, 6, 40, 1, 9.00, 9.00): INSERT INTO INVOICE ITEM VALUES(2018002, 1. 11, 10, 2.50, 25.00); INSERT INTO INVOICE ITEM VALUES (2018003, 1, 20, 5, 5.00, 25.00); INSERT INTO INVOICE ITEM VALUES (2018003, 2, 25, 4, 6.00, 24.00): INSERT INTO INVOICE ITEM VALUES(2018004, 1, 11, 7, 2.50, 17.50); INSERT INTO INVOICE ITEM VALUES (2018005, 1., 16, 2, 3.50, 7.00): INSERT INTO INVOICE ITEM VALUES(2018005, 2. 11, 2, 2.50, 5.00): INSERT INTO INVOICE_ITEM VALUES(2018006, 1, 16, 5, 3.50, 17.50); INSERT INTO INVOICE_ITEM VALUES(2018006, 2, 11, 10, 2.50, 25.00); INSERT INTO INVOICE ITEM VALUES(2018006, 3, 20, 10, 5.00, 50.00): INSERT INTO INVOICE ITEM VALUES(2018006, 4, 25, 10, 6.00, 60.00): INSERT INTO INVOICE_ITEM VALUES(2018007, 1, 16, 2, 3.50, 7.00): INSERT INTO INVOICE ITEM VALUES(2018008, 1, 16, 3, 3.50, 10.50); INSERT INTO INVOICE ITEM VALUES(2018008, 2, 11, 12, 2.50, 30.00): INSERT INTO INVOICE ITEM VALUES(2018008, 3, 20, 8, 5.00, 40.00): INSERT INTO INVOICE ITEM VALUES(2018008, 4, 25, 10, 6.00, 60.00): INSERT INTO INVOICE ITEM VALUES(2018009, 1, 40, 3, 9.00, 27.00); 1.6 Executing the following codes. 6 points (1) How many errors in the following codes? Why do you get these errors? INSERT INTO CUSTOMER VALUES('Nike', "Kaccaton', 'Nikki Kaccaton@somewhere.com'); (2) How many errors in the following codes? Why do you get these errors? INSERT INTO CUSTOMER VALUES(Bread". "White', '505-543-2344"); 1.7 Add a constraint to Tax in table INVOICE, make sure that the value of tax is less than 15 (tax<15). 5 points 1.8 Execute following codes. If you get errors, please explain why do you get errors. If your codes are successful, please submit your screenshot which should show all rows of the final table. 5 points INSERT INTO INVOICE ITEM VALUES (2018001, 1, 2, 2, 3.50, 7.00); SERVICE ServiceID Service Description UnitPrice INVOICE_ITEM INVOICE InvoiceNumber ItemNumber ServiceID quantity InvoiceNumber CustomerID Subtotal Tax CUSTOMER CustomerID FirstName LastName Phone UnitPrice TotalAmount EmailAddress ExtendedPrice 1.9 Divide the INVOICE_ITEM table based on the ItemNumber. Please also calculate the average UnitPrice, minimal ExtendedPrice and maximal quantity for each group. (for this question, please submit codes and screenshot of results after executing your codes) 10 points

Answer rating: 100% (QA)

To assist you here I will guide you through creating SQL table structures based on the requirements youve given and I will address the errors in the provided SQL INSERT statements 11 Creating the CUST... View the full answer