Which query gives the correct output?

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?

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;



Leave a Reply 4

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


Eamon

Eamon

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.

kiya

kiya

Eamon i appreciate your explanation keep it up …
tnx alot

Afonso - Mozambique

Afonso - Mozambique

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

Mo

Mo

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;