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.