29.Using the output shown in Figure P7.29 as your guide, generate a list of cust

Question

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

Explanation / Answer

If you have any doubts, please give me comment...

-- 29)

SELECT I.CUS_CODE, I.INV_NUMBER, P.P_DESCRIPT, L.LINE_UNITS AS [Units Bought], L.LINE_PRICE AS [Unit Price], L.LINE_UNITS*L.LINE_PRICE AS Subtotal

FROM CUSTOMER C, INVOICE I, LINE L, PRODUCT P

WHERE C.CUS_CODE = I.CUS_CODE AND I.INV_NUMBER = L.INV_NUMBER

AND P.P_CODE = L.P_CODE

ORDER BY I.CUS_CODE, I.INV_NUMBER, P.P_DESCRIPT;

-- 30)

SELECT I.CUS_CODE, C.CUS_BALANCE, SUM(L.LINE_UNITS * L.LINE_PRICE) AS [Total Purchases]

FROM CUSTOMER C, INVOICE I, LINE L

WHERE I.INV_NUMBER = L.INV_NUMBER AND C.CUS_CODE = I.CUS_CODE

GROUP BY I.CUS_CODE, C.CUS_BALANCE;

-- 31)

SELECT I.CUS_CODE, C.CUS_BALANCE, SUM(L.LINE_UNITS * L.LINE_PRICE) AS [Total Purchases], COUNT(*) AS [Number of Purchases]

FROM CUSTOMER C, INVOICE I, LINE L

WHERE I.INV_NUMBER = L.INV_NUMBER AND C.CUS_CODE = I.CUS_CODE

GROUP BY I.CUS_CODE, C.CUS_BALANCE;

-- 32)

SELECT I.CUS_CODE, C.CUS_BALANCE, SUM(L.LINE_UNITS * L.LINE_PRICE) AS [Total Purchases], COUNT(*) AS [Number of Purchases], AVG(L.LINE_UNITS * L.LINE_PRICE) AS [Average Purchase Amount]

FROM CUSTOMER C, INVOICE I, LINE L

WHERE I.INV_NUMBER = L.INV_NUMBER AND C.CUS_CODE = I.CUS_CODE

GROUP BY I.CUS_CODE, C.CUS_BALANCE;

Related Questions

Navigate

• Browse (All)
• Browse 2
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.