Use the SaleCo ERD listed below and the provided SQL scripts to construct its relational database and
Question:
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
- How many invoices are there? List the invoice numbers and the invoice dates.
- How many customers are there? List the customer codes and customer names.
- List vendor numbers and vendor names. Show the vendor count per state.
- Based on price, what is the most expensive product? How much quantity on hand is available for the most expensive product?
5. Display the product description, quantity on hand, and price for all products that have a discount greater than 5%.
6. Generate a listing of products offered by each vendor. List vendor name, product code and product name. Sort by vendor name and product code.
7. What is the average discount (rounded to the nearest cent) given by each vendor.
8. What is the vendor with most "products on hand" for a particular product? 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?
9. 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.
10. 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.
11. Find a listing of customers who did not make purchases during the invoicing period; sort by customer code.
12. post a query to produce a summary of the value of products currently in inventory.
What the output should look like:
7. What is the average discount (rounded to the nearest cent) given by each vendor.
V_NAME avg_discount
--------------- ------------
Bryson, Inc. 0.05
D&E Supply 0.0
Gomez Bros. 0.02
ORDVA, Inc. 0.05
Randsets Ltd. 0.0
Rubicon Systems 0.05
8. What is the vendor with the 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?"
V_NAME P_CODE max_qoh
------------ -------- -------
Bryson, Inc. SM-18277 172
V_NAME total_qoh
---------- ---------
D&E Supply 237
9. 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.
CUS_CODE CUS_LNAME CUS_FNAME INV_NUMBER INV_DATE LINE_NUMBER P_CODE LINE_UNITS LINE_PRICE Subtotal
-------- --------- --------- ---------- ----------- ----------- -------- ---------- ---------- --------
10011 Dunne Leona 1002 16-JAN-2016 1 54778-2T 2 4.99 9.98
10011 Dunne Leona 1004 17-JAN-2016 1 54778-2T 3 4.99 14.97
10011 Dunne Leona 1004 17-JAN-2016 2 23109-HB 2 9.95 19.9
10011 Dunne Leona 1008 17-JAN-2016 1 PVC23DRT 5 5.87 29.35
10011 Dunne Leona 1008 17-JAN-2016 2 WR3/TT3 3 119.95 359.85
10011 Dunne Leona 1008 17-JAN-2016 3 23109-HB 1 9.95 9.95
10012 Smith Kathy 1003 16-JAN-2016 1 2238/QPD 1 38.95 38.95
10012 Smith Kathy 1003 16-JAN-2016 2 1546-QQ2 1 39.95 39.95
10012 Smith Kathy 1003 16-JAN-2016 3 13-Q2/P2 5 14.99 74.95
10014 Orlando Myron 1001 16-JAN-2016 1 13-Q2/P2 1 14.99 14.99
10014 Orlando Myron 1001 16-JAN-2016 2 23109-HB 1 9.95 9.95
10014 Orlando Myron 1006 17-JAN-2016 1 SM-18277 3 6.99 20.97
10014 Orlando Myron 1006 17-JAN-2016 2 2232/QTY 1 109.92 109.92
10014 Orlando Myron 1006 17-JAN-2016 3 23109-HB 1 9.95 9.95
10014 Orlando Myron 1006 17-JAN-2016 4 89-WRE-Q 1 256.99 256.99
10015 O'Brian Amy 1007 17-JAN-2016 1 13-Q2/P2 2 14.99 29.98
10015 O'Brian Amy 1007 17-JAN-2016 2 54778-2T 1 4.99 4.99
10018 Farriss Anne 1005 17-JAN-2016 1 PVC23DRT 12 5.87 70.44
10. 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.
CUS_CODE CUS_LNAME CUS_FNAME TOTAL_AMOUNT_SPENT
-------- --------- --------- ------------------
10011 Dunne Leona 444.0
10012 Smith Kathy 153.85
10014 Orlando Myron 422.77
10015 O'Brian Amy 34.97
10018 Farriss Anne 70.44
11. Find a listing of customers who did not make purchases during the invoicing period; sort by customer code.
CUS_CODE CUS_LNAME CUS_FNAME
-------- --------- ---------
10010 Ramas Alfred
10013 Olowski Paul
10016 Brown James
10017 Williams George
10019 Smith Olette
12. Create a query to produce a summary of the value of products currently in inventory.
Total_Value
-----------
15084.52
Modern Systems Analysis And Design
ISBN: 9780134204925
8th Edition
Authors: Joseph Valacich, Joey George