OCP-1Z0-051 第147题 CASE..WHEN的使用

一、原题

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

Evaluate the following SQL statement:
SQL>SELECT promo_name,
           CASE
             WHEN promo_cost >= (SELECT AVG(promo_cost)
                                   FROM promotions
                                  WHERE promo_category = 'TV') then
              'HIGH'
             else
              'LOW'
           END COST_REMARK
      FROM promotions;
Which statement is true regarding the outcome of the above query?
A. It shows COST_REMARK for all the promos in the table.
B. It produces an error because the subquery gives an error.
C. It shows COST_REMARK for all the promos in the promo category 'TV'.
D. It produces an error because subqueries cannot be used with the CASE expression.

答案：A

二、题目翻译

看下面PROMOTIONS表的结构.
评估下面的语句:
关于上面查询的结果哪句话是正确的？
A.显示所有promos 的COST_REMARK。
B.报错，因为子查询会出错。
C.显示promo category为TV的所有产品的COST_REMARK。
D.报错，因为子查询不能用于CASE表达式。

三、题目解析

子查询
SELECT AVG(promo_cost)
    FROM promotions
WHERE promo_category = 'TV'
显示了TV的平均促销成本
CASE..WHEN的结果是，促销成本大于TV的平均促销成本的，显示为HIGH，否则显示为LOW。