Test Your Oracle UPSERT Knowledge

Progress

True / False

The term "UPSERT" in Oracle refers to a combination of INSERT and UPDATE operations.

True False

UPSERT is a database operation that combines INSERT and UPDATE, ensuring that if a row already exists, it is updated, otherwise, a new row is inserted.

True / False

In Oracle, the MERGE statement is used to perform UPSERT operations.

True False

The MERGE statement in Oracle is used to perform UPSERT operations, merging data into a table based on a condition.

True / False

The INSERT ... ON DUPLICATE KEY UPDATE syntax is used in Oracle for UPSERT operations.

True False

This syntax is used in MySQL, not Oracle. Oracle uses the MERGE statement for UPSERT operations.

True / False

The MERGE statement in Oracle requires a USING clause to specify the source data for the UPSERT operation.

True False

The MERGE statement requires a USING clause to specify the source table or subquery that provides the data for the operation.

True / False

In Oracle, the WHEN MATCHED THEN UPDATE clause of the MERGE statement specifies what to do when a match is found.

True False

The WHEN MATCHED THEN UPDATE clause specifies the update operation to perform if a match is found in the target table.

True / False

In Oracle, the MERGE statement can only update existing rows but cannot insert new rows.

True False

The MERGE statement can both update existing rows and insert new rows, depending on whether a match is found.

True / False

In Oracle, you can use the WHEN NOT MATCHED THEN INSERT clause of the MERGE statement to specify the insertion of new rows.

True False

The WHEN NOT MATCHED THEN INSERT clause specifies the insert operation to perform if no match is found in the target table.

True / False

In Oracle, the MERGE statement can be used with complex conditions involving multiple columns.

True False

The MERGE statement can include complex conditions in the ON clause, allowing for detailed matching criteria involving multiple columns.

True / False

The MERGE statement in Oracle can be optimized using indexes on the target table.

True False

Indexes on the target table can help optimize the performance of the MERGE statement by speeding up the matching process.

Q10

True / False

The Oracle Certified Professional (OCP) exam includes knowledge on using the MERGE statement for UPSERT operations and optimizing its performance.

True False

The OCP certification covers advanced topics, including how to use the MERGE statement for UPSERT operations and how to optimize its performance, reflecting the candidate's comprehensive knowledge of Oracle database management.

Q11

Single Choice

What is the primary use of the MERGE statement in Oracle?

To only insert rows To only update rows To perform both insert and update operations in a single statementTo delete rows

The MERGE statement in Oracle is primarily used for UPSERT operations, where it can insert new rows or update existing ones based on a condition.

Q12

Single Choice

In Oracle, what keyword is used to specify the action for matching rows in a MERGE statement?

ON WHEN MATCHED IF EXISTSCHECK

The WHEN MATCHED keyword in an Oracle MERGE statement is used to define the action to be taken when a match is found.

Q13

Single Choice

Which of the following is required in an Oracle MERGE statement?

A SELECT statement A JOIN condition A USING clauseA GROUP BY clause

The USING clause in Oracle's MERGE statement is necessary to specify the source data for the UPSERT operation.

Q14

Single Choice

Consider the following Oracle MERGE statement:What will happen if there is no matching row in target_table for an id present in source_table?

SQL Code

MERGE INTO target_table t
USING source_table s
ON (t.id = s.id)
WHEN MATCHED THEN
 UPDATE SET t.value = s.value
WHEN NOT MATCHED THEN
 INSERT (id, value) VALUES (s.id, s.value);

The row will be updated in target_table The row will be deleted from source_table A new row will be inserted into target_tableNo action will be taken

If no matching row is found in the target_table, the MERGE statement will insert a new row with the data from source_table.

Q15

Single Choice

What is the purpose of the ON clause in an Oracle MERGE statement?

To define the source table To specify the target columns To set the matching condition between the source and target tablesTo group rows by a condition

