Description

Objectives 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.

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):

1. 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 occurrences of the min or maxstates, display all.in which the sale transaction s took place. If there are >1
For the same customer, compute the average sales quantity.
2. For each year and month combination, find the “slowest” and “busiest” days (those days
with the least and the most total sales quantities of products sold) and the corresponding
total sales quantities (i.e., SUMs).
3. For each customer and month, find the “most favorite” product (the product that the
cuproductstomer purchased the most that the customer purchased the for the given monthleast for the given month) and the “least favor). ite” product (the
4. For each customer and product combination, show the average sales quantities for the
four quarters (of the year), Q1, Q2, Q3 and Q4 in four separate columns – Q1 being the
10the YEAR component, meaning simply compute AVG) along with the /25/2017, etc.). Additionally, compute the average for the “whole” yeartotal quantities (again ignoring
(SUM) and the counts (COUNT).
5. For each product, output the maximum sales quantities for each quarter in 4 separate
columns. Like the first report, display the corresponding dates (i.e., dates of those
corresponding maximum sales quantities). Ignore the YEAR component of the dates (i.e.,

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 (1) ‘month’, ‘day’ and
‘year’ as 3 separate columns or (2) concatenate them into MM/DD/YYYY format and display

Report #1:
CUSTOMER MIN_Q MIN_PROD MIN_DATE ST MAX_Q MAX_PROD MAX_DATE ST AVG_Q
======== ===== ======== ========== == ===== ======== ========== == =====

. . . .

CS 561 Page 1 of 2
Database Management Systems I

Report #2:
YEAR MONTH SLOWEST_DAY SLOWEST_TOTAL_Q BUSIEST_DAY BUSIEST_TOTAL_Q
==== ===== =========== =============== =========== ===============
2017 7 5 15734 1 90382
. . . .
. . . .
Report #3:
CUSTOMER MONTH MOST_FAV_PROD LEAST_FAV_PROD
======== ===== ============= ==============
Boo 9 Fish Apple
Chae 3 Dates Butter
Mia 12 Ham Jellies . . . .

Report #4:
CUSTOMER PRODUCT Q1_AVG Q2_AVG Q3_AVG Q4_AVG AVERAGE TOTAL COUNT
======== ======= ====== ====== ====== ====== ======= ===== =====
Dan Ice 149 998 383 25 488 6344 13
Wally Fish 39 72 142 35 79 632 8 Helen Butter 43 981 239 87 281 2529 9 . . . .

Report #5:
======= ====== ========== ====== ========== ====== ========== ====== ==========

Grading NOTE: A query with syntax errors will lose 50% of the points for the query.

Submission Submit one file containing all the 5 queries on Canvas – Please include your name and CWID in
the file. The file type must be “TXT”.

Please include a “README” file if any special instructions are required.
You can discuss the “ideas” with your classmates or your friends, but the final queries must be
your own work. If I determine that your queries are copies of someone else’s, both you and that
someone else will be disciplined (you will receive 0 for the entire assignment) and possibly receive
additional penalties for the course.

CS 561 Page 2 of 2
Database Management Systems I