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;
Why is B not correct?
The format for B yyyy return 1999 rather than 99
syntax of decode:
decode(exp,search_string,result,default)
For b format is wrong we have to give 1999(yyyy) instead of 99(yy).Right Query is
SELECT SUM(CASE TO_CHAR(psdate,’yyyy’) WHEN ’1999’ THEN 1 ELSE 0 END) “1999”,SUM(CASE TO_CHAR(psdate,’yyyy’) WHEN ’2000’ THEN 1 ELSE 0 END) “2000”
FROM promotions;
A is correct
one of our guests recently proposed the following website
I know D is incorrect. but sack of knowledge I ask why it is return all rows of tables.
Thanks..! 🙂