IST 331 | Chapter 7 1-5, 8-12, 20-26

List the balances of customers who have made purchases during the current invoice cycle—that is, for the customers who appear in the INVOICE table. Sort the results by customer code, as shown in Figure P7.20.

SELECT CUS_CODE, CUS_BALANCE FROM CUSTOMER WHERE CUSTOMER.CUS_CODE IN(SELECT DISTINCT CUS_CODE FROM INVOICE);

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 P7.25.

SELECT P_DESCRIPT,P_QOH, P_PRICE, ROUND(P_QOH*P_PRICE,2) AS "Subtotal" FROM PRODUCT ORDER BY Subtotal DESC;

Write a query to count the number of invoices.

SELECT COUNT(*) FROM INVOICE;

Find the listing of customers who did not make purchases during the invoicing period. Sort the results by customer code. Your output must match the output shown in Figure P7.23.

SELECT CUS_CODE, CUS_BALANCE FROM CUSTOMER WHERE CUS_CODE NOT IN(SELECT CUS_CODE FROM INVOICE);

Provide a summary of customer balance characteristics for customers who made purchases. Include the minimum balance, maximum balance, and average balance, as shown in Figure P7.21.

SELECT MIN(CUS_BALANCE) AS "Minimum Balance", MAX(CUS_BALANCE) AS "Maximum Balance", ROUND(AVG(CUS_BALANCE),2) AS "Average Balance" FROM CUSTOMER WHERE CUS_CODE IN (SELECT CUS_CODE FROM INVOICE);

Create a query to find the balance characteristics for all customers, including the total of the outstanding balances. The results of this query are shown in Figure P7.22.

SELECT SUM(CUS_BALANCE) AS "Total Balance", MIN(CUS_BALANCE) AS "Minimum Balance", MAX(CUS_BALANCE) AS "Maximum Balance", ROUND(AVG(CUS_BALANCE), 2) AS "Average Balance" FROM CUSTOMER;

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.24.

SELECT SUM(CUS_BALANCE) AS "Total Balance", MIN(CUS_BALANCE) AS "Minimum Balance", MAX(CUS_BALANCE) AS "Maximum Balance", ROUND(AVG(CUS_BALANCE),2) AS "Average Balance" FROM CUSTOMER WHERE CUS_CODE NOT IN (SELECT DISTINCT CUS_CODE FROM INVOICE);

Find the total value of the product inventory. The results are shown in Figure P7.26.

SELECT SUM(P_QOH*P_PRICE) AS "Total Value of Inventory" FROM PRODUCT;

Write a query to count the number of customers with a balance of more than $500.

SELECT COUNT(*) FROM CUSTOMER WHERE CUS_BALANCE > 500; SELEC

Generate a listing of all purchases made by the customers, using the output shown in Figure P7.11 as your guide. Sort the results by customer code, invoice number, and product description.

SELECT CUSTOMER.CUS_CODE, INVOICE.INV_NUMBER, INVOICE.INV_DATE, PRODUCT.P_DESCRIPT, LINE.LINE_UNITS, LINE.LINE_PRICE FROM CUSTOMER JOIN INVOICE ON CUSTOMER.CUS_CODE=INVOICE.CUS_CODE JOIN LINE ON INVOICE.INV_NUMBER=LINE.INV_NUMBER JOIN PRODUCT ON LINE.P_CODE=PRODUCT.P_CODE ORDER BY CUS_CODE, INV_NUMBER, P_DESCRIPT;

Using the output shown in Figure P7.12 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.

SELECT I.CUS_CODE, I.INV_NUMBER, P.P_DESCRIPT, L.LINE_UNITS AS "Units Bought", L.LINE_PRICE AS "Unit Price", ROUND(L.LINE_UNITS * L.LINE_PRICE, 2) 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;