OCP 1Z0 051 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. 

聚合函数忽略空行，所以语句中avg分别对应各自的范围求值

SQL> /*库中promo_category没有“A”值，我们改用“TV”代替*/
  2  SELECT AVG(CASE
  3               WHEN promo_cost BETWEEN 0 AND 2000 AND promo_category = 'TV' THEN
  4                promo_cost
  5               ELSE
  6                NULL
  7             END) "CAT_2000A",
  8         AVG(CASE
  9               WHEN promo_cost BETWEEN 2001 AND 5000 AND promo_category = 'TV' THEN
 10                promo_cost
 11               ELSE
 12                NULL
 13             END) "CAT_5000A"
 14    FROM sh.promotions;
 CAT_2000A  CAT_5000A
---------- ----------
      1300       3950
1 row selected

SQL> SELECT COUNT(*) AS ct1,
  2         COUNT(CASE
  3                 WHEN promo_cost BETWEEN 0 AND 5000 AND promo_category = 'TV' THEN
  4                  promo_cost
  5                 ELSE
  6                  NULL
  7               END) AS ct2,
  8         COUNT(CASE
  9                 WHEN promo_cost BETWEEN 0 AND 2000 AND promo_category = 'TV' THEN
 10                  promo_cost
 11                 ELSE
 12                  NULL
 13               END) "CAT_2000A",
 14         COUNT(CASE
 15                 WHEN promo_cost BETWEEN 2001 AND 5000 AND promo_category = 'TV' THEN
 16                  promo_cost
 17                 ELSE
 18                  NULL
 19               END) "CAT_5000A"
 20    FROM sh.promotions;
       CT1        CT2  CAT_2000A  CAT_5000A
---------- ---------- ---------- ----------
       503          4          2          2
1 row selected

SQL> SELECT AVG(promo_cost) AS av
  2    FROM sh.promotions
  3   WHERE promo_cost BETWEEN 0 AND 2000
  4     AND promo_category = 'TV';
        AV
----------
      1300
1 row selected

SQL> 
SQL> SELECT AVG(promo_cost) AS av
  2    FROM sh.promotions
  3   WHERE promo_cost BETWEEN 2001 AND 5000
  4     AND promo_category = 'TV';
        AV
----------
      3950
1 row selected

Answer: A