QUESTIONS 5-18

In either ORACLESQLDevleoper or MYSQL

For each query, show your SQL statement and the corresponding output from the DBMS in your Microsoft Word document that you submit. DDL:

CREATE TABLE CUSTOMER ( CUS_CODE integer, CUS_LNAME varchar2(15), CUS_FNAME varchar2(15), CUS_INITIAL varchar2(1), CUS_AREACODE varchar2(3), CUS_PHONE varchar2(8), CUS_BALANCE number(9,2), primary key (CUS_CODE) );

CREATE TABLE INVOICE ( INV_NUMBER integer, CUS_CODE integer, INV_DATE date, primary key (INV_NUMBER), Foreign key (CUS_CODE) references CUSTOMER (CUS_CODE) );

CREATE TABLE VENDOR ( V_CODE integer, V_NAME varchar2(15), V_CONTACT varchar2(50), V_AREACODE varchar2(3), V_PHONE varchar2(8), V_STATE varchar2(2), V_ORDER varchar2(1), primary key (V_CODE) );

CREATE TABLE PRODUCT ( P_CODE varchar2(10), P_DESCRIPT varchar2(35), P_INDATE date, P_QOH integer, P_MIN integer, P_PRICE number(8,2), P_DISCOUNT number(5,2), V_CODE integer, primary key (P_CODE), Foreign key (V_CODE) references VENDOR (V_CODE) );

CREATE TABLE LINE ( INV_NUMBER integer, LINE_NUMBER integer, P_CODE varchar2(10), LINE_UNITS number(9,2), LINE_PRICE number(9,2), primary key (INV_NUMBER,P_CODE), Foreign key (INV_NUMBER) references INVOICE (INV_NUMBER), Foreign key (P_CODE) references PRODUCT (P_CODE) );

INSERT INTO CUSTOMER VALUES('10010','Ramas','Alfred','A','615','844-2573','0'); INSERT INTO CUSTOMER VALUES('10011','Dunne','Leona','K','713','894-1238','0'); INSERT INTO CUSTOMER VALUES('10012','Smith','Kathy','W','615','894-2285','345.86'); INSERT INTO CUSTOMER VALUES('10013','Olowski','Paul','F','615','894-2180','536.75'); INSERT INTO CUSTOMER VALUES('10014','Orlando','Myron','','615','222-1672','0'); INSERT INTO CUSTOMER VALUES('10015','O''Brian','Amy','B','713','442-3381','0'); INSERT INTO CUSTOMER VALUES('10016','Brown','James','G','615','297-1228','221.19'); INSERT INTO CUSTOMER VALUES('10017','Williams','George','','615','290-2556','768.93'); INSERT INTO CUSTOMER VALUES('10018','Farriss','Anne','G','713','382-7185','216.55'); INSERT INTO CUSTOMER VALUES('10019','Smith','Olette','K','615','297-3809','0');

INSERT INTO INVOICE VALUES('1001','10014','16-jan-2012'); INSERT INTO INVOICE VALUES('1002','10011','16-jan-2012'); INSERT INTO INVOICE VALUES('1003','10012','16-jan-2012'); INSERT INTO INVOICE VALUES('1004','10011','17-jan-2012'); INSERT INTO INVOICE VALUES('1005','10018','17-jan-2012'); INSERT INTO INVOICE VALUES('1006','10014','17-jan-2012'); INSERT INTO INVOICE VALUES('1007','10015','17-jan-2012'); INSERT INTO INVOICE VALUES('1008','10011','17-jan-2012');

INSERT INTO VENDOR VALUES('21225','Bryson, Inc.','Smithson','615','223-3234','TN','Y'); INSERT INTO VENDOR VALUES('21226','SuperLoo, Inc.','Flushing','904','215-8995','FL','N'); INSERT INTO VENDOR VALUES('21231','DE Supply','Singh','615','228-3245','TN','Y'); INSERT INTO VENDOR VALUES('21344','Gomez Bros.','Ortega','615','889-2546','KY','N'); INSERT INTO VENDOR VALUES('22567','Dome Supply','Smith','901','678-1419','GA','N'); INSERT INTO VENDOR VALUES('23119','Randsets Ltd.','Anderson','901','678-3998','GA','Y'); INSERT INTO VENDOR VALUES('24004','Brackman Bros.','Browning','615','228-1410','TN','N'); INSERT INTO VENDOR VALUES('24288','ORDVA, Inc.','Hakford','615','898-1234','TN','Y'); INSERT INTO VENDOR VALUES('25443','BK, Inc.','Smith','904','227-0093','FL','N'); INSERT INTO VENDOR VALUES('25501','Damal Supplies','Smythe','615','890-3529','TN','N'); INSERT INTO VENDOR VALUES('25595','Rubicon Systems','Orton','904','456-0092','FL','Y');

