DATABASE
Table name: CUSTOMER CUS_CODE CUS_LNAME CUS_FNAME CUS_INITIAL CUS AREACODE CUS_PHONE CUS_BALANCE 10010 Raras Alfred A 615 844-2573 0.00 10011 Dunne Leona K 713 894-1238 0.00 10012 Smith Kathy 516 891-2285 315.86 10013 Olowski Paul F 615 894-2180 536 75 10014 Orlando Myron 615 222-1672 0.00 10015 O'Brian Amy B 713 442-3381 0.00 10016 Brown James G 615 297-1228 221.19 10017 Williams George 615 290-2556 768.93 1001B Farriss Anne G 713 382-7186 216.55 10019 Smith Dleite 615 297-3809 0.00 Table name: INVOICE INV_NUMBER CUS_CODE INV_DATE INV_SUBTOTAL 1001 10014 16-Jan-18 24.90 1002 10011 16-Jan-18 9.98 1003 10012 16-Jan-18 153.85 1004 10011 17-Jan-18 34.97 1005 10018 17-Jan-18 70.44 1006 10014 17-Jan-18 397.83 1007 10015 17-Jan-18 34.97 1008 10011 17-Jan-18 399.15 INV_TAX 1.99 0.80 12.31 2.80 5.64 31.83 2.80 31.93 INV_TOTAL 26.89 10.78 166.16 37.77 76.08 429.66 37.77 431.08 0.00 23119 Table name: PRODUCT P_CODE P_DESCRIPT 11QER/31 Power painter, 15 psi., 3-nozzle 13-02/P2 7.25-in. pwr. saw blade 14-Q113 9.00-in. pwr. saw blade 1546-QQ2 Hrd. cloth, 1/4-in., 2x50 1558-QW1 Hrd, cloth, 1/2-in., 3x50 2232/QTY B&D jigsaw, 12-in. blade 2232/QWE B&D jigsaw, 8-in. blade 2238/QPD B&D cordless drill, 1/2-in. 23109-HB Claw hammer 23114-AA Sledge hammer, 12 lb. 54778-2T Rat-tail file, 1/8-in fine 89-WRE-Q Hicut chain saw, 16 in. PVC23DRT PVC pipe, 3.5-in., 8-ft SM-18277 1.25-in. metal screw, 25 SW-23116 2.5-in, wd. screw, 50 WR3/TT3 Steel matting, 4'x8x1/6", 5" mesh P_INDATE P_QOH P_MIN P_PRICE P_DISCOUNT V_CODE 03-Nov-17 8 5 109.99 0.00 25595 13-Dec-17 32 15 14.99 0.05 21344 13-Nov-17 18 12 17.49 21344 15-Jan-18 15 8 39.95 0.00 23119 15-Jan-18 23 5 43.99 0.00 30-Dec-17 8 5 109.92 0.05 24288 24-Dec-17 5 99.87 0.05 24288 20-Jan-18 12 5 38.95 0.05 25595 20-Jan-18 23 10 9.95 0.10 21225 02-Jan-18 8 5 14.40 0.05 15-Dec-17 43 20 0.00 21344 07-Feb-18 11 5 256.99 0.05 24288 20-Feb-18 188 75 5.87 0.00 01-Mar-18 172 75 6.99 0.00 21225 24-Feb-18 237 100 8.45 0.00 21231 17-Jan-18 18 5 119.95 0.10 25595 Table name: LINE INV_NUMBER LINE_NUMBER P_CODE 1001 1 13-Q2/P2 1001 2 23100 HE 1002 1 54778-21 1003 1 223B/QPD 1003 2 1546-QQ2 1005 3 13 02/P2 1004 1 54778-2T 1004 2 231 09-HB 1005 1 PVC230RT 1005 1 SM-18277 1005 2 2232/QTY 3 23109-HE 1005 4 89 WRE- 1007 1 13-02/P2 1007 2 54778-2T 1003 1 PVC23DRT 1003 2 WRB/TT3 1003 3 23109-HE LINE_UNITS LINE PRICE 1 14.99 1 9.96 2 4.99 1 38.06 1 39.95 14.99 3 4.99 2 9.95 12 5.87 3 5.99 1 109.92 9.96 1 256.99 2 14.99 1 4.99 5 3 119.96 1 996 FONAMEN LINE TOTAL 14.99 9.95 9.98 38 95 39.95 74.96 11.97 1990 70.44 20.97 109 92 9.95 256.99 29.98 1.99 29 35 359 86 995 1005 4.99 5.87 Table name: VENDOR V_CODE V_NAME V_CONTACT | VAREACODE 21225 Bryson, Inc Smithson 615 21226 SuperLop, Inc. Flushing 904 21231 DSE Supply Singh 615 21341 Gomez Bros Ortega 615 22567 Dome Supply Smith 901 28119 Randsets Lid Anderson 901 24004 Brackman Bros Browning 615 24288 OROVA, Inc Hakford 615 25443 B&K, Inc. Smith 904 25501 Damal Supplies Smythe 615 25595 Rubicon Systems Orton 904 V PHONE 223-3234 215-8996 228-3245 889-2546 678-1419 67B-3928 226-1410 898-1224 227-0096 890-3529 456-0092 V_STATE TN FL TN KY GA GA TN TN FL TN FL V_ORDER Y N Y N N Y N Y N N V 1- Generate a listing of all purchases made by the customers, using the output shown below as your guide. Sort the results by customer code, invoice number, and product description. (5 marks) CUS CODE INV NUMBER INV DATE P DESCRIPT 10011 1002 16-Jan-18 Rat-talfie, 118-n tine 10011 1004 17-Jan-18 Claw hammer 10011 1004 17-Jan-18 Rot.talle, 1.8-in fine 10011 1008 17-Jan-18 Claw hammer 10011 1006 17-Jan-18 PVC pipe, 3.5-in, B-ft 10011 1006 17-Jan-18 Steel matting, 4x8x16, 5' mesh 10012 1003 16-Jan-18 7 25.in. pwr. w blade 10012 1003 16-Jan-18 B8D cordless dril, 12-n 10012 1003 16-Jan-18 Mrd. cloth, 1/4-n, 2x50 10014 1001 16-Jan-18 7 25.in. pwr. w bade 10014 1001 16-Jan-18 Claw hammer 10014 1006 17-Jan-18 1 25-in metal screw, 25 10014 1006 17-Jan-18 B&D jigsaw, 12-in blade 10014 1006 17-Jan-18 Claw hammer 10014 1006 17-Jan-18 Hout chain saw, 16 in 10015 1007 17-Jan-18 7 25-in. pwr. sow blode 10015 1007 17-Jan-18 Rat-tail file, 1.8-in fine 10018 1005 17-Jan-18 PVC pipe, 3.5-in., 8-ft LINE UNTS LINE PRICE 2 4.99 2 9.95 3 4.99 1 9.95 5 5.87 119.95 5 14.99 1 38.96 1 39.95 1 14.99 1 9.95 3 6.99 1 109.92 1 9.95 1 256.99 2 14.99 1 4.99 12 5.87 2- Using the output shown below as your guide, generate a list of customer purchases, including the subtotals for each of the invoice line numbers. The subtotal is a derived attribute calculated by multiplying LINE_UNITS by LINE_PRICE. Sort the output by customer code, invoice number, and product description. Be certain to use the column aliases as shown in the figure. (5 marks) Units Bought 2 2 3 1 CUS CODE INV NUMBER P DESCRIPT 10011 1002 Rat-tail file, 1/8-in, fine 10011 1004 Claw hammer 10011 1004 Rat-tail file, 1/8-in. fine 10011 1008 Claw hammer 10011 1008 PVC pipe, 3.5-in., 8-ft 10011 1008 Steel matting, 4x8x1/6" 5" mesh 10012 1003 7 25-in. pwr. saw blade 10012 1003 B&D cordless drill, 1/2-in. 10012 1003 Hrd. cloth, 1/4-in., 2x50 10014 1001 7 25-in. pwr. saw blade 10014 1001 Claw hammer 10014 1006 1.25-in metal screw, 25 10014 1006 B&D jigsaw, 12-in. blade 10014 1006 Claw hammer 10014 1006 Hicut chain saw, 16 in 10015 1007 7 25-in. pwr. saw blade 10015 1007 Rat-tail file, 1/8-in. fine 10018 1005 PVC pipe, 3.5-in., 8-ft 5 1 1 1 1 3 1 1 1 2 1 12 Unit Price 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 Subtotal 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- Write a query to display the customer code, balance, and total purchases for each customer. Total purchase is calculated by summing the line subtotals (as in question 2) for each customer. Sort the results by customer code, and use aliases as shown below. (5 marks) CUSCODECUS BALANCE Total Puches 10011 600 400 10012 3455 153 05 10014 0.00 10015 10018 21655 4- Modify the query in (question 3) to include the number of individual product purchases made by each customer. (In other words, if the customer's invoice is based on three products, one per LINE_NUMBER, you count three product purchases. Note that in the original invoice data, customer 10011 generated three invoices, which contained a total of six lines, each representing a product purchase.) The output values must match those shown in Figure below, sorted by customer code. (5 marks) CUS_CODE CUS_BALANCE Total Purchases Number of Purchases 10011 000 10012 10014 000 10015 10018 21656 7044 5- Use a query to compute the total of all purchases, the number of purchases, and the average purchase amount made by each customer. The output values must match those shown in Figure below. Sort the results by customer code. (5 marks) CUS_CODE CUS_BALANCE Total Purchases Number of Purchases 10011 0.00 444 00 6 10012 345.86 153.85 3 10014 0.00 422.77 6 10015 0.00 34.97 2 10018 216.55 70.44 1 Average Purchase Amount 74.00 51.28 70.46 17.48 70.44 6- Create a query to produce the total purchase per invoice, generating the results shown below, sorted by invoice number. The invoice total is the sum of the product purchases in the LINE that corresponds to the INVOICE. (5 marks) INV NUMBER Invoice Total 1001 24 94 1002 9.98 1005 153.85 1004 34.87 1005 70.44 1006 397.83 1007 34.97 1008 399.15 7- Write a query to produce the number of invoices and the total purchase amounts by customer, using the output shown in Figure below as your guide. The results are sorted by customer code. (5 marks) CUS_CODE Number of invoices Total Customer Purchases 10011 444 00 10012 1 153 851 10014 2 10015 10018 70.44 1 8- Write a query to generate the total number of invoices, the invoice total for all of the invoices, the smallest of the customer purchase amounts, the largest of the customer purchase amounts, and the average of all the customer purchase amounts. The output must match Figure below. (5 marks) Total Invoices Total Sales Minimum Customer Purchases Largest Customer Purchases Average Customer Purchases 1126.03 34.97 444.00 225.21 9- Create a query that summarizes the value of products currently in inventory. Note that the value of each product is a result of multiplying the units currently in inventory by the unit price. Sort the results in descending order by subtotal, as shown in Figure below. (5 marks) P DESCRPT Hicut chain saw, 16 in Steel matting, 48%16', 5* mesh 2.5-in. wd. screw, 50 1.25-in, metal screw, 25 PVC pipe, 35-n, 8-1 Hrd cloth 1/2-in., 3.50 Power painter 15 psi. 3-nozzle BSD jigsaw, 12.in. blade Hrd. cloth, 1/4-In., 22:50 BSD jigsaw, 8-in blade 7.25-in. pwr. saw blade B&D cordless dril, 1/2.n 9.00-in. pwr. saw blade Claw hammer Rat-tail file, 1/6-in fine Sledgehammer, 12 b. PAGOH P PRICE 11 296.99 16 119.95 237 8.45 172 6.99 188 5.87 23 4399 8 109.99 8 109.92 15 39 95 6 99 87 32 14.99 12 38.95 18 17.49 23 9.95 43 4.99 8 14.40 Subtotal 2826.89 2159.10 2002.65 1202.28 1103,56 1011.77 879.92 879.36 599.25 599.22 479,68 467.40 314.82 228.85 214.57 115.20 10- Find the total value of the product inventory. The results are shown in Figure below. (5 marks) Total Value of Inventory 15084.52 11- Write a query to count the number of customers with a balance of more than $500. (2 Marks)
