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

147. 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.

Answer: A

答案解析：

1、首先运行子查询，得出一个数值

 

sh@TEST0910> SELECT AVG(promo_cost) FROM promotions  WHERE promo_category='TV';

AVG(PROMO_COST)

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

     46260.8696

2、所有的promos 根据上面计算的数值，来进行分类。故选择A

sh@TEST0910> SELECT promo_name,CASE WHEN promo_cost >=

  2  (SELECT AVG(promo_cost) FROM promotions  WHERE promo_category='TV')

  3  then 'HIGH'  else 'LOW'END COST_REMARK

  4  FROM promotions;

PROMO_NAME                     COST

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

NO PROMOTION #                 LOW

newspaper promotion #16-108    LOW

post promotion #20-232         LOW

newspaper promotion #16-349    LOW

internet promotion #14-471     LOW