Which SQL statements are valid?

View the Exhibit and examine the structure of the PROMOTIONS table.
Which SQL statements are valid? (Choose all that apply.)

View the Exhibit and examine the structure of the PROMOTIONS table.

Which SQL statements are valid? (Choose all that apply.)

A.
SELECT promo_id, DECODE(NVL(promo_cost,0), promo_cost,
promo_cost * 0.25, 100) “Discount”
FROM promotions;

B.
SELECT promo_id, DECODE(promo_cost, 10000,
DECODE(promo_category, ‘G1’, promo_cost *.25, NULL),
NULL) “Catcost”
FROM promotions;

C.
SELECT promo_id, DECODE(NULLIF(promo_cost, 10000),
NULL, promo_cost*.25, ‘N/A’) “Catcost”
FROM promotions;

D.
SELECT promo_id, DECODE(promo_cost, >10000, ‘High’,
<10000, ‘Low’) “Range”
FROM promotions;

Show Hint

← Previous question

Next question →

Leave a Reply 18

Sergey

what is wrong with C?

Laurentiu Oprea

The returned value ‘N/A’ is not a number type. The returned values must be the same type.

med

why B is correct ‘G1′ is not a number ??

Alvin2201

‘G1’ is not a returned value, it’s a confrontation value for promo_category

Giri

A & B are only correct.

user

What about D? why is it not correct?

Would option D be correct, if the Query is modified as per below:

SELECT promo_id, DECODE(promo_cost, promo_cost > 10000, ‘High’, promo_cost < 10000, ‘Low’) “Range”
FROM promotions;

Would it be correct now?

Eric Sacramento

You can’t use inequality operators in decode!

med

why B is correct ‘G1’ is not a number ??

sh3342

Decode requires the same data type to be compared. In B, Promo_category and G1 both are var which can be compared, the first DECODE returns number(promo_cost*25) which can be compared with promo_cost and 10000, because they are both numbers.

Sara

NOTE:

Oracle automatically converts expr and each search value to the datatype of the first search value before comparing. Oracle automatically converts the return value to the same datatype as the first result. If the first result has the datatype CHAR or if the first result is null, then Oracle converts the return value to the datatype VARCHAR2.

In a DECODE function, Oracle considers two nulls to be equivalent. If expr is null, then Oracle returns the result of the first search that is also null.

Victor

My viewing why b is correct:

— decode(COLUMN_NAME,WANTED_VALUE,RETURNED_VALUES_1,RETURNED_VALUES_2)
For decode function is ok if COLUMN_NAME and WANTED_VALUE are the same data types as well as same data types of RETURNED_VALUES.
And decode will fail if data types are different

— OK
eg :

— string vs string, number vs number
SQL> select decode(‘a’,’a’,100,200) from dual;

DECODE(‘A’,’A’,100,200)
———————–
100

SQL> select decode(‘a’,’b’,100,200) from dual;

DECODE(‘A’,’B’,100,200)
———————–
200

Also it is ok for DECODE if VALUES can be implicity converted to another data type, eg:

— string vs string, number vs string (‘200’ can be implicity converted to number)
SQL> select decode(‘a’,’b’,100,’200′) from dual;

DECODE(‘A’,’B’,100,’200′)
————————-
200

— number vs string (100 can be converted to string ‘100’), string vs number (300 can be converted to string ‘300’)
SQL> select decode(100,’b’,’150′,300) from dual;

DECODE(10
———
300

Also it is ok if one of the value are NULL “datatype”:

SQL> select decode(‘b’,NULL,100,200) from dual;

DECODE(‘B’,NULL,100,200)
————————
200

SQL> select decode(‘b’,’b’,NULL,200) from dual;

D
–

— FAIL
SQL> select decode(‘a’,’b’,100,’c’) from dual;
select decode(‘a’,’b’,100,’c’) from dual
*
ERROR at line 1:
ORA-01722: invalid number

Hence
a) is
select promo_id,decode(NUMBER,NUMBER,NUMBER,NUMBER) “Discount” from promotions;

b) is
select promo_id,decode(NUMBER,NUMBER,
DECODE(VARCHAR2,VARCHAR2,NUMBER,NULL),
NULL) “Catcost”
from promotions;

OR
select promo_id,decode(NUMBER,NUMBER,[NUMBER or NULL],NULL) “Catcost” from promotions;

c) is
select promo_id,decode([NULL or NUMBER],NULL,NUMBER,VARCHAR2) “CATCOST” from promotins;
‘N/A’ can not be implicity converted to number.