Computer Science mySQL Homework help

COMP 283 Homework 3

On blackboard, provide brief responses to the following questions. Homework due Thursday, March 14th . Late assignments accepted until 3/17 11:59 PM for a 25% Penalty.

Use the database shown in Figure P7.9 to answer Problems 1-5 This is the Data Structure of the SaleCo database.

1. Using the output shown in the figure below 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.

2. Use a query to compute the total of all purchases, the number of purchases, and the average purchase amount made by each customer.

Your output values must match those shown in the figure below.Sort the results by customer code

3. Create a query to produce the total purchase per invoice, generating the results shown in the figure below, sorted by invoice number.

The invoice total is the sum of the product purchases in the LINE that corresponds to the INVOICE.

4. 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 the customer purchase amounts. Your output must match the figure below. Hint: Start with the query produced in #3.

5. Find the listing of customers who did not make purchases during the invoicing period. The invoice period is all invoices found in the Invoice table. Sort the results by customer code. Your output must match the output shown in the figure below.

6. Find the customer balance summary for all customers who have not made purchases during the current invoicing period. The results are shown in the figure below.

7. 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 the figure below.