The Module $4$ assignment provides practice with SQL statements for data integration. A file in the same website item contains table definitions for the problems. Problems $1$ to $9$ involve Oracle SQL for the MERGE and multiple table INSERT statements. Problems $10$ to $12$ involve the INSERT $...$ ON CONFLICT statement for PostgreSQL. You are encouraged to use both DBMSs to complete the assignment. If you only have one DBMS installed, you should provide your best solution without execution for statements using the other DBMS$.$

$1.$ Write an Oracle SQL MERGE statement to combine the SSItem dimension table and the SSItemChanges$1$ change table. The associated document contains CREATE TABLE and INSERT statements for both tables. Each matching row of SSItemChanges$1$ contains values for both modified and non$-$modified columns.

$2.$ Write an Oracle SQL MERGE statement to combine the SSItem dimension table and the SSItemChanges$2$ change table. The associated document contains CREATE TABLE and INSERT statements for both tables. Each matching row of SSItemChanges$2$ contains new values for modified columns and null values for non$-$modified columns.

$3.$ Write an Oracle SQL INSERT ALL statement to insert rows of the ProductSale$1$ table into four tables $($ProductSales$2018,$ ProductSales$2019,$ ProductSales$2020,$ and ProductSales$2021).$ The associated document contains CREATE TABLE and INSERT statements for all tables. Note that the target tables lack the SalesYear column. If SalesYear equals $2018,$ insert a row into ProductSales$2018.$ The comparisons for the other tables only differ on the SalesYear value and suffix in the name of the target table. You should compute the SalesAmt column in each target table as the sum of the quarter sales $($Qtr$1,$ Qtr$2,$ Qtr$3,$ and Qtr$4)$ in the corresponding row of the ProductSale table.

$4.$ On Problem $3,$ is the number of the rows in the target tables the same when using INSERT FIRST versus INSERT ALL? Justify your answer by writing and executing an INSERT FIRST statement.

$5.$ Write an Oracle SQL INSERT FIRST statement to insert rows of the ProductSale$2$ table into three target tables $($Year$\_$Low$\_$Sales, Year$\_$Mid$\_$Sales, and Year$\_$High$\_$Sales$).$ The associated document contains CREATE TABLE statements for all tables. Insert a row into Year$\_$Low$\_$Sales when annual sales $($sum of Qtr$1,$ Qtr$2,$ Qtr$3,$ and Qtr$4)$ are less than $4,000.$ Insert a row into Year$\_$Mid$\_$Sales when annual sales are greater than or equal $4,000$ and less than $7,000.$ Insert remaining rows into Year$\_$High$\_$Sales. You should compute the SalesAmt column in each target table as the sum of the quarter sales $($Qtr$1,$ Qtr$2,$ Qtr$3,$ and Qtr$4)$ in the corresponding row of the ProductSale table.

$6.$ On Problem $5,$ is the number of the rows in the target tables the same when using INSERT FIRST versus INSERT ALL? Justify your answer by writing and executing an INSERT ALL statement.

$7.$ Write an Oracle MERGE statement to combine the Mobile$\_$Bill table with the Mobile$\_$Usage table matching on CustId. The associated document contains CREATE TABLE statements for both tables. The Mobile$\_$Bill table contains the most recent bill with the current amount $($CurrentAmt$)$ and past amount $($PastAmt$).$ When a match occurs, update the Mobile$\_$Bill.CurrentAmt column as minutes used $($Mobile$\_$Usage.MinutesUsed$)$ times $0\mathrm{.}05$ and the Mobile$\_$Bill.PastAmt column as the previous current amount $($Mobile$\_$Bill.CurrentAmt$)$ plus the previous past amount $($Mobile$\_$Bill.PastAmt$).$ When a match does not occur, insert a row into the Mobile$\_$Bill table with the customer identifier $($Mobile$\_$Usage.CustId$),$ minutes used $($Mobile$\_$Usage.MinutesUsed$)$ times $0\mathrm{.}05,$ and $0$ for the past amount $($Mobile$\_$Bill.PastAmt$).$

$8.$ Write an Oracle INSERT FIRST statement to insert rows from a mobile customer table $($Mobile$\_$Customer$)$ into three tables $($Mobile$\_$Gold, Mobile$\_$Silver, and Mobile$\_$Bronze$)$ based on a customer$$s current revenue amount $($Mobile$\_$Customer.CurrentAmt$).$ The associated document contains CREATE TABLE statements for all tables. If the current revenue amount is greater than or equal to $150,$ insert the mobile customer row into the Mobile$\_$Gold table. If the current revenue amount is greater than or equal to $100,$ insert the mobile customer row into the Mobile$\_$Silver table. Otherwise, insert the mobile customer row into the Mobile$\_$Bronze table.

$9.$ On Problem $8,$ is the number of the rows in the target tables the same when using INSERT FIRST versus INSERT ALL? Justify your answer by writing and executing an INSERT ALL statement.

$10.$ Write a PostgreSQL INSERT $....$ CONFLICT ON statement for the requirements in Problem $1.$

$11.$ Write a PostgreSQL INSERT $....$ CONFLICT ON statement for the requirements in Problem $2.$

$12.$ Modify the solution to Problem $11$ to work with NOT NULL constraints on all columns of SSItem except for ItemId. INSERT statements have been modified in the change table in the associated document. Text columns $($VARCHAR and CHAR$)$ have a value of $$NV$$ for no value. Numeric columns have a value of $0$ for no value.