CS561 – SQL Assignment 1 Solved

Generate separate reports/output based on the following queries 

1.     For each customer, compute the minimum and maximum sales quantities along with the corresponding products, dates (i.e., dates of those maximum and minimum sales quantities) and the customer, also compute statesthe  in which the saleaverage sales quantity. transactions took place.  For the same 

2. For each combination of customer and product, output the maximum sales quantities for  October (regardless of the year, that is, both 10/11/2016 and 10/23/2019 are considered sales transactions for October) and minimum sales quantities for November and December (again, regardless of the year) in 3 separate columns.  Like the first report, display the corresponding dates (i.e., dates of those maximum and minimum sales quantities).  Furthermore, for October (MAX), include only the sales that occurred after  2017 (that is, not to include sales that occurred in 2017 or earlier); for November (MIN) and December (MIN), include all sales. 

3.For each of the 12 months (regardless of the year), find the most “popular” and least  “popular” products (those products with most and least total sales quantities) and the corresponding total sales quantities (i.e., SUMs). 

4.For each product, find the “most favorable” month (when most amount of the producsold) and the “least favorable” month (when the least amount of the product was sold). 

5.For the years 2016, 2017, 2018, 2019 and 2020, show, for each product and customer combination, the average sales quantities for the 4 states, ‘CT’, ‘NY’, ‘NJ’ and ‘PA’ (in four separate columns).  Also compute the average for the “whole” year (again ignoring the YEAR component, meaning simply compute AVG) along with the total quantities (SUM) and the counts (COUNT). 

The following are sample output reports – quantities displayed are for illustration only (not the actual values). 

Report #1:

CUSTOMER  MIN_Q  MIN_PROD  MIN_DATE    ST  MAX_ MAX_PROD  MAX_DATE    ST  AVG_Q 
     ========  =====  ========  ==========  ==  =====  ========  ==========  ==  ===== 
  Bloom12  Pepsi  01/01/2016  NJ   2893  Apple 09/25/2019  NY   1435  Sam 1  Milk  02/15/2017  NJ    259  Banana  03/23/2018  CT     56Emily  1  Bread 07/01/2018  NY   3087  Milk   02/02/2016  NJ   1512    . . . .  

Report #2:

 CUSTOMER  PRODUCT  OCT_MAX  OCT_DATE    NOV_MIN  NOV_DATE    DEC_MIN  DEC_DATE       
   ========  =======  =======  ==========  =======  ==========  =======  ========== 
Sam  Egg  8  10/11/2019 3234  11/24/2016 2432  12/03/2018 
 Helen Cookies 92  10/22/2018 4342  11/14/2020 9483  12/23/2017      Bloom  Butter 45  10/31/2020 1923  11/10/2017   2596  12/11/2016 CS 561-A & B                                                                                                    Page 1 of 2 

Database Management Systems I 
Fall 2021 

Report #3: 
   MONTH  MOST_POPULAR_PROD  MOST_POP_TOTAL_Q  LEAST_POPULAR_PROD  LEAST_POP_TOTAL_Q  
   =====  =================  ================  ==================  ================= 
1   Eggs                         497214  Pepsi                           55526  
2   Milk                        1874794  Banana                          23126         3  Pepsi                        974531  Milk                            19958    . . . . 

Report #4: 
     PRODUCT  MOST_FAV_MO  LEAST_FAV_MO  
   =======  ===========  ============ 
     Egg                4            12  
     Apple             1            11  
     Banana           3           2     . . . . 

Report #5: 
PRODUCT  CUSTOMER  CT_AVG  NY_AVG  NJ_AVG  PA_AVG  AVERAGE  TOTAL  COUNT 
=======  ========  ======  ======  ======  ======  =======  =====  ===== 
Pepsi Sam  1923  4241   2383 1325   2988  38848  13 
Milk Emily 239 9872   142  2435  2663  21307  8 Bread Helen       2534     981    4239    19872781  25032  9 . . . .