Multiple choice

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:

EMPLOYEES:

EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2 (25) LAST_NAME VARCHAR2 (25) HIRE_DATE DATE

NEW_EMPLOYEES:

EMPLOYEE_ID NUMBER Primary Key NAME VARCHAR2 (60)

Which MERGE statement is valid?

  1. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name ||', '||e.last_name);

  2. MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name ||', '||e.last_name);

  3. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name ||', '||e.last_name);

  4. MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT INTO new_employees VALUES (e.employee_id, e.first_name ||', '||e.! last_name);
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

Option A uses correct Oracle MERGE syntax: MERGE INTO target USING source ON (condition) WHEN MATCHED THEN UPDATE... WHEN NOT MATCHED THEN INSERT... Options B and C incorrectly use WHEN EXISTS instead of WHEN MATCHED. Option D has multiple errors: uses FROM instead of INTO and INSERT INTO instead of just INSERT. Option A correctly updates the name column when employee_id exists and inserts new rows when it doesn't.

Keep practicing — related questions