The ON clause in Oracle's MERGE statement defines the condition that determines how rows from the source and target tables should match.

Q16

Single Choice

Consider the following Oracle MERGE statement:If updates contains duplicate employee_id values, what will Oracle do?

SQL Code

MERGE INTO employees e
USING updates u
ON (e.employee_id = u.employee_id)
WHEN MATCHED THEN
 UPDATE SET e.salary = u.salary
WHEN NOT MATCHED THEN
 INSERT (employee_id, salary) VALUES (u.employee_id, u.salary);

Insert all duplicate rows Update the first matching row and ignore the rest Raise an errorMerge only unique rows

Oracle's MERGE statement will raise an error if the source table (updates in this case) contains duplicate rows that match the ON clause condition.

Q17

Single Choice

What will be the result of the following Oracle MERGE statement if a NULL value is encountered in the source_table?

SQL Code

MERGE INTO target_table t
USING source_table s
ON (t.id = s.id)
WHEN MATCHED THEN
 UPDATE SET t.value = s.value
WHEN NOT MATCHED THEN
 INSERT (id, value) VALUES (s.id, s.value);

Oracle will skip the row Oracle will update the row with NULL Oracle will insert a row with NULLIt depends on whether the column allows NULL values

The outcome depends on whether the value column in target_table allows NULL. If it does, the row will be updated or inserted with NULL.

Q18

Single Choice

Consider the following MERGE statement:What does the WHERE clause do in this Oracle MERGE statement?

SQL Code

MERGE INTO orders o
USING order_updates u
ON (o.order_id = u.order_id)
WHEN MATCHED THEN
 UPDATE SET o.status = u.status
WHEN NOT MATCHED THEN
 INSERT (order_id, status) VALUES (u.order_id, u.status)
 WHERE u.status IS NOT NULL;

It filters the source data before the merge It prevents inserting rows with NULL values in the status column It filters the target tableIt is used to join tables

The WHERE clause in this MERGE statement ensures that only rows where status is not NULL are inserted into the orders table.

Q19

Single Choice

In Oracle, what will happen if the USING clause in a MERGE statement returns no rows?

An error will be raised All rows in the target table will be deleted No changes will be made to the target tableAll rows in the target table will be updated with default values

If the USING clause in a MERGE statement returns no rows, the MERGE operation will not perform any updates or inserts, leaving the target table unchanged.

Q20

Single Choice

Consider the following MERGE statement used in an Oracle certification exam:Which of the following actions will be performed if pu.new_stock is negative?

SQL Code

MERGE INTO product_inventory pi
USING product_updates pu
ON (pi.product_id = pu.product_id)
WHEN MATCHED THEN
 UPDATE SET pi.stock = pi.stock + pu.new_stock
WHEN NOT MATCHED THEN
 INSERT (product_id, stock) VALUES (pu.product_id, pu.new_stock);

The stock value in product_inventory will decrease The row will be skipped The merge operation will failA new row with a negative stock value will be inserted

If pu.new_stock is negative, the MERGE statement will decrease the stock value in product_inventory by the specified amount during the update operation.

Q21

Multiple Choice

Identify the SQL scripts that correctly perform a basic UPSERT operation using the MERGE statement in Oracle.

SQL Code

You need to insert a new record into the 'employees' table if it does not exist, or update the existing record if it does. Implement this using the MERGE statement.

Which of the following SQL scripts correctly implement this scenario?

