See the Exhibit and examine the structure of the PROMOSTIONS table:
Exhibit:
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;
Explanation:
The DECODE Function
Although its name sounds mysterious, this function is straightforward. The DECODE function implements
ifthen-else conditional logic by testing its first two terms for equality and returns the third if they are equal and
optionally returns another term if they are not.
The DECODE function takes at least three mandatory parameters, but can take many more. The syntax of the
function is DECODE(expr1, comp1, iftrue1,
[comp2, iftrue2…[ compN, iftrueN]], [iffalse]).
You can’t use inequality operators in decode!- D is not correct
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.