29.Using the output shown in Figure P7.29 as your guide, generate a list of customer purchases, including the subtotals for each of the invoice line numbers. (Hint: Modify the query format used to produce the list of customer purchases in Problem 28, delete the INV_DATE column, and add the derived attribute LINE_ UNITS * LINE_PRICE to calculate the subtotals.)

SELECT INVOICE.CUS_CODE, INVOICE.INV_NUMBER, PRODUCT.P_DESCRIPT,

LINE.LINE_UNITS AS [Units Bought], LINE.LINE_PRICE AS [Unit Price],

LINE.LINE_UNITS*LINE.LINE_PRICE AS Subtotal

FROM CUSTOMER, INVOICE, LINE, PRODUCT

WHERE CUSTOMER.CUS_CODE = INVOICE.CUS_CODE

AND INVOICE.INV_NUMBER = LINE.INV_NUMBER

AND PRODUCT.P_CODE = LINE.P_CODE

ORDER BY INVOICE.CUS_CODE, INVOICE.INV_NUMBER, PRODUCT.P_DESCRIPT;

30.Modify the query used in Problem 29 to produce the summary shown in Figure P7.30.

SELECT INVOICE.CUS_CODE, CUSTOMER.CUS_BALANCE,

Sum(LINE.LINE_UNITS*LINE.LINE_PRICE) AS [Total Purchases]

FROM CUSTOMER, INVOICE, LINE

WHERE INVOICE.INV_NUMBER = LINE.INV_NUMBER

AND CUSTOMER.CUS_CODE = INVOICE.CUS_CODE

GROUP BY INVOICE.CUS_CODE, CUSTOMER.CUS_BALANCE;

31.Modify the query in Problem 30 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.) Your output values must match those shown in Figure P7.31.

32.Use a query to compute the average purchase amount per product made by each customer. (Hint: Use the results of Problem 31 as the basis for this query.) Your output values must match those shown in Figure P7.32. Note that the average purchase amount is equal to the total purchases divided by the number of purchases per customer.

P DESCRIPT Units Bought Unit PriceSubtotal CUS CODE INV NUMBER 4.99 1002 Rat-tail file, 1/8-in. fine 1004 Claw hammer 1004 Rat-tail file, 1/8-in. fine 008 Claw hammer 1008 PVC pipe, 3.5-in, 8-ft 1008 Steel matting, 4x8x1/6", 5" mesh 1003 7.25-in. pwr. saw blade 1003 B&D cordless drill, 1/2-in. 1003 Hrd. cloth, 1/4-in., 2x50 1001 7.25-in. pwr. saw blade 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. pwr. saw blade 1007 Rat-tail file, 1/8-in. fine 19.90 14.97 9.95 29.35 10011 2 3 4.99 9.95 5.87 19.95 14.99 10011 10011 10011 10011 10012 10012 10012 10014 10014 10014 10014 10014 10014 10015 10015 3 74.95 139.95 14.99 9.95 6.99 109.92 9.95 256.99 14.99 4.99 5.87 39.95 14.99 9.95 20.97 109.92 9.95 256.99 29.98 2 4.99 12 70.44 005 PVC pipe, 3.5-in., 8-ft