MERGE INTO employees e USING (SELECT 101 AS employee_id, 'John Doe' AS employee_name FROM dual) src ON (e.employee_id = src.employee_id) WHEN MATCHED THEN UPDATE SET e.employee_name = src.employee_name WHEN NOT MATCHED THEN INSERT (employee_id, employee_name) VALUES (src.employee_id, src.employee_name); MERGE INTO employees e USING (SELECT 101 AS employee_id FROM dual) src ON (e.employee_id = src.employee_id) WHEN MATCHED THEN UPDATE SET e.employee_name = 'John Doe' WHEN NOT MATCHED THEN INSERT (employee_id, employee_name) VALUES (src.employee_id, 'John Doe'); INSERT INTO employees (employee_id, employee_name) VALUES (101, 'John Doe') ON DUPLICATE KEY UPDATE employee_name = 'John Doe';MERGE INTO employees USING dual ON (employee_id = 101) WHEN MATCHED THEN UPDATE SET employee_name = 'John Doe' WHEN NOT MATCHED THEN INSERT (employee_id, employee_name) VALUES (101, 'John Doe');

The MERGE statement in Oracle is used to perform an UPSERT operation, where it updates existing records or inserts new ones if they don't exist. The correct scripts will demonstrate the basic usage of MERGE.

Q22

Multiple Choice

Select the SQL scripts that correctly handle an UPSERT operation with multiple conditions in Oracle.

SQL Code

You need to perform an UPSERT operation on the 'orders' table, where you update the order details if the order_id and customer_id match, or insert a new record if not. Implement this using the MERGE statement.

Which of the following SQL scripts correctly implement this scenario?

MERGE INTO orders o USING (SELECT 1001 AS order_id, 200 AS customer_id, 'New Order' AS order_status FROM dual) src ON (o.order_id = src.order_id AND o.customer_id = src.customer_id) WHEN MATCHED THEN UPDATE SET o.order_status = src.order_status WHEN NOT MATCHED THEN INSERT (order_id, customer_id, order_status) VALUES (src.order_id, src.customer_id, src.order_status); MERGE INTO orders o USING (SELECT 1001 AS order_id, 200 AS customer_id FROM dual) src ON (o.order_id = src.order_id OR o.customer_id = src.customer_id) WHEN MATCHED THEN UPDATE SET o.order_status = 'New Order' WHEN NOT MATCHED THEN INSERT (order_id, customer_id, order_status) VALUES (src.order_id, src.customer_id, 'New Order'); INSERT INTO orders (order_id, customer_id, order_status) VALUES (1001, 200, 'New Order') ON DUPLICATE KEY UPDATE order_status = 'New Order';MERGE INTO orders o USING (SELECT 1001 AS order_id, 200 AS customer_id FROM dual) src ON (o.order_id = src.order_id AND o.customer_id = src.customer_id) WHEN MATCHED THEN UPDATE SET o.order_status = 'New Order' WHEN NOT MATCHED THEN INSERT (order_id, customer_id, order_status) VALUES (src.order_id, src.customer_id, 'New Order');

Handling multiple conditions in an UPSERT operation requires careful use of the ON clause in the MERGE statement. The correct scripts will demonstrate how to handle complex conditions during the UPSERT operation.

Q23

Multiple Choice

Determine the SQL scripts that correctly perform an UPSERT operation with calculated fields in Oracle.

SQL Code

You need to perform an UPSERT operation on the 'inventory' table, where you update the stock quantity by adding the incoming stock if the item already exists, or insert a new record with the incoming stock as the initial quantity. Implement this using the MERGE statement.

Which of the following SQL scripts correctly implement this scenario?

MERGE INTO inventory i USING (SELECT 101 AS item_id, 50 AS incoming_stock FROM dual) src ON (i.item_id = src.item_id) WHEN MATCHED THEN UPDATE SET i.stock_quantity = i.stock_quantity + src.incoming_stock WHEN NOT MATCHED THEN INSERT (item_id, stock_quantity) VALUES (src.item_id, src.incoming_stock); MERGE INTO inventory i USING (SELECT 101 AS item_id, 50 AS incoming_stock FROM dual) src ON (i.item_id = src.item_id) WHEN MATCHED THEN UPDATE SET i.stock_quantity = i.stock_quantity + 50 WHEN NOT MATCHED THEN INSERT (item_id, stock_quantity) VALUES (src.item_id, 50); MERGE INTO inventory i USING (SELECT 101 AS item_id FROM dual) src ON (i.item_id = src.item_id) WHEN MATCHED THEN UPDATE SET i.stock_quantity = i.stock_quantity + src.incoming_stock WHEN NOT MATCHED THEN INSERT (item_id, stock_quantity) VALUES (src.item_id, 50);INSERT INTO inventory (item_id, stock_quantity) VALUES (101, 50) ON DUPLICATE KEY UPDATE stock_quantity = stock_quantity + 50;

