In this project, you will work with a modified version of the Star Schema Benchmark (SSBM). This benchmark is widely used in the database systems research community. It combines a realistic distributed data (maintaining correct data types and cross-column correlations) with a synthetic data generator. The table definitions (including the primary keys and foreign keys) and the data to populate these tables can be downloaded from the following link:

http://dbgroup.cdm.depaul.edu/CSC355_Fall2018/FinalProjectData.zip

There is a total of three parts to this project along with an extra credit part. Each part has multiple tasks. Everything that requires a response in your submission is in bold.

Write SQL queries for 7 out of the 10 following questions:

A. Compute the average lo_quantity for each c_city and c_nation combination.

B. Find the minimum, maximum, and average discount (in a single query) for suppliers whose address includes at least one comma (,) and who are located outside of Canada.

C. For each combination of even year (1992, 1994, ) and odd month (1, 3, ) find the number of different p_color and c_mktsegment values

D. P_container entry always consists of 2 words separated by a space. For each p_container where the 2nd word has exactly 4 characters (e.g., WRAP DRUM) find the number of unique lo_shippriority and c_mktsegment values. (NOTE: You can use LIKE or REGEXP_LIKE, as you prefer).

E. Find all lineorder entries (lo_orderdate, lo_orderpriority, lo_shippriority, lo_revenue and lo_tax) for suppliers whose city has at least one space (e.g., BRAZIL 5 but not INDONESIA5). Your result should be sorted by d_dayofweek in ascending order.

F. Find the dates (d_dayofweek, d_month, d_year) on which at least one lineorder record had higher-than-average lo_quantity. No date listing should appear more than once and your output should be sorted by d_year in decreasing order.

G. Find the c_nation (or s_nation) that has the greatest, combined amount of both suppliers and customers. (e.g., if Canada has 100 suppliers and 100 customers, the total amount would be 200 for Canada).

H. Find all parts where the p_color appears in the p_name. (e.g., the p_color of red appears in the p_name red brick).

I. Find the unique set of customer names that ordered parts with a larger than the average p_size from a supplier with a s_region that is different than c_region.

J. Using a single query, return two values: 1) the total number of parts where the p_type contains COPPER, and 2) the total number of parts where the p_type contains STEEL. Your output should contain two columns (COPPER and STEEL) and one record that is the total number of parts for each.