Description
Objectives:
• You will continue with expressing “complex” OLAP queries in SQL. The reports below are similar in nature with the reports from the assignment #1; however, there are two main differences between the two: (1) the new reports will require aggregation “outside” the groups (in assignment #1, all of the aggregates were computed for the rows within the groups); (2) some of the aggregates in the new reports will be computed based on other aggregates of the same reports – they are known as “dependent aggregates”.
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 MO
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. |
||
Grading: |
NOTE: A query with syntax errors will lose 50% of the points for the query. |
|
Submission: |
Submit one file on Canvas containing all of the 4 queries with your name and CWID on it. |
|
The file type must be “TXT”. |
||
Please include a “README” section in the same file if any special instructions are required. |
I encourage you to discuss the “ideas” with your CAs as soon as possible (rather than your classmates, esp, if you have any specific questions), 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.