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

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?

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.



Leave a Reply 8

Your email address will not be published. Required fields are marked *


Jason

Jason

I think A is correct.

Jason

Jason

C is right after test

Jenny

Jenny

I think the answer is A

JTS

JTS

that’s correct but about theory the ifunequal can’t be null…. 🙁

know

know

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.

Bruno

Bruno

It’s C.
Study the answers here.
That’s what i did and passed with 94%.

John

John

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