Demonstrate your knowledge of advanced sql

 

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:

  1. Write a CASE expression that can be used to return the quarter number (1, 2, 3, or 4) only based on the month.
  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_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
 

Need your ASSIGNMENT done? Use our paper writing service to score better and meet your deadline.


Click Here to Make an Order Click Here to Hire a Writer