101.Examine the data in the PROMO_BEGIN_DATE column of the PROMOTIONS table:

本文详细介绍了如何使用SQL查询语句来计算1999年和2000年促销活动的数量，通过案例演示了正确的查询方法，包括使用DECODE函数和CASE WHEN语句，并解释了每种方法的优缺点。

101.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
解析：
A:正确，SUBSTR(promo_begin_date,8)这个返回yy格式的年，如果是99返回1否则返回0，然后对整个记录执行sum，后面2000年那个也一样，因此正确
B:错误，TO_CHAR(promo_begin_date,'yyyy')这个返回的是yyyy这种格式，与99不匹配
C:错误，和B一样的错误
D:错误，TO_CHAR(promo_begin_date,'yyyy')返回的是个yyyy的年，然后SUBSTR对这个四位的年进行截取，截取位置为第八位,大于了总长度，返回null