SQL Programming
1/3
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
Region
Product
product_id (p) product_name
1256 Gear – Large
2/3
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:
3/3
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.
2. 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
3. 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.
4. 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.
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. The statements should be executed as a single unit of work.
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).
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.