When performing an UPSERT with calculated fields, it’s important to correctly calculate the fields during the UPDATE and INSERT operations. The correct scripts will demonstrate how to handle calculations during the UPSERT.

Q24

Multiple Choice

Identify the SQL scripts that correctly handle an UPSERT operation with NULL checks in Oracle.

SQL Code

You need to perform an UPSERT operation on the 'products' table, where you update the product price if the product_id exists, or insert a new record if not, ensuring that NULL values in the source are not overwritten. Implement this using the MERGE statement.

Which of the following SQL scripts correctly implement this scenario?

MERGE INTO products p USING (SELECT 100 AS product_id, 20.99 AS product_price FROM dual) src ON (p.product_id = src.product_id) WHEN MATCHED THEN UPDATE SET p.product_price = NVL(src.product_price, p.product_price) WHEN NOT MATCHED THEN INSERT (product_id, product_price) VALUES (src.product_id, src.product_price); MERGE INTO products p USING (SELECT 100 AS product_id, 20.99 AS product_price FROM dual) src ON (p.product_id = src.product_id) WHEN MATCHED THEN UPDATE SET p.product_price = src.product_price WHEN NOT MATCHED THEN INSERT (product_id, product_price) VALUES (src.product_id, NVL(src.product_price, p.product_price)); MERGE INTO products p USING (SELECT 100 AS product_id, 20.99 AS product_price FROM dual) src ON (p.product_id = src.product_id) WHEN MATCHED THEN UPDATE SET p.product_price = COALESCE(src.product_price, p.product_price) WHEN NOT MATCHED THEN INSERT (product_id, product_price) VALUES (src.product_id, src.product_price);MERGE INTO products p USING (SELECT 100 AS product_id, 20.99 AS product_price FROM dual) src ON (p.product_id = src.product_id) WHEN MATCHED THEN UPDATE SET p.product_price = src.product_price WHEN NOT MATCHED THEN INSERT (product_id, product_price) VALUES (src.product_id, p.product_price);

Handling NULL values during an UPSERT requires careful checks to ensure that NULLs in the source data do not overwrite existing non-NULL values in the target. The correct scripts will demonstrate how to implement this.

Q25

Multiple Choice

Select the SQL scripts that correctly perform an UPSERT operation with a sequence in Oracle.

SQL Code

You need to perform an UPSERT operation on the 'transactions' table, where a new transaction record is inserted if it does not exist, and a sequence-generated ID is assigned. If the transaction exists, the transaction date is updated. Implement this using the MERGE statement.

Which of the following SQL scripts correctly implement this scenario?

MERGE INTO transactions t USING (SELECT 1001 AS transaction_code, SYSDATE AS transaction_date FROM dual) src ON (t.transaction_code = src.transaction_code) WHEN MATCHED THEN UPDATE SET t.transaction_date = src.transaction_date WHEN NOT MATCHED THEN INSERT (transaction_id, transaction_code, transaction_date) VALUES (transaction_seq.NEXTVAL, src.transaction_code, src.transaction_date); MERGE INTO transactions t USING (SELECT 1001 AS transaction_code FROM dual) src ON (t.transaction_code = src.transaction_code) WHEN MATCHED THEN UPDATE SET t.transaction_date = SYSDATE WHEN NOT MATCHED THEN INSERT (transaction_id, transaction_code, transaction_date) VALUES (transaction_seq.CURRVAL, src.transaction_code, SYSDATE); MERGE INTO transactions t USING (SELECT 1001 AS transaction_code, SYSDATE AS transaction_date FROM dual) src ON (t.transaction_code = src.transaction_code) WHEN MATCHED THEN UPDATE SET t.transaction_date = src.transaction_date WHEN NOT MATCHED THEN INSERT (transaction_id, transaction_code, transaction_date) VALUES (transaction_seq.CURRVAL, src.transaction_code, src.transaction_date);MERGE INTO transactions t USING (SELECT 1001 AS transaction_code FROM dual) src ON (t.transaction_code = src.transaction_code) WHEN MATCHED THEN UPDATE SET t.transaction_date = SYSDATE WHEN NOT MATCHED THEN INSERT (transaction_id, transaction_code, transaction_date) VALUES (transaction_seq.NEXTVAL, src.transaction_code, SYSDATE);

