View the Exhibit and evaluate the structure and data in the CUST_STATUS table. 
You issue the following SQL statement: 
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.
Explanation:
The NULLIF Function
The NULLIF function tests two terms for equality. If they are equal the function returns a null, else
it returns the first of the two terms tested.
The NULLIF function takes two mandatory parameters of any data type. The syntax is
NULLIF(ifunequal, comparison_term), where the parameters ifunequal and
comparison_term are compared. If they are identical, then NULL is returned. If they differ, the
ifunequal parameter is returned.
Quoted from Oracle’s documentation:
NULLIF compares expr1 and expr2. If they are equal, then the function returns null. If they are not equal, then the function returns expr1. You cannot specify the literal NULL for expr1.
For this reason, the query will produce an error. The answer should be A.
Yes A is the correct answer.
expr1 can be an expression that evaluates to NULL, but it can not be the literal NULL.
Here, it evaluates to NULL, the literal NULL is not used.
So, hope the answer is C.
You are right. I didn’t pay attention to that.
This question seems tricky. According its structure the field amt_spent can admited NOT NULL so, in its data this “blank space” would seems empty but it is NULL. So, for this case the function NULLIF would give error and the answer would be letter A. But I filled the table with the same data and the answer is C.
I Agree the answer is C too.
C AMT_ CRED
= ==== ====
1 1000 1000
2 2000 2500
3 null 3000
4 3000 2800
Read 4 rows
SELECT CUSTNO, NVL2(NULLIF(AMT_SPENT,CREDIT_LIMIT),0, 1000) “BONUS”
FROM CUST_STATUS;
C BONU
= ====
1 1000
2 0
3 1000
4 0
Read 4 rows
You cannot specify the literal NULL for expr1 for NULLIF,so that give you error
C is correct. Tested
This question seems tricky. According its structure the fiel amt_spent can admited NOT NULL so, in its data that “blank space” would seems empty but is is NULL. So, for this case the function NULLIF would give error and the answer would be letter A. But I filled the table with the same data and the answer is C.