INSERT INTO PRODUCT VALUES('11QER/31','Power painter, 15 psi., 3-nozzle','3-Nov-2011','8','5','109.99','0','25595'); INSERT INTO PRODUCT VALUES('13-Q2/P2','7.25-in. pwr. saw blade','13-dec-2011','32','15', '14.99','0.05','21344'); INSERT INTO PRODUCT VALUES('14-Q1/L3','9.00-in. pwr. saw blade','13-nov-2011','18','12','17.49','0','21344'); INSERT INTO PRODUCT VALUES('1546-QQ2','Hrd. cloth, 1/4-in., 2x50','15-jan-2012','15','8','39.95','0','23119'); INSERT INTO PRODUCT VALUES('1558-QW1','Hrd. cloth, 1/2-in., 3x50','15-jan-2012','23','5','43.99','0','23119'); INSERT INTO PRODUCT VALUES('2232/QTY','BD jigsaw, 12-in. blade','30-dec-2011','8','5','109.92','0.05','24288'); INSERT INTO PRODUCT VALUES('2232/QWE','BD jigsaw, 8-in. blade','24-dec-2011','6','5','99.87','0.05','24288'); INSERT INTO PRODUCT VALUES('2238/QPD','BD cordless drill, 1/2-in.','20-jan-2012','12','5','38.95','0.05','25595'); INSERT INTO PRODUCT VALUES('23109-HB','Claw hammer','20-jan-2012','23','10','9.95','0.1','21225'); INSERT INTO PRODUCT VALUES('23114-AA','Sledge hammer, 12 lb.','2-jan-2012','8','5','14.40','0.05',NULL); INSERT INTO PRODUCT VALUES('54778-2T','Rat-tail file, 1/8-in. fine','15-dec-2011','43','20','4.99','0','21344'); INSERT INTO PRODUCT VALUES('89-WRE-Q','Hicut chain saw, 16 in.','7-feb-2012','11','5','256.99','0.05','24288'); INSERT INTO PRODUCT VALUES('PVC23DRT','PVC pipe, 3.5-in., 8-ft','20-feb-2012','188','75','5.87','0',NULL); INSERT INTO PRODUCT VALUES('SM-18277','1.25-in. metal screw, 25','1-mar-2012','172','75','6.99','0','21225'); INSERT INTO PRODUCT VALUES('SW-23116','2.5-in. wd. screw, 50','24-feb-2012','237','100','8.45','0','21231'); INSERT INTO PRODUCT VALUES('WR3/TT3','Steel matting, 4''x8''x1/6", .5" mesh','17-jan-2012','18','5','119.95','0.1','25595');

INSERT INTO LINE VALUES('1001','1','13-Q2/P2','1','14.99'); INSERT INTO LINE VALUES('1001','2','23109-HB','1','9.95'); INSERT INTO LINE VALUES('1002','1','54778-2T','2','4.99'); INSERT INTO LINE VALUES('1003','1','2238/QPD','1','38.95'); INSERT INTO LINE VALUES('1003','2','1546-QQ2','1','39.95'); INSERT INTO LINE VALUES('1003','3','13-Q2/P2','5','14.99'); INSERT INTO LINE VALUES('1004','1','54778-2T','3','4.99'); INSERT INTO LINE VALUES('1004','2','23109-HB','2','9.95'); INSERT INTO LINE VALUES('1005','1','PVC23DRT','12','5.87'); INSERT INTO LINE VALUES('1006','1','SM-18277','3','6.99'); INSERT INTO LINE VALUES('1006','2','2232/QTY','1','109.92'); INSERT INTO LINE VALUES('1006','3','23109-HB','1','9.95'); INSERT INTO LINE VALUES('1006','4','89-WRE-Q','1','256.99'); INSERT INTO LINE VALUES('1007','1','13-Q2/P2','2','14.99'); INSERT INTO LINE VALUES('1007','2','54778-2T','1','4.99'); INSERT INTO LINE VALUES('1008','1','PVC23DRT','5','5.87'); INSERT INTO LINE VALUES('1008','2','WR3/TT3','3','119.95'); INSERT INTO LINE VALUES('1008','3','23109-HB','1','9.95');

