OCP-1Z0-051-V9.02-99题

99. View the Exhibit and examine the structure of the PROMOTIONS table.

Using the PROMOTIONS table,  you need to find out the average cost for all promos in the ranges

$0-2000 and $2000-5000 in category A

You   issue the following SQL statement:   

SQL>SELECT AVG(CASE        

WHEN promo_cost BETWEEN 0 AND 2000 AND promo_category='A'             

then promo_cost              

ELSE null END) "CAT_2000A",           

AVG(CASE               

WHEN promo_cost BETWEEN 2001 AND 5000 AND promo_category='A'                   

THEN promo_cost              

ELSE null END) "CAT_5000A"    

FROM promotions;

What would be the outcome? 

A. It executes successfully and gives the required result.

B. It generates an error because NULL cannot be specified as a return value.

C. It generates an error because CASE cannot be used with group functions. 

D. It generates an error because multiple conditions cannot be specified for the WHEN clause.

Answer: A

 

答案解析：

在原表中 promo_category='A'没有，故这里换成'TV'，一样的测试效果。

sh@TEST0910> select promo_cost from promotions where promo_category='A';

 

no rows selected

 

sh@TEST0910>  select promo_cost from promotions where promo_category='TV';

 

PROMO_COST

----------

      1100

      1500

      3100

      4800

      5200

...

115 rows selected.

 

先按条件找出区间范围的值。

sh@TEST0910> SELECT CASE WHEN promo_cost BETWEEN 0 AND 2000 AND promo_category='TV' then promo_cost ELSE null END "CAT_2000A",

  2  CASE WHEN promo_cost BETWEEN 2001 AND 5000 AND promo_category='TV' THEN promo_cost ELSE null END "CAT_5000A"

  3  FROM promotions;

 

 CAT_2000A  CAT_5000A

---------- ----------

 

      1100

 

      1500

 

                 3100

 

 

                 4800

......

503 rows selected.

 

再算出平均值。

sh@TEST0910> SELECT AVG(CASE WHEN promo_cost BETWEEN 0 AND 2000 AND promo_category='TV' then promo_cost ELSE null END) "CAT_2000A",

  2  AVG(CASE WHEN promo_cost BETWEEN 2001 AND 5000 AND promo_category='TV' THEN promo_cost ELSE null END) "CAT_5000A"

  3  FROM promotions;

 

 CAT_2000A  CAT_5000A

---------- ----------

      1300       3950