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

该子查询是在select后 from 前，这种叫标量子查询。

有匹配数据时显示一个值，没有数据时显示null

不影响主查询的行数

SQL> SELECT promo_name,  2         CASE  3           WHEN promo_cost >= (SELECT AVG(promo_cost)  4                                 FROM sh.promotions  5                                WHERE promo_category = 'TV') THEN  6            'HIGH'  7           ELSE  8            'LOW'  9         END cost_remark 10    FROM sh.promotions 11   WHERE rownum <= 5;PROMO_NAME                     COST_REMARK------------------------------ -----------NO PROMOTION #                 LOWnewspaper promotion #16-108    LOWpost promotion #20-232         LOWnewspaper promotion #16-349    LOWinternet promotion #14-471     LOW5 rows selected

SQL> SELECT COUNT(*)  2    FROM (SELECT promo_name,  3                 CASE  4                   WHEN promo_cost >=  5                        (SELECT AVG(promo_cost)  6                           FROM sh.promotions  7                          WHERE promo_category = 'TV') THEN  8                    'HIGH'  9                   ELSE 10                    'LOW' 11                 END cost_remark 12            FROM sh.promotions);  COUNT(*)----------       5031 row selected

SQL> SELECT COUNT(*) FROM sh.promotions;  COUNT(*)----------       5031 row selected

Answer: A