Database Systems Sale

FIGURE P7.26 THE CH07_SALECO DATABASE 26. Write a query to count the number of invoices. SELECT COUNT(*) FROM INVOICE; 27. Write a query to count the number of customers with a customer balance over $500. SELECT COUNT(*) FROM CUSTOMER WHERE CUS_BALANCE >500; 28. Generate a listing of all purchases made by the customers, using the output shown in Figure P7.28 as your guide. (Hint: Use the ORDER BY clause to order the resulting rows as shown in Figure P7.28) FIGURE P7.28 List of Customer Purchases SELECT INVOICE.CUS_CODE, INVOICE.INV_NUMBER, INVOICE.INV_DATE, PRODUCT.P_DESCRIPT, LINE.LINE_UNITS, LINE.LINE_PRICE 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; 29. Using the output shown in Figure P7.29 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 Problem 18, delete the INV_DATE column, and add the derived (computed) attribute LINE_UNITS * LINE_PRICE to calculate the subtotals.) FIGURE P7.29 Summary of Customer Purchases with Subtotals SELECT FROM WHERE AND AND ORDER BY 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 CUSTOMER, INVOICE, LINE, PRODUCT CUSTOMER.CUS_CODE = INVOICE.CUS_CODE INVOICE.INV_NUMBER = LINE.INV_NUMBER PRODUCT.P_CODE = LINE.P_CODE 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. FIGURE P7.30 Customer Purchase Summary 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 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 Figure P7.31. FIGURE P7.31 Customer Total Purchase Amounts and Number of Purchases SELECT INVOICE.CUS_CODE, CUSTOMER.CUS_BALANCE, Sum(LINE.LINE_UNITS*LINE.LINE_PRICE) AS 'Total Purchases', Count(*) AS 'Number of 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; 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. FIGURE P7.32 Average Purchase Amount by Customer SELECT INVOICE.CUS_CODE, CUSTOMER.CUS_BALANCE, Sum(LINE.LINE_UNITS*LINE.LINE_PRICE) AS 'Total Purchases', Count(*) AS 'Number of Purchases', AVG(LINE.LINE_UNITS*LINE.LINE_PRICE) AS 'Average Purchase Amount' 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; 33. Create a query to produce the total purchase per invoice, generating the results shown in Figure P7.33. The Invoice Total is the sum of the product purchases in the LINE that corresponds to the INVOICE. FIGURE P7.33 Invoice Totals SELECT LINE.INV_NUMBER, Sum(LINE.LINE_UNITS*LINE.LINE_PRICE) AS 'Invoice Total' FROM LINE GROUP BY LINE.INV_NUMBER; 34. Use a query to show the invoices and invoice totals as shown in Figure P7.34. (Hint: Group by the CUS_CODE.) FIGURE P7.34 Invoice Totals by Customer SELECT FROM WHERE GROUP BY CUS_CODE, LINE.INV_NUMBER AS INV_NUMVER, Sum(LINE.LINE_UNITS*LINE.LINE_PRICE) AS INV_TOT INVOICE, LINE INVOICE.INV_NUMBER = LINE.INV_NUMBER CUS_CODE, LINE.INV_NUMBER; 35. Write a query to produce the number of invoices and the total purchase amounts by customer, using the output shown in Figure P7.35 as your guide. (Compare this summary to the results shown in Problem 34.) FIGURE P7.35 Number of Invoices and Total Purchase Amounts by Customer You can use any aggregate function such as Average, Max, Min, Sum etc. as shown below: SELECT CUS_CODE, COUNT(INV_NUMBER) AS 'Number of Invoices', AVG(INV_TOT) AS 'Average Invoice Amount', MAX(INV_TOT) AS 'Max Invoice Amount', MIN(INV_TOT) AS 'Min Invoice Amount', Sum(INV_TOT) AS 'Total Customer Purchases' FROM (SELECT CUS_CODE, L.INV_NUMBER AS INV_NUMBER, Sum(L.LINE_UNITS*L.LINE_PRICE) AS INV_TOT FROM INVOICE I, LINE L WHERE I.INV_NUMBER = L.INV_NUMBER GROUP BY CUS_CODE, L.INV_NUMBER) AS IL GROUP BY CUS_CODE; 36. Using the query results in Problem 35 as your basis, 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 of the customer purchase amounts . (Hint: Check the figure output in Problem 35.) Your output must match Figure P7.36. FIGURE P7.36 Number of Invoices, Invoice Totals, Minimum, Maximum, and Average Sales SELECT Count(INV_NUMBER) AS 'Total Invoices', Sum(INV_TOT) AS 'Total Sales', Min(INV_TOT) AS 'Minimum Sale', Max(INV_TOT) AS 'Largest Sale', Avg(INV_TOT) AS 'Average Sale' CUS_CODE, L.INV_NUMBER AS INV_NUMBER, FROM (SELECT Sum(L.LINE_UNITS*L.LINE_PRICE) AS INV_TOT INVOICE I, LINE L FROM I.INV_NUMBER = L.INV_NUMBER WHERE GROUP BY CUS_CODE, L.INV_NUMBER ) IL; 37. List the balance characteristics of the customers who have made purchases during the current invoice cycle—that is, for the customers who appear in the INVOICE table. The results of this query are shown in Figure P7.37. FIGURE P7.37 Balances for Customers who Made Purchases SELECT FROM WHERE CUS_CODE, CUS_BALANCE CUSTOMER CUSTOMER.CUS_CODE IN (SELECT DISTINCT CUS_CODE FROM INVOICE ); or SELECT FROM WHERE DISTINCT CUS_CODE, CUS_BALANCE CUSTOMER, INVOICE CUSTOMER.CUS_CODE = INVOICE.CUS_CODE; 38. Using the results of the query created in Problem 37, provide a summary of customer balance characteristics as shown in Figure P7.38. FIGURE P7.38 Balance Summary for Customers Who Made Purchases SELECT MIN(CUS_BALANCE) AS 'Minimum Balance', MAX(CUS_BALANCE) AS 'Maximum Balance', AVG(CUS_BALANCE) AS 'Average Balance' FROM (SELECT CUS_CODE, CUS_BALANCE FROM CUSTOMER WHERE CUSTOMER.CUS_CODE IN (SELECT DISTINCT CUS_CODE FROM INVOICE) ) AS C; or SELECT MIN(CUS_BALANCE) AS 'Minimum Balance', MAX(CUS_BALANCE) AS 'Maximum Balance', AVG(CUS_BALANCE) AS 'Average Balance' FROM (SELECT DISTINCT CUSTOMER.CUS_CODE, CUS_BALANCE FROM CUSTOMER, INVOICE WHERE CUSTOMER.CUS_CODE = INVOICE.CUS_CODE) AS CI; or SELECT MIN(CUS_BALANCE) AS 'Minimum Balance', MAX(CUS_BALANCE) AS 'Maximum Balance', AVG(CUS_BALANCE) AS 'Average Balance' FROM CUSTOMER WHERE CUS_CODE IN (SELECT CUS_CODE FROM INVOICE); 39. 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 Figure P7.39. FIGURE P7.39 Customer Balance Summary for All Customers SELECT FROM Sum(CUS_BALANCE) AS 'Total Balance', Min(CUS_BALANCE) AS 'Minimum Balance', Max(CUS_BALANCE) AS 'Maximum Balance', Avg(CUS_BALANCE) AS 'Average Balance' CUSTOMER; 40. Find the listing of customers who did not make purchases during the invoicing period. Your output must match the output shown in Figure P7.40. FIGURE P7.40 Customer Balances for Customers Who Did Not Make Purchases SELECT CUS_CODE, CUS_BALANCE FROM CUSTOMER WHERE CUSTOMER.CUS_CODE NOT IN (SELECT DISTINCT CUS_CODE FROM INVOICE); 41. Find the customer balance summary for all customers who have not made purchases during the current invoicing period. The results are shown in Figure P7.41. FIGURE P7.41 Summary of Customer Balances for Customers Who Did Not Make Purchases SELECT FROM SUM(CUS_BALANCE) AS 'Total Balance', MIN(CUS_BALANCE) AS 'Minimum Balance', MAX(CUS_BALANCE) AS 'Maximum Balance', AVG(CUS_BALANCE) AS 'Average Balance' (SELECT CUS_CODE, CUS_BALANCE FROM CUSTOMER WHERE CUSTOMER.CUS_CODE NOT IN (SELECT DISTINCT CUS_CODE FROM INVOICE) ) AS C; or SELECT SUM(CUS_BALANCE) AS 'Total Balance', MIN(CUS_BALANCE) AS 'Minimum Balance', MAX(CUS_BALANCE) AS 'Maximum Balance', AVG(CUS_BALANCE) AS 'Average Balance' FROM CUSTOMER WHERE CUS_CODE NOT IN (SELECT CUS_CODE FROM INVOICE); 42. 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. Use the ORDER BY clause to match the order shown in Figure P7.42. FIGURE P7.42 Value of Products in Inventory SELECT FROM P_DESCRIPT, P_QOH, P_PRICE, P_QOH*P_PRICE AS Subtotal PRODUCT; 43. Using the results of the query created in Problem 42, find the total value of the product inventory. The results are shown in Figure P7.43. FIGURE P7.43 Total Value of All Products in Inventory SELECT FROM SUM(P_QOH*P_PRICE) AS 'Total Value of Inventory' PRODUCT;