SERVICE INVOICE ServiceID Service Description UnitPrice INVOICE_ITEM InvoiceNumber ItemNumber ServiceID InvoiceNumber CustomerID CUSTOMER CustomerID FirstName...
Fantastic news! We've Found the answer you've been seeking!
Question:
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
Expert Answer:
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
Related Book For
Posted Date:
Students also viewed these programming questions
-
Database Model Reference Essay The relational model is the current database implementation standard used today, and while some of the other models are not used, some of the concepts still are....
-
Consider a two-node network with two links and two demands between the two nodes. Suppose that c1 = 1, c2 = n and h1 = 1, h2 = n, and that demand d = 1 uses only one path P11 = {1}, and demand d = 2...
-
Your favorite algebra teachers are packing presents for the toy drive. Ms. May, Ms. Kirkland and Mr. Virgil pack 3 boxes of Barbies and 4 boxes of Kens for a total of 30 toys. Ms. Shead, Ms. Knight...
-
Pick a Nigerian brand that has been very successful competing against the bigger global brands in its product category. What are the elements of its strategy that have enabled it to achieve this...
-
How does pressure relate to force?
-
Shop for a new liquid asset account appropriate for your needs (e.g., bank account, CD, money market mutual fund). Describe the purchase process (e.g., dollar cost, "paperwork") and your anticipated...
-
The income statements for Prince Inc. for two years (summarized) follow. Additional information - Environmental fines are not deductible for income tax purposes. - The amount collected in 2020...
-
James A. and Ella R. Polk, ages 70 and 65, are retired physicians who live at 13319 Taylorcrest Street, Houston, Texas 77079. Their three adult children (Benjamin Polk, Michael Polk, and Olivia...
-
Banks take on risks and may suffer losses if the risks materialise. To stay safe and protect people's deposits, regulators require banks to have enough capital to be able to absorb such losses and...
-
1. A building owned by Hopewell Company was recently valued at $850,000 by a real estate expert. The president of the company is questioning the accuracy of the firm's latest balance sheet because it...
-
Question 3: (a) A GMPE predicts a median peak horizontal acceleration of 0.4 g. If the standard deviation in In units is 0.5, what is the 84th percentile estimate for the peak horizontal...
-
Suppose, swap-spreads went up to historical highs and LTCM believed they would come back down so they entered a "short" swap-spread position, which is the opposite position as the one described in...
-
Give An introductory paragraph that identifiesDiLonard's thesis (using own words as far as possible). This can consist of a few short sentences, or a single all-encompassing sentence that...
-
Piedmont Fasteners Corporation makes three different clothing fasteners in its manufacturing facility in North Carolina. All three products are sold in highly competitive markets, so the company is...
-
A 12-year-old boy who brandished a knife while threatening another youth earlier this month has received an unusual sentence. The boy, who can't be identified under provisions of the Youth Criminal...
-
You are entitled to receive an annuity of AUD1,000 per year for the next four years.Assuming an interest rate of 8% (annual) what is the present value of the annuity.
-
Question has full details. There is nothing else to add.
-
If the cylinder described in Problem 21.3 were initially heated to 500F, how long would it take for the center of the cylinder to cool to 240F if it were constructed of a. Copper? b. Brass? c. Nickel?
-
What is business process modeling?
-
Explain how primary keys and foreign keys are denoted in this book.
-
Create a Web page for Heather Sweeney Designs to display data in the SEMINAR,SEMINAR_CUSTOMER, and CUSTOMER tables to list the SEMINARdata and the EmailAddress, LastName, FirstName, and Phone of any...
-
You will compute the (Fourier) convolution of two box functions of the same width. Recall that the box function is given by \[f_{a}(x)= \begin{cases}1, & |x| \leq a \\ 0, & |x|>a\end{cases}\]...
-
Define the integrals \(I_{n}=\int_{-\infty}^{\infty} x^{2 n} e^{-x^{2}} d x\). Noting that \(I_{0}=\sqrt{\pi}\), a. Find a recursive relation between \(I_{n}\) and \(I_{n-1}\). b. Use this relation...
-
Find the Laplace transform of the following functions: a. \(f(t)=9 t^{2}-7\). b. \(f(t)=e^{5 t-3}\). c. \(f(t)=\cos 7 t\) d. \(f(t)=e^{4 t} \sin 2 t\). e. \(f(t)=e^{2 t}(t+\cosh t)\). f. \(f(t)=t^{2}...
Study smarter with the SolutionInn App