Description

CS 561 – SQL Programming Assignment 1
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 separate reports/output based on the following queries (one report for each of the 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. transaction s took place. For the same
2. For each combination of customer and product, output the maximum sales quantities for
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 product was
sold) and the “least favorable” month (when the least amount of the product was sold).

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_Q MAX_PROD MAX_DATE ST AVG_Q
======== ===== ======== ========== == ===== ======== ========== == =====
Report #2:
CUSTOMER PRODUCT OCT_MAX OCT_DATE NOV_MIN NOV_DATE DEC_MIN DEC_DATE
======== ======= ======= ========== ======= ========== ======= ==========
CS 561-A & B Page 1 of 2
Database Management Systems I

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 1987 2781 25032 9 . . . .

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

Submission Submit one file containing all of the 5 queries with your name and CWID on it on Canvas. The file
type must be “TXT”.

Please include a “README” section in the same file if any special instructions are required.

You can discuss the “ideas” with your class mates 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-A & B Page 2 of 2
Database Management Systems I