MIS 582 Week 6 Course Project; SQL Queries

Course Project DeVry University College of Engineering and Information Sciences Course Number: MIS582 Course Project Deliverable: 6 Include screenshots of the code and result Problem 1: Write a query to count the number of invoices. SELECT COUNT(*) FROM INVOICE; 685800228587 Problem 2: Generate a listing of all purchases made by the customers, using the output shown in the following as your guide. Sort the results by customer code, invoice number, and product description. You will need to join INVOICE, LINE, PRODUCT SELECT CUS_CODE, INVOICE.INV_NUMBER, INV_DATE, P_DESCRIPT, LINE_UNITS, LINE_PRICE FROM INVOICE JOIN LINE ON INVOICE.INV_NUMBER = LINE.INV_NUMBER JOIN PRODUCT ON PRODUCT.P_CODE = LINE.P_CODE ORDER BY CUS_CODE, INVOICE.INV_NUMBER, P_DESCRIPT; 685800229013 Problem 3 : Create a query to produce the total purchase per invoice, generating the results shown in the following Figure, sorted by invoice number. The invoice total is the sum of the product purchases in the LINE that corresponds to the INVOICE. SELECT INV_NUMBER, SUM(LINE_UNITS * LINE_PRICE) AS "INVOICE TOTAL" FROM LINE GROUP BY INV_NUMBER ORDER BY INV_NUMBER; 457200147997 Problem 4: 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 the following Figure. Note: you will need to use the DISTINCT keyword and join the INVOICE and CUSTOMER tables SELECT DISTINCT CUSTOMER.CUS_CODE, CUS_BALANCE FROM CUSTOMER JOIN INVOICE ON CUSTOMER.CUS_CODE = INVOICE.CUS_CODE ORDER BY CUSTOMER.CUS_CODE; 685800145079 Problem 5: Create a query to find the balance characteristics for all customers (ie sum, min, max, and average). The results of this query are shown in the following Figure. 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; 685800175237 Problem 6: Using the output shown in the following Figure 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. You will need to join INVOICE, LINE, PRODUCT SELECT CUS_CODE, INVOICE.INV_NUMBER, P_DESCRIPT, LINE_UNITS AS "UNIT BOUGHT", LINE_PRICE AS "UNIT PRICE", LINE_UNITS * LINE_PRICE AS "SUBTOTAL" FROM INVOICE JOIN LINE ON INVOICE.INV_NUMBER = LINE.INV_NUMBER JOIN PRODUCT ON LINE.P_CODE = PRODUCT.P_CODE ORDER BY CUS_CODE,INVOICE.INV_NUMBER, P_DESCRIP