Description
SQL Programming Assignment 1
Objectives
In this assignment, you will learn to 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 product compute the maximum and minimum sales quantities along with the corresponding customer (who purchased the product), dates (i.e., dates of those maximum and minimum sales quantities) and the state in which the sale transaction took place.
For the same product, compute the average sales quantity.
2. For each combination of customer and product, output the maximum sales quantities for NY and minimum sales quantities for NJ and CT in 3 separate columns. Like the first report, display the corresponding dates (i.e., dates of those maximum and minimum sales quantities). Furthermore, for CT and NJ, include only the sales that occurred after 2000; for NY, include all sales.
3. For each of the 12 months (regardless of the year), find the most “popular” and least “popular” states (those states with most and least total sales quantities) and the corresponding total sales quantities (i.e., SUMs).
4. For each customer, find the “most favorite” product (which the customer purchased the most), the “least favorite” product (which the customer purchased the least), the “most favorable state” (where the most amounts of purchases were made) and the “least favorable state” (where the least amounts of purchases were made).
5. Show for each customer and product combination, the total 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).
The following are sample output reports – quantities displayed are for illustration only (not the actual values.
Report #1:
PRODUCT
MAX_Q
MAX_CUST
MAX_DATE
ST
MIN_Q
MIN_CUST
MIN_DATE
ST
AVG_Q
========
=====
========
==========
==
=====
========
==========
==
=====
Pepsi
2893
Bloom
01/01/2006
NJ
12
Emily
09/25/2001
NY
1435
Banana
159
Dan
02/15/2002
NJ
1
Sam
03/23/2004
CT
56
Apple
3087
Helen
07/01/2005
NY
2
Sam
02/02/2001
NJ
1512
….
CS 561
Database Management Systems I
Page 1 of 2
Report #2:
CUSTOMER
PRODUCT
NY_MAX
DATE
NJ_MIN
DATE
CT_MIN
DATE
========
=======
======
==========
======
==========
======
==========
Sam
Egg
1908
01/11/2000
2
07/24/2005
2
11/03/2008
Helen
Cookies
392
03/31/2002
42
09/14/2001
11
07/23/2002
Bloom
Butter
7045
09/22/2003
23
03/10/2004
8
09/11/2006
….
Report #3:
MONTH
MOST_POPULAR_ST MOST_POP_TOTAL_Q
LEAST_POPULAR_ST LEAST_POP_TOTAL_Q
=====
===============
================
================
=================
1
NJ
497214
CT
55526
2
NJ
1874794
NY
23126
3
CT
974531
NJ
19958
….
Report #4:
CUSTOMER MOST_FAV_PROD LEAST_FAV_PROD MOST_FAV_ST LEAST_FAV_ST
======== ============= ============== =========== ============
Claire
Apple
Jellies
NJ
NY
Emily
Eggs
Butter
NJ
CT
Chae
Fish
Ham
PA
NJ
….
Report #5:
CUSTOMER
PRODUCT
Q1_TOT Q2_TOT Q3_TOT Q4_TOT AVERAGE TOTAL
COUNT
========
=======
======
======
======
======
=======
=====
=====
Sam
Pepsi
1923
4241
2383
1325
759
9872
13
Emily
Milk
239
9872
142
2435
1586
12688
8
Helen
Bread
2534
981
4239
1987
1082
9741
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 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
