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

149. View the Exhibits and examine the structures of the COSTS and PROMOTIONS tables.

Evaluate the following SQL statement:

SQL> SELECT prod_id FROM costs  

WHERE promo_id IN  (SELECT promo_id FROM promotions                      

WHERE promo_cost < ALL                    

(SELECT MAX(promo_cost) FROM promotions                      

GROUP BY (promo_end_date-                                 

promo_begin_date)));

What would be the outcome of the above SQL statement?

A. It displays prod IDs in the promo with the lowest cost.

B. It displays prod IDs in the promos with the lowest cost in the same time interval.

C. It displays prod IDs in the promos with the highest cost in the same time interval.

D. It displays prod IDs in the promos with cost less than the highest cost in the same time interval.

Answer: D

 

答案解析：

1、首先查询出promo_end_date- promo_begin_date期间promo_cost的最大值

sh@TEST0924> SELECT MAX(promo_cost) FROM promotions

  2  GROUP BY (promo_end_date- promo_begin_date);

 

MAX(PROMO_COST)

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

              0

          98500

              0

          99900

         100000

          99000

          99300

          95900

          99600

          98000

 

10 rows selected.

 

2、<ALL，即小于1查询出来所有的的值，即小于最小值。

3、按题中查询，即查询出小于MAX(promo_cost) 的所有的值。

sh@TEST0924>  SELECT prod_id FROM costs

  2  WHERE promo_id IN  (SELECT promo_id FROM promotions

  3  WHERE promo_cost < ALL

  4  (SELECT MAX(promo_cost) FROM promotions

  5  GROUP BY (promo_end_date-

  6  promo_begin_date)));

 

 

no rows selected

ALL参考： http://blog.csdn.net/rlhua/article/details/12006433

ANY参考： http://blog.csdn.net/rlhua/article/details/12007309

此题与148题相似： http://blog.csdn.net/rlhua/article/details/12011533