OCP 1Z0 051 99

来源：互联网发布：网络常用端口号大全编辑：程序博客网时间：2024/04/25 08:39

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       39501 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          21 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----------      13001 row selectedSQL> 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----------      39501 row selected

Answer: A

0 0