（97）View the Exhibit and evaluate the structure and data in the CUST_STATUS table. You issue the following SQL statement:

SELECT custno, NVL2(NULLIF(amt_spent, credit_limit), 0, 1000) "BONUS" FROM cust_status;

Which statement is true regarding the execution of the above query?

A. It produces an error because the AMT_SPENT column contains a null value.

B. It displays a bonus of 1000 for all customers whose AMT_SPENT is less than CREDIT_LIMIT.

C. It displays a bonus of 1000 for all customers whose AMT_SPENT equals CREDIT_LIMIT, or AMT_SPENT is null .

D. It produces an error because the TO_NUMBER function must be used to convert the result of the NULLIF function before it can be used by the NVL2 function.

答案：（C）

解析：只是说NULLIF的第一个参数不能指定为NULL字面量，但来自列中的NULL是可以接受的。

CREATE TABLE cust_status

(

CustNo NUMBER(2) NOT NULL,

AmtSpent NUMBER(10, 2),

CreditLimit NUMBER(10, 2)

);

INSERT INTO cust_status VALUES(1, 1000, 1000);

INSERT INTO cust_status VALUES(2, 2000, 2500);

INSERT INTO cust_status VALUES(3, NULL, 3000);

INSERT INTO cust_status VALUES(4, 3000, 2800);

SELECT custno, NVL2(NULLIF(amtspent, creditlimit), 0, 1000) "BONUS" FROM cust_status;

ITPub博客

OCP 1Z0-051-97

最新文章