You work as a database administrator at ABC.com. You study the exhibit carefully.
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.
I think A is correct.
C is right after test
I think the answer is A
that’s correct but about theory the ifunequal can’t be null…. 🙁
How is that possible that C is correct?
Taking values from row 3:
– AMT_SPENT is null
– CREDIT_LIMIT is 3000
we have:
SQL> select nvl2(nullif(null,3000),0,1000)”BONUS” from dual;
select nvl2(nullif(null,3000),0,1000)”BONUS” from dual
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected – got CHAR
C means:
“It displays a bonus of 1000 for all customers whose AMT_SPENT equals CREDIT_LIMIT” which is true, but
” or AMT_SPENT is null” which is not true.
That is why A is correct.
It’s C.
Study the answers here.
That’s what i did and passed with 94%.
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.
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions102.htm
SQL> create table cust_status (
2 custno number(2) not null,
3 amt_spent number(10,2),
4 credit_limit number(10,2));
Table created.
SQL> insert into cust_status values (1, 1000, 1000);
1 row created.
SQL> insert into cust_status values (&1, &2, &3);
Enter value for 1: 2
Enter value for 2: 2000
Enter value for 3: 2500
old 1: insert into cust_status values (&1, &2, &3)
new 1: insert into cust_status values (2, 2000, 2500)
1 row created.
SQL> /
Enter value for 1: 3
Enter value for 2:
Enter value for 3: 3000
old 1: insert into cust_status values (&1, &2, &3)
new 1: insert into cust_status values (3, , 3000)
insert into cust_status values (3, , 3000)
*
ERROR at line 1:
ORA-00936: missing expression
SQL> /
Enter value for 1: 3
Enter value for 2: null
Enter value for 3: 3000
old 1: insert into cust_status values (&1, &2, &3)
new 1: insert into cust_status values (3, null, 3000)
1 row created.
SQL> /
Enter value for 1: 4
Enter value for 2: 3000
Enter value for 3: 2800
old 1: insert into cust_status values (&1, &2, &3)
new 1: insert into cust_status values (4, 3000, 2800)
1 row created.
SQL> commit;
Commit complete.
SQL> select * from cust_status;
CUSTNO AMT_SPENT CREDIT_LIMIT
———- ———- ————
1 1000 1000
2 2000 2500
3 3000
4 3000 2800
SQL> select custno, nvl2(nullif(amt_spent,credit_limit), 0, 1000) “BONUS” from cust_status;
CUSTNO BONUS
———- ———-
1 1000
2 0
3 1000
4 0