Use the SaleCo ERD listed below and the provided SQL scripts to construct its relational database and answer the following queries.
Note: Purchases = LINE_PRICE * LINE_UNITS
Note: Inventory value = P_QOH * P_PRICE
- (2 pts) How many invoices are there? (3 pts) List the invoice numbers and the invoice dates.
- (2 pts) How many customers are there? (3 pts) List the customer codes and customer names.
- (2 pts) List vendor numbers and vendor names. (3 pts) Show the vendor count per state.
- (2 pts) Based on price, what is the most expensive product? (3 pts) How much quantity on hand is available for the most expensive product?
5. (5 pts) Display the product description, quantity on hand, and price for all products that have a discount greater than 5%.
- (7 pts) Generate a listing of products offered by each vendor. List vendor name, product code and product name. Sort by vendor name and product code.
- (5 pts) What is the average discount (rounded to the nearest cent) given by each vendor.
- (3 pts) What is the vendor with most “products on hand” for a particular product? (7 pts) What is the vendor with most “products on hand” for all its products combined? List both the vendor name and the number of products. Is it the same vendor in both cases?
- (7 pts) Generate a listing of customer purchases, including the subtotals for each of the invoice line numbers; sort output by customer code, invoice number and the line_number.
- (7 pts) List the total amount spent by each customer who made purchases during the current invoice cycle—that is, for the customers who appear in the INVOICE table; sort by customer code.
- (7 pts) Find a listing of customers who did not make purchases during the invoicing period; sort by customer code.
- (7 pts) write a query to produce a summary of the value of products currently in inventory.
