- Information
- AI Chat
Ch7 - Ch7
Database Systems (ITC423)
Charles Sturt University
Recommended for you
Related documents
Preview text
FIGURE P7 THE CH07_SALECO DATABASE
- Write a query to count the number of invoices.
SELECT COUNT(*) FROM INVOICE;
- Write a query to count the number of customers with a customer balance over $500.
SELECT COUNT(*) FROM CUSTOMER WHERE CUS_BALANCE >500;
- Generate a listing of all purchases made by the customers, using the output shown in Figure P7 as your guide. (Hint: Use the ORDER BY clause to order the resulting rows as shown in Figure P7)
FIGURE P7 List of Customer Purchases
SELECT INVOICE_CODE, INVOICE_NUMBER, INVOICE_DATE,
PRODUCT_DESCRIPT, LINE_UNITS, LINE_PRICE
FROM CUSTOMER, INVOICE, LINE, PRODUCT
WHERE CUSTOMER_CODE = INVOICE_CODE
AND INVOICE_NUMBER = LINE_NUMBER
AND PRODUCT_CODE = LINE_CODE
ORDER BY INVOICE_CODE, INVOICE_NUMBER, PRODUCT_DESCRIPT;
- Modify the query used in Problem 29 to produce the summary shown in Figure P7.
FIGURE P7 Customer Purchase Summary
SELECT INVOICE_CODE, CUSTOMER_BALANCE,
Sum(LINE_UNITS*LINE_PRICE) AS 'Total Purchases' FROM CUSTOMER, INVOICE, LINE WHERE INVOICE_NUMBER = LINE_NUMBER AND CUSTOMER_CODE = INVOICE_CODE GROUP BY INVOICE_CODE, CUSTOMER_BALANCE;
- 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.
FIGURE P7 Customer Total Purchase Amounts and Number of Purchases
SELECT INVOICE_CODE, CUSTOMER_BALANCE,
Sum(LINE_UNITSLINE_PRICE) AS 'Total Purchases', Count() AS 'Number of Purchases' FROM CUSTOMER, INVOICE, LINE WHERE INVOICE_NUMBER = LINE_NUMBER AND CUSTOMER_CODE = INVOICE_CODE GROUP BY INVOICE_CODE, CUSTOMER_BALANCE;
- 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. Note that the Average Purchase Amount is equal to the Total Purchases divided by the Number of Purchases.
FIGURE P7 Average Purchase Amount by Customer
SELECT INVOICE_CODE, CUSTOMER_BALANCE,
Sum(LINE_UNITSLINE_PRICE) AS 'Total Purchases', Count() AS 'Number of Purchases', AVG(LINE_UNITS*LINE_PRICE) AS 'Average Purchase Amount' FROM CUSTOMER, INVOICE, LINE WHERE INVOICE_NUMBER = LINE_NUMBER AND CUSTOMER_CODE = INVOICE_CODE GROUP BY INVOICE_CODE, CUSTOMER_BALANCE;
- Create a query to produce the total purchase per invoice, generating the results shown in Figure P7. The Invoice Total is the sum of the product purchases in the LINE that corresponds to the INVOICE.
FIGURE P7 Invoice Totals
SELECT LINE_NUMBER,
Sum(LINE_UNITS*LINE_PRICE) AS 'Invoice Total' FROM LINE GROUP BY LINE_NUMBER;
- 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.
FIGURE P7 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' FROM (SELECT CUS_CODE, L_NUMBER AS INV_NUMBER, Sum(L_UNITS*L_PRICE) AS INV_TOT FROM INVOICE I, LINE L WHERE I_NUMBER = L_NUMBER GROUP BY CUS_CODE, L_NUMBER ) IL;
- 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.
FIGURE P7 Balances for Customers who Made Purchases
SELECT CUS_CODE, CUS_BALANCE
FROM CUSTOMER
WHERE CUSTOMER_CODE IN
(SELECT DISTINCT CUS_CODE FROM INVOICE );
or
SELECT DISTINCT CUS_CODE, CUS_BALANCE FROM CUSTOMER, INVOICE WHERE CUSTOMER_CODE = INVOICE_CODE;
- Using the results of the query created in Problem 37, provide a summary of customer balance characteristics as shown in Figure P7.
FIGURE P7 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_CODE IN (SELECT DISTINCT CUS_CODE FROM INVOICE) ) AS C; or
- 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.
FIGURE P7 Summary of Customer Balances for Customers Who Did Not
Make Purchases
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 (SELECT CUS_CODE, CUS_BALANCE FROM CUSTOMER WHERE CUSTOMER_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);
- 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.
FIGURE P7 Value of Products in Inventory
SELECT P_DESCRIPT, P_QOH, P_PRICE, P_QOH*P_PRICE AS Subtotal FROM PRODUCT;
- 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.
FIGURE P7 Total Value of All Products in Inventory
SELECT SUM(P_QOH*P_PRICE) AS 'Total Value of Inventory' FROM PRODUCT;
Ch7 - Ch7
Course: Database Systems (ITC423)
University: Charles Sturt University
- Discover more from:
