Description
Generate 5 separate reports based on the following queries (one report for query #1, one for query #2, one for query #3, one for query #4 and another for query #5):
-
For each customer, compute the minimum and maximum sales quantities along with the corresponding products (purchased), dates (i.e., dates of those minimum and maximum sales quantities) and the states in which the sale transactions took place. If there are >1 occurrences of the min or max, display all.
For the same customer, compute the average sales quantity.
-
For each of the 12 months (regardless of the year), find the most “productive” and least “productive” days (those days with most and least total sales quantities) and the corresponding total sales quantities (i.e., SUMs).
-
For each product, find the “most favorable” month (when most amount of the product was sold) and the “least favorable” month (when the least amount of the product was sold).
-
Show for each customer and product combination, the average sales quantities for 4 quarters, Q1, Q2, Q3 and Q4 (in four separate columns) – Q1 being the first 3 months of the year (Jan, Feb & Mar), Q2 the next 3 months (Apr, May & Jun), and so on – ignore the YEAR component of the dates (i.e., 3/11/2001 is considered the same date as 3/11/2002, etc.). 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).
-
For each combination of customer and product, output the maximum sales quantities for NJ, NY and CT in 3 separate columns. Like the first report, display the corresponding dates (i.e., dates of those corresponding maximum sales quantities). Furthermore, show the output only if maximum for NY is greater than NJ or CT.
The following is a sample output – quantities displayed are for illustration only (not the actual values). For dates (e.g., MAX_DATE, MIN_DATE), you can display ‘month’, ‘day’ and ‘year’ as 3 separate columns – i.e., you don’t need to concatenate them into MM/DD/YYYY format.
Report #1:
|
CUSTOMER |
MIN_Q |
MIN_PROD |
MIN_DATE |
ST |
MAX_Q |
MAX_PROD |
MAX_DATE |
ST |
AVG_Q |
|
======== |
===== |
======== |
========== |
== |
===== |
======== |
========== |
== |
===== |
|
Bloom |
12 |
Pepsi |
01/01/2006 |
NJ |
2893 |
Apple |
09/25/2001 |
NY |
1435 |
|
Sam |
1 |
Milk |
02/15/2002 |
NJ |
259 |
Banana |
03/23/2004 |
CT |
56 |
|
Emily |
2 |
Bread |
07/01/2005 |
NY |
3087 |
Milk |
02/02/2001 |
NJ |
1512 |
CS 561 Page 2 of 2
Database Management Systems I
Spring 2020
