In this assignment, you will express “complex” OLAP queries in SQL. The key point of the exercise is to observe the complexity of expressing the type of such queries despite relatively simple ideas of the queries themselves. Your mission (in addition to writing the SQL queries) is to consider the reasons for the complexity of the expression of these queries.
Generate separate reports/output based on the following queries (one report for each of the
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 states in which the sale transactions took place. For the same customer, also compute the average sales quantity.
2. For each combination of customer and product, output the minimum sales quantities for
January (regardless of the year, that is, both 1/11/2000 and 1/23/2008 are considered
sales transactions for January) and maximum sales quantities for February and March
(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 January (MIN), include only the sales that occurred after 1999 (that is,
not to include sales that occurred in 1999 or earlier); for February (MAX) and March
(MAX), include all sales.