1. The database designer included columns for Year and Month in the Sales_Totals table, but forgot to include a column for Quarter. Write a CASE expression which can be used to return the quarter number (1, 2, 3, or 4) using other column values from the table.
Demonstrate Your Knowledge of Advanced SQL
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 |
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:
- The database designer included columns for Year and Month in the Sales_Totals table, but forgot to include a column for Quarter. Write a CASE expression which can be used to return the quarter number (1, 2, 3, or 4) using other column values from the table.
- 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_gears | tot_sales_small_gears | tot_sales_crankshafts | tot_sales_sprockets |
| 6200 | 5450 | 0 | 3050 |
- Write a query which 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.
- Write a query which 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.
- 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. The statements should be executed as a single unit of work.
- 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).
- 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.
- 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.
- 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.