4. Using the output shown in the figure below as your guide, generate the listing of customer purchases, including the subtotals for each of the invoice line numbers. (Hint: Modify the query format used to produce the listing of customer purchases in the previous Problem, delete the INV_DATE column, and add the derived (computed) attribute LINE_UNITS * LINE_PRICE to calculate the subtotals.)

Summary of Customer Purchases with Subtotals

5. Modify the query used in the previous Problem to produce the summary shown in the figure below.

Customer Purchase Summary

6. Modify the query in the previous Problem to include the number of individual product purchases made by each customer. (In other words, if the customers invoice is based on three products, one per LINE_NUMBER, you would count three product purchases. If you examine the original invoice data, you will note that customer 10011 generated three invoices, which contained a total of six lines, each representing a product purchase.) Your output values must match those shown in the figure below.

Customer Total Purchase Amounts and Number of Purchases

7. Use a query to compute the average purchase amount per product made by each customer. (Hint:Use the results of the previous Problem as the basis for this query.) Your output values must match those shown in the figure below. Note that the Average Purchase Amount is equal to the Total Purchases divided by the Number of Purchases.

Average Purchase Amount by Customer

8. Create a query to produce the total purchase per invoice, generating the results shown in in the figure below. The Invoice Total is the sum of the product purchases in the LINE that corresponds to the INVOICE.

Invoice Totals

9. Use a query to show the invoices and invoice totals as shown in the figure below. (Hint: Group by the CUS_CODE.)

Invoice Totals by Customer

10. Write a query to produce the number of invoices and the total purchase amounts by customer, using the output shown in the figure below as your guide. (Compare this summary to the results shown in the previous Problem.)

Number of Invoices and Total Purchase Amounts by Customer

11. Using the query results in the previous Problem as your basis, write a query to generate the total number of invoices, the invoice total for all of the invoices, the smallest invoice amount, the largest invoice amount, and the average of all of the invoices. (Hint: Check the figure output in the previous Problem.) Your output must match the figure below.

Number of Invoices, Invoice Totals, Minimum, Maximum, and Average Sales

12. List the balance characteristics of the customers who have made purchases during the current invoice cyclethat is, for the customers who appear in the INVOICE table. The results of this query are shown in the figure below.

Balances for Customers who Made Purchases

13. Using the results of the query created in the previous Problem, provide a summary of customer balance characteristics as shown in the figure below.

Balance Summary for Customers Who Made Purchases

14. Create a query to find the customer balance characteristics for all customers, including the total of the outstanding balances. The results of this query are shown in the figure below.

Customer Balance Summary for All Customers

15. Find the listing of customers who did not make purchases during the invoicing period. Your output must match the output shown in the figure below.

Customer Balances for Customers Who Did Not Make Purchases

16. Find the customer balance summary for all customers who have not made purchases during the current invoicing period. The results are shown in the figure below.

Summary of Customer Balances for Customers Who Did Not Make Purchases

17. Create a query to produce the summary of the value of products currently in inventory. Note that the value of each product is produced by the multiplication of the units currently in inventory and the unit price.

Value of Products in Inventory

18. Using the results of the query created in the previous Problem, find the total value of the product inventory. The results are shown in the figure below.

Total Value of All Products in Inventory

P_DESCRIPT Units Bought Unit Price Subtotal CUS CODE INV NUMBER 10011 10011 10011 10011 10011 10011 10012 10012 10012 10014 10014 10014 10014 10014 10014 10015 10015 10018 1002 Rat-tail file, 1/8-in. fine 1004 Claw hammer 1004 Rat-tail file, 1/8-in. fine 1008 Claw hammer 1008 PVC 1008 Steel matting, 4'x8'x1/6" .5" mesh 003 7.25-in 1003 B&D cordless drill, 1/2-in 1003 Hrd. cloth, 1/4-in., 2x50 1001 7.25-in 1001 Claw hammer 1006 1.25-in. metal screw, 25 1006 B&D jigsaw, 12-in. blade 1006 Claw hammer 1006 Hicut chain saw, 16 in 1007 7.25-in 1007 Rat-tail file, 1/8-in. fine 1005 PVC pipe, 3.5-in., 8-ft 4.99 9.95 4.99 9.95 5.87 119.95 14.99 38.95 39.95 14.99 9.95 6.99 109.92 9.95 256.99 14.99 4.99 5.87 9.98 19.90 14.97 9.95 29.35 359.85 74.95 38.95 39.95 14.99 9.95 20.97 109.92 9.95 256.99 29.98 4.99 70.44 3.5-in., 8-ft saw blade saw blade saw blade