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:

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

Below is the same tables and its values in the Question  created in SQL;

REGION TABLE:

CREATE TABLE REGION(region_id integer PRIMARY KEY, region_name text,super_region_id integer);

Insert into REGION values(101,"North America",null);
Insert into REGION values(102,"USA",101);
Insert into REGION values(103,"Canada",101);
Insert into REGION values(104,"USA-North East",102);
Insert into REGION values(105,"USA-South East",102);
Insert into REGION values(106,"USA-West",102);
Insert into REGION values(107,"Mexico",101);

PRODUCT TABLE:

CREATE TABLE PRODUCT(product_id integer PRIMARY KEY, product_name text);

Insert into Product values(1256,"Gear-Large");
Insert into Product values(4437,"Gear-Small");
Insert into Product values(5567,"Crankshaft");
Insert into Product values(7684,"Sprocket");

SALES_TOTALS TABLE:

CREATE TABLE SALES_TOTALS(product_id integer, region_id integer,year integer,month integer ,sales integer,PRIMARY KEY(product_id,region_id,year,month) FOREIGN KEY (product_id) references PRODUCT (product_id)FOREIGN KEY (region_id) references REGION (region_id));

Insert into SALES_TOTALS values(1256,104,2020,1,1000);
Insert into SALES_TOTALS values(4437,105,2020,2,1200);
Insert into SALES_TOTALS values(7684,106,2020,3,800);
Insert into SALES_TOTALS values(1256,103,2020,4,2200);
Insert into SALES_TOTALS values(4437,107,2020,5,1700);
Insert into SALES_TOTALS values(7684,104,2020,6,750);
Insert into SALES_TOTALS values(1256,104,2020,7,1100);
Insert into SALES_TOTALS values(4437,105,2020,8,1050);
Insert into SALES_TOTALS values(7684,106,2020,9,600);
Insert into SALES_TOTALS values(1256,103,2020,10,1900);
Insert into SALES_TOTALS values(4437,107,2020,11,1500);
Insert into SALES_TOTALS values(7684,104,2020,12,900);

ANSWER

Below SQL statements inserts a new row to the REGION Table adding Europe Region and also inserts a row to the SALES_TOTALS Table adding  Sprocket product (product_id = 7684) for October 2020, with a sales total of $1,500 for the European Region.

SAVEPOINT SP1;
Insert into REGION values (108,"Europe",null);
SAVEPOINT SP2;
Insert into SALES_TOTALS values(7684,108,2020,10,1500);

Note:you can rollback to any of the savepoints if needed;

The REGION and SALES_TOTAL Tables after executing the statement;

REGION TABLE:

SALES_TOTALS TABLE: