OCP CBT Question 549

Please solve the following question.

You need to synchronize the `employees` table with data from `new_hires`. The `new_hires` table contains new employees and updated details for existing employees, including a `status` column which can be 'ACTIVE' or 'TERM'. If an existing employee in `employees` has a matching `employee_id` in `new_hires`: * If `new_hires.status` is 'TERM', the employee should be deleted from `employees`. * Otherwise, the employee's `salary` and `hire_date` in `employees` should be updated with values from `new_hires`. * If an `employee_id` in `new_hires` does not exist in `employees`, it should be inserted. Examine the following MERGE statement: ```sql MERGE INTO employees e USING new_hires nh ON (e.employee_id = nh.employee_id) WHEN MATCHED THEN UPDATE SET e.salary = nh.salary, e.hire_date = nh.hire_date DELETE WHERE (nh.status = 'TERM') WHEN NOT MATCHED THEN INSERT (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id) VALUES (nh.employee_id, nh.first_name, nh.last_name, nh.email, nh.phone_number, nh.hire_date, nh.job_id, nh.salary, nh.department_id); ``` Which statement correctly describes the behavior of this MERGE statement given the requirements?

The statement correctly implements all requirements, ensuring employees with 'TERM' status in new_hires are deleted from employees, and others are updated or inserted.

The DELETE clause within WHEN MATCHED will attempt to delete rows from new_hires, not employees.

An employee whose status is 'TERM' in new_hires will first be updated and then deleted from employees if the UPDATE and DELETE clauses are both executed for the same row.

The DELETE WHERE condition within WHEN MATCHED evaluates against the employees table, causing unintended deletions if employees.status (instead of new_hires.status) were used.