Using a sequence during an UPSERT operation allows for assigning unique IDs to new records. The correct scripts will demonstrate how to use a sequence to populate the ID column in the target table during an UPSERT.

Q26

Multiple Choice

Identify the SQL scripts that correctly handle an UPSERT operation with error handling in Oracle.

SQL Code

You need to perform an UPSERT operation on the 'customers' table, where a new customer is inserted if they do not exist, or their information is updated if they do, while handling any potential errors. Implement this using the MERGE statement with error handling.

Which of the following SQL scripts correctly implement this scenario?

BEGIN MERGE INTO customers c USING (SELECT 1001 AS customer_id, 'John Doe' AS customer_name FROM dual) src ON (c.customer_id = src.customer_id) WHEN MATCHED THEN UPDATE SET c.customer_name = src.customer_name WHEN NOT MATCHED THEN INSERT (customer_id, customer_name) VALUES (src.customer_id, src.customer_name); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('An error occurred during the UPSERT operation.'); END; MERGE INTO customers c USING (SELECT 1001 AS customer_id, 'John Doe' AS customer_name FROM dual) src ON (c.customer_id = src.customer_id) WHEN MATCHED THEN UPDATE SET c.customer_name = src.customer_name WHEN NOT MATCHED THEN INSERT (customer_id, customer_name) VALUES (src.customer_id, src.customer_name); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('An error occurred during the UPSERT operation.'); BEGIN MERGE INTO customers c USING (SELECT 1001 AS customer_id, 'John Doe' AS customer_name FROM dual) src ON (c.customer_id = src.customer_id) WHEN MATCHED THEN UPDATE SET c.customer_name = src.customer_name WHEN NOT MATCHED THEN INSERT (customer_id, customer_name) VALUES (src.customer_id, src.customer_name); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN DBMS_OUTPUT.PUT_LINE('Duplicate value error occurred during the UPSERT operation.'); END;MERGE INTO customers c USING (SELECT 1001 AS customer_id, 'John Doe' AS customer_name FROM dual) src ON (c.customer_id = src.customer_id) WHEN MATCHED THEN UPDATE SET c.customer_name = src.customer_name WHEN NOT MATCHED THEN INSERT (customer_id, customer_name) VALUES (src.customer_id, src.customer_name); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('An error occurred during the UPSERT operation.');

Error handling during an UPSERT operation can be managed using PL/SQL exception handling. The correct scripts will demonstrate how to catch and handle errors during an UPSERT operation.

Q27

Multiple Choice

Select the SQL scripts that correctly perform an UPSERT operation with conditional logic in Oracle.

SQL Code

You need to perform an UPSERT operation on the 'employees' table, where the 'salary' is updated if the employee exists and their current salary is below a threshold, or a new record is inserted otherwise. Implement this using the MERGE statement with conditional logic.

Which of the following SQL scripts correctly implement this scenario?

