Examine the data in the PROMO_BEGIN_DATE column of the PROMOTIONS table:
PROMO_BEGIN _DATE
————————————
04-jan-00
10-jan-00
15-dec-99
18-oct-98
22-aug-99
You want to display the number of promotions started in 1999 and 2000.
Which query gives the correct output?
A.
SELECT SUM(DECODE(SUBSTR(promo_begin_date,8),’00’,1,0)) “2000”,
SUM(DECODE(SUBSTR
(promo_begin_date,8),’99’,1,0)) “1999”
FROM promotions;
B.
SELECT SUM(CASE TO_CHAR(promo_begin_date,’yyyy’) WHEN ’99’ THEN 1 ELSE 0 END)
“1999”,SUM(CASE TO_CHAR(promo_begin_date,’yyyy’) WHEN ’00’ THEN 1 ELSE 0 END)
“2000”
FROM promotions;
C.
SELECT COUNT(CASE TO_CHAR(promo_begin_date,’yyyy’) WHEN ’99’ THEN 1 ELSE 0 END)
“1999”, COUNT(CASE TO_CHAR(promo_begin_date,’yyyy’) WHEN ’00’ THEN 1 ELSE 0 END)
“2000” FROM promotions;
D.
SELECT COUNT(DECODE(SUBSTR(TO_CHAR(promo_begin_date,’yyyy’), 8), ‘1999’, 1, 0))
“1999”, COUNT(DECODE(SUBSTR(TO_CHAR(promo_begin_date,’yyyy’), 8),’2000′, 1,
0)) “2000”
FROM promotions;
A is correct
C and D won’t work because …
COUNT will not work as it always increments regardless of whether the value is what you want it to be.
SUM on the other hand will SUM the 1’s we collect from the DECODE(SUBSTR
B won’t work because …
the expression
TO_CHAR(promo_begin_date,’yyyy’) WHEN ’99’
pulls out a 4 digit year and then compares it to a 2 digit year.
Eamon i appreciate your explanation keep it up …
tnx alot
how is that possible? substring accepts char as a first argument…promo_begin_date and promo_end_date must be converted to char…so i think A is not correct
SELECT
next_day(
RPAD(
SUBSTR(
LPAD(
SUBSTR
(
ADD_MONTHS
( SYSDATE,trunc(months_between(sysdate,’30-SEP-09′))),4,10),9,’14-‘),1,7),11,’2017′),’Friday’) AS “Date”
from dual;