This assignment will test your understanding of conditional logic, views, ranking and windowing functions, and transactions, as shown in the videos. The prompt includes 10 questions.
You will need to create your own test database and tables using the criteria below. Please submit your answers using only one file. The preferable format is a text file with a .sql extension. You can easily edit the file using a text editor such as Notepad ++, which is available online for free.
Prompt: A manufacturing company’s data warehouse contains the following tables.
Region
region_id (p)
region_name
super_region_id (f)
101
North America
102
USA
101
103
Canada
101
104
USA-Northeast
102
105
USA-Southeast
102
106
USA-West
102
107
Mexico
101
Note: (p) = “primary key” and (f) = “foreign key”. They are not part of the column names.
Product
product_id (p)
product_name
1256
Gear – Large
4437
Gear – Small
5567
Crankshaft
7684
Sprocket
Sales_Totals
product_id (p)(f)
region_id (p)(f)
year (p)
month (p)
sales
1256
104
2020
1
1000
4437
105
2020
2
1200
7684
106
2020
3
800
1256
103
2020
4
2200
4437
107
2020
5
1700
7684
104
2020
6
750
1256
104
2020
7
1100
4437
105
2020
8
1050
7684
106
2020
9
600
1256
103
2020
10
1900
4437
107
2020
11
1500
7684
104
2020
12
900
Answer the following questions using the above tables/data:
- Write a CASE expression that can be used to return the quarter number (1, 2, 3, or 4) only based on the month.
- Write a query which will pivot the Sales_Totals data so that there is a column for each of the 4 products containing the total sales across all months of 2020. It is OK to include the product_id values in your query, and the results should look as follows:
tot_sales_large_gearstot_sales_small_gearstot_sales_crankshaftstot_sales_sprockets6200545003050
3. Write a query that retrieves all columns from the Sales_Totals table, along with a column called sales_rank which assigns a ranking to each row based on the value of the Sales column in descending order.
4. Write a query that retrieves all columns from the Sales_Totals table, along with a column called product_sales_rank which assigns a ranking to each row based on the value of the Sales column in descending order, with a separate set of rankings for each product.
5. Expand on the query from question #4 by adding logic to return only those rows with a product_sales_rank of 1 or 2.
6. Write a set of SQL statements which will add a row to the Region table for Europe, and then add a row to the Sales_Total table for the Europe region and the Sprocket product (product_id = 7684) for October 2020, with a sales total of $1,500. You can assign any value to the region_id column, as long as it is unique to the Region table. The statements should be executed as a single unit of work. Please note that since the statements are executed as a single unit of work, additional code is needed.
7. Write a statement to create a view called Product_Sales_Totals which will group sales data by product and year. Columns should include product_id, year, product_sales, and gear_sales, which will contain the total sales for the “Gear – Large” and “Gear Small” products (should be generated by an expression, and it is OK to use the product_id values in the expression). To accomplish this, you need a CASE statement. The product_sales column should be a sum of sales for the particular product_id and year, regardless of what kind of product it is. The gear_sales column should be a sum of sales only in the case where the product is either “Gear – Large” or “Gear Small”. Else in the case that the product is neither “Gear – Large” or “Gear Small”, the value for gear_sales should be 0.
8. Write a query to return all sales data for 2020, along with a column showing the percentage of sales for each product. Columns should include product_id, region_id, month, sales, and pct_product_sales.
9. Write a query to return the year, month, and sales columns, along with a 4th column named prior_month_sales showing the sales from the prior month. There are only 12 rows in the sales_totals table, one for each month of 2020, so you will not need to group data or filter/partition on region_id or product_id.
10. If the tables used in this prompt are in the ‘sales’ database, write a query to retrieve the name and type of each of the columns in the Product table. Please specify the ‘sales’ database in your answer.
Previous