MERGE INTO employees e USING (SELECT 101 AS employee_id, 60000 AS salary FROM dual) src ON (e.employee_id = src.employee_id) WHEN MATCHED THEN UPDATE SET e.salary = src.salary WHERE e.salary < 50000 WHEN NOT MATCHED THEN INSERT (employee_id, salary) VALUES (src.employee_id, src.salary); MERGE INTO employees e USING (SELECT 101 AS employee_id, 60000 AS salary FROM dual) src ON (e.employee_id = src.employee_id) WHEN MATCHED THEN UPDATE SET e.salary = 60000 WHERE e.salary < 50000 WHEN NOT MATCHED THEN INSERT (employee_id, salary) VALUES (src.employee_id, 60000); MERGE INTO employees e USING (SELECT 101 AS employee_id, 60000 AS salary FROM dual) src ON (e.employee_id = src.employee_id) WHEN MATCHED THEN UPDATE SET e.salary = src.salary WHERE e.salary > 50000 WHEN NOT MATCHED THEN INSERT (employee_id, salary) VALUES (src.employee_id, src.salary);MERGE INTO employees e USING (SELECT 101 AS employee_id, 60000 AS salary FROM dual) src ON (e.employee_id = src.employee_id) WHEN MATCHED THEN UPDATE SET e.salary = src.salary WHEN NOT MATCHED THEN INSERT (employee_id, salary) VALUES (src.employee_id, src.salary);

Conditional logic in an UPSERT operation can be implemented using the WHEN MATCHED clause in the MERGE statement. The correct scripts will demonstrate how to apply conditions during the UPSERT operation.

Q28

Multiple Choice

Determine the SQL scripts that correctly handle an UPSERT operation with data transformations in Oracle.

SQL Code

You need to perform an UPSERT operation on the 'sales' table, where the 'total_amount' is updated or inserted after applying a discount if the sale exists, or a new record with the discounted amount is inserted if not. Implement this using the MERGE statement with data transformations.

Which of the following SQL scripts correctly implement this scenario?

MERGE INTO sales s USING (SELECT 2001 AS sale_id, 100 AS total_amount FROM dual) src ON (s.sale_id = src.sale_id) WHEN MATCHED THEN UPDATE SET s.total_amount = src.total_amount * 0.9 WHEN NOT MATCHED THEN INSERT (sale_id, total_amount) VALUES (src.sale_id, src.total_amount * 0.9); MERGE INTO sales s USING (SELECT 2001 AS sale_id, 100 AS total_amount FROM dual) src ON (s.sale_id = src.sale_id) WHEN MATCHED THEN UPDATE SET s.total_amount = src.total_amount - (src.total_amount * 0.1) WHEN NOT MATCHED THEN INSERT (sale_id, total_amount) VALUES (src.sale_id, src.total_amount - (src.total_amount * 0.1)); MERGE INTO sales s USING (SELECT 2001 AS sale_id, 100 AS total_amount FROM dual) src ON (s.sale_id = src.sale_id) WHEN MATCHED THEN UPDATE SET s.total_amount = src.total_amount * 0.9 WHEN NOT MATCHED THEN INSERT (sale_id, total_amount) VALUES (src.sale_id, src.total_amount);MERGE INTO sales s USING (SELECT 2001 AS sale_id FROM dual) src ON (s.sale_id = src.sale_id) WHEN MATCHED THEN UPDATE SET s.total_amount = src.total_amount * 0.9 WHEN NOT MATCHED THEN INSERT (sale_id, total_amount) VALUES (src.sale_id, src.total_amount * 0.9);

Applying data transformations during an UPSERT operation requires calculating the fields during the UPDATE and INSERT operations. The correct scripts will demonstrate how to handle transformations during the UPSERT.

Q29

Multiple Choice

Identify the SQL scripts that correctly perform an UPSERT operation with date-based conditions in Oracle.

SQL Code

You need to perform an UPSERT operation on the 'subscriptions' table, where the subscription is updated if the end_date is in the future, or a new record is inserted if not. Implement this using the MERGE statement with date-based conditions.

