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

1. How many invoices are there?  List the invoice numbers and the invoice dates.
2. How many customers are there? List the customer codes and customer names.
3. List vendor numbers and vendor names.  Show the vendor count per state.
4. 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