Instructions
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 but it's not necessary to submit the scripts for creating the database objects. 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 ++ or Sublime (for Macs as well as Windows), which are available online for free.
Prompt: A manufacturing company’s data warehouse contains the following tables.
Region
Note: (p) = "primary key" and (f) = "foreign key". They are not part of the column names.
Product
Sales_Totals
Answer the following questions using the above tables/data:
Write a SELECT statement to return the month column, as well as an additional column for the quarter (1, 2, 3, or 4) that is based on the month. Please use a CASE expression for this and do not alter the table.
Write a query that 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:
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. Please use SQL RANK functions shown in the class video.
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. Please use SQL RANK functions shown in the class video.
Expand on the query from question #4 by adding logic to return only those rows with a product_sales_rank of 1 or 2.
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.
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.
Write a query to return all sales data for 2020, along with a column called “pct_product_sales” showing the percentage of sales for each product by region_id and month. Columns should include product_id, region_id, month, sales, and pct_product_sales. The values in pct_product_sales should add up to 100% for each product.
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. Please use a windowing function for this as shown in the class video.
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' schema in your answer.
