CS561 –SQL Assignment 2 Solved

 Description:  •Generate 4 separate reports based on the following queries (one report for query #1, one 

for query #2, one for query #3 and another for query #4): 

1.     For each customer, product, month and state combination, compute (1) the customer's average sale of this product for the given month and state,

(2) the customer’s the customeraver’s average saleage sale for the given  for the givenmonth and state product and state, but for , but forall other  all other monthsproducts

(3) the average sale of the product and the month but for all other states. 

2.For customer, product and state, show the average sales before and after each quarter (e.g., for Q2, show average sales of Q1 and Q3.  For “before” Q1 and “after” Q4, display <NULL>.  The “YEAR” attribute is not considered for this query – for 
example, both Q1 of 2017 and Q1 of 2018 are considered Q1 regardless of the year. 

3.     For each product, find the median sales quantity (assume an odd number of simplicity of presentation).  (NOTE – “median” is defined as “denoting or relating to a  value or quantity lying at the midpoint of a frequency distribution of obsequantities, such that there is an equal probability of falling above or below itrved values or .”  E.g., 

  Median value of the list {13, 23, 12, 16, 15, 9,                     For example, given the following sales transactions for Bread, the median quant for Bread 

is 3. 

                                                             PRODUCT  QUANT 

                                                             =======  ===== 

                                                 Bread         1Bread         1  

                                                 Bread         1Bread         2  

                                                 Bread     Bread             23  

                                                     Bread         4Bread         5  

Bread         6  

Bread         7 

Bread         7 

4. For customer and product, find the month by which time, 75% of the sales quantities have been purchased. Again, for this query, the “YEAR” attribute is not considered. 
Another way to view this query is to pretend all 10,000 rows of sales data are from the same year. 

The following are sample report output (NOTE: the numbers shown below are not the actual aggregate values.  You can write simple SQL queries to verify the actual aggregate values)
                                                    
Report #1: 

CUSTOMER PRODUCT MONTH STATE CUST_AVG OTHER_PROD_AVG OTHER_MONTH_AVG OTHER_STATE_AVG 

======== ======= ===== ===== ======== ============== =============== =============== 

Helen    Bread       1 NY         243           1493             199             268 Emily    Milk        3 NJ        1426            926             482             478 . . . . 

Report #2: 

CUSTOMER PRODUCT STATE Q1  BEFORE_AVG  AFTER_AVG 

======== ======= ===== ==  ==========  ========= 

Bloom    Bread      NJ  1      <NULL>       2434 Sam      Milk       CT  3         254        325  . . . . 
Report #3: 

PRODUCT  MEDIAN QUANT 

=======  ============ 

Bread             422              Milk             1976               . . . . 

Report #4: 

CUSTOMER PRODUCT  75% PURCHASED BY MONTH 

======== =======  ====================== 

Emily    Bread                         2              

Bloom    Milk                          3           

Make sure that: 

1.     Character string data (e.g., customer name and product name) are left justified. 

2.     Numeric data (e.g., Maximum/minimum Sales Quantities) are right justified. 

3.     Only standard SQL statements and aggregate function syntaxes are used – if you’re unsure, please ask the Tas.