Question

SQL

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:

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

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.

Answer 1

Answer :

2 ) SELECT [Gear - Large] as tot_sales_large_gears,[Gear - Small] as tot_sales_small_gears,[Crankshaft] as tot_sales_crankshafts,[Sprocket] as tot_sales_sprockets FROM   
(select P.product_name,ISNULL(ST.sales,0)as sales from Sales_Totals ST join Product P on ST.product_id=P.product_id)t
PIVOT(SUM(sales)
FOR product_name IN (
[Gear - Large] ,
[Gear - Small],
[Crankshaft],
[Sprocket])
) AS pivot_table;

Result :

tot_sales_large_gears	tot_sales_small_gears	tot_sales_crankshafts	tot_sales_sprockets
6200	5450	NULL	3050

========================================================================================

ANSWERED AS PER MY KNOWLEDGE

IF ANY DOUBTS COMMENT IT

THANKYOU