oracle ocp认证真题,【2019年8月版本】Oracle OCP认证 071考试原题-58

choose two

Examine this description of the PRODUCTS table:

Name Null? Type

---------------------------------------------------------------------------

PROD_ID     NOT NULL      NUMBER(2)

QTY                                    NUMBER(5,2)

COST                                  NUMBER(8,2)

You successfully execute this command:

CREATE TABLE new_prices (prod_id NUMBER(2),price NUMBER(8,2));

which two statements execute without errors?

A) MERGE INTO new_prices n

USING (SELECT * FROM products) p

WHEN MATCHED THEN

UPDATE SET n.price = p. cost*.01

WHEN NOT MATCHED THEN

INSERT (n.prod_id, n.price) VALUES (p.prod_id, cost* .01)

WHERE (p.cost < 200) ;

B) MERGE INTO new_prices n

USING (SELECT * FROM products WHERE cost> 150) p

ON (n.prod_id=p.prod_id)

WHEN MATCHED THEN

DELETE WHERE (p.cost < 200)

WHEN NOT MATCHED THEN

INSERT (n.prod_id, n.price) VALUES (p.prod_id, p.cost*.01);

C) MERGE INTO new_prices n

USING (SELECT * FROM products WHERE cost>150) p

ON (n.prod_id= p.prod_id)

WHEN MATCHED THEN

UPDATE SET n.price= p.cost*.01

DELETE WHERE (p.cost < 200);

D) MERGE INTO new_prices n

USING products p

ON (p.prod_id = n.prod_id)

WHEN NOT MATCHED THEN

INSERT (n,prod_id, n.price) VALUES (p.prod_id, p.cost*.01)

WHERE (p.cost < 200);

Answer:CD

(解析：merge into 的语法一般是 mathed 就 update，否则就 insert，本题故意把语法复杂化，加了 delete 操作。但是最重要的一定要有 using…..on 子句。)