Which of the following SQL scripts correctly implement this scenario?

MERGE INTO subscriptions s USING (SELECT 3001 AS subscription_id, '2024-12-31' AS end_date FROM dual) src ON (s.subscription_id = src.subscription_id) WHEN MATCHED THEN UPDATE SET s.end_date = src.end_date WHERE src.end_date > SYSDATE WHEN NOT MATCHED THEN INSERT (subscription_id, end_date) VALUES (src.subscription_id, src.end_date); MERGE INTO subscriptions s USING (SELECT 3001 AS subscription_id, '2024-12-31' AS end_date FROM dual) src ON (s.subscription_id = src.subscription_id) WHEN MATCHED THEN UPDATE SET s.end_date = src.end_date WHERE s.end_date > SYSDATE WHEN NOT MATCHED THEN INSERT (subscription_id, end_date) VALUES (src.subscription_id, src.end_date); MERGE INTO subscriptions s USING (SELECT 3001 AS subscription_id, '2024-12-31' AS end_date FROM dual) src ON (s.subscription_id = src.subscription_id) WHEN MATCHED THEN UPDATE SET s.end_date = src.end_date WHERE src.end_date <= SYSDATE WHEN NOT MATCHED THEN INSERT (subscription_id, end_date) VALUES (src.subscription_id, src.end_date);MERGE INTO subscriptions s USING (SELECT 3001 AS subscription_id, '2024-12-31' AS end_date FROM dual) src ON (s.subscription_id = src.subscription_id) WHEN MATCHED THEN UPDATE SET s.end_date = src.end_date WHEN NOT MATCHED THEN INSERT (subscription_id, end_date) VALUES (src.subscription_id, src.end_date);

Date-based conditions during an UPSERT operation can be managed using the WHEN MATCHED clause in the MERGE statement. The correct scripts will demonstrate how to apply date-based conditions during the UPSERT.

Q30

Multiple Choice

Select the SQL scripts that correctly perform an UPSERT operation with a WHERE clause in Oracle.

SQL Code

You need to perform an UPSERT operation on the 'users' table, where a new record is inserted if it does not exist, or the user's status is updated to 'Active' only if the current status is 'Inactive'. Implement this using the MERGE statement with a WHERE clause.

Which of the following SQL scripts correctly implement this scenario?

MERGE INTO users u USING (SELECT 4001 AS user_id, 'Active' AS status FROM dual) src ON (u.user_id = src.user_id) WHEN MATCHED THEN UPDATE SET u.status = src.status WHERE u.status = 'Inactive' WHEN NOT MATCHED THEN INSERT (user_id, status) VALUES (src.user_id, src.status); MERGE INTO users u USING (SELECT 4001 AS user_id, 'Active' AS status FROM dual) src ON (u.user_id = src.user_id) WHEN MATCHED THEN UPDATE SET u.status = 'Active' WHERE u.status = 'Inactive' WHEN NOT MATCHED THEN INSERT (user_id, status) VALUES (src.user_id, 'Active'); MERGE INTO users u USING (SELECT 4001 AS user_id FROM dual) src ON (u.user_id = src.user_id) WHEN MATCHED THEN UPDATE SET u.status = 'Active' WHERE u.status = 'Inactive' WHEN NOT MATCHED THEN INSERT (user_id, status) VALUES (src.user_id, 'Active');MERGE INTO users u USING (SELECT 4001 AS user_id, 'Active' AS status FROM dual) src ON (u.user_id = src.user_id) WHEN MATCHED THEN UPDATE SET u.status = src.status WHERE u.status = 'Inactive' WHEN NOT MATCHED THEN INSERT (user_id, status) VALUES (src.user_id, src.status);

Using a WHERE clause in the WHEN MATCHED clause of a MERGE statement allows for more precise updates during an UPSERT operation. The correct scripts will demonstrate how to use a WHERE clause to apply conditions during the UPSERT.

Oracle Database Quiz Questions