QUESTION NO: 13
Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:
Which MERGE statement is valid?
A.MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET
name = e.first_name ||’,’|| e.last_name WHEN NOT MATCHED THEN INSERT value S(e.employee_id, e.first_name ||’, ‘||e.last_name);
B.MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET
name = e.first_name ||’,’|| e.last_name WHEN NOT MATCHED THEN INSERT valueS(e.employee_id, e.first_name ||’, ‘||e.last_name);
C. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET
. name = e.first_name ||’,’|| e.last_name WHEN NOT MATCHED THEN INSERT value S(e.employee_id, e.first_name ||’,
‘||e.last_name);
D.MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET
. 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);
Answer: A
答案解析:
参考:此题考MERGE的语法
Examples
Merging into a Table: Example The following example uses the bonuses table in the sample schema oe with a default bonus of 100. It then inserts into thebonuses table all employees who made sales, based on the sales_rep_id column of the oe.orders table. Finally, the human resources manager decides that employees with a salary of $8000 or less should receive a bonus. Those who have not made sales get a bonus of 1% of their salary. Those who already made sales get an increase in their bonus equal to 1% of their salary. The MERGE statement implements these changes in one step:
CREATE TABLE bonuses (employee_id NUMBER, bonus NUMBER DEFAULT 100); INSERT INTO bonuses(employee_id) (SELECT e.employee_id FROM employees e, orders o WHERE e.employee_id = o.sales_rep_id GROUP BY e.employee_id); SELECT * FROM bonuses ORDER BY employee_id; EMPLOYEE_ID BONUS ———– ———- 153 100 154 100 155 100 156 100 158 100 159 100 160 100 161 100 163 100 MERGE INTO bonuses D USING (SELECT employee_id, salary, department_id FROM employees WHERE department_id = 80) S ON (D.employee_id = S.employee_id) WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01 DELETE WHERE (S.salary > 8000) WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus) VALUES (S.employee_id, S.salary*.01) WHERE (S.salary <= 8000); SELECT * FROM bonuses ORDER BY employee_id; EMPLOYEE_ID BONUS ———– ———- 153 180 154 175 155 170 159 180 160 175 161 170 164 72 165 68 166 64 167 62 171 74 172 73 173 61 179 62
Explanation: Explanation: this is the correct MERGE statement syntax
Incorrect answer:
Bit should MERGE INTO table_name
Cit should be WHEN MATCHED THEN
Dit should MERGE INTO table_name
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-29