OCP 1Z0 051 149

来源：互联网发布：mac 日历广告编辑：程序博客网时间：2024/05/01 07:38

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.

该题叙述不准确

TEST >SELECT MAX(promo_cost)  2    FROM sh.promotions  3   GROUP BY (promo_end_date - promo_begin_date);MAX(PROMO_COST)---------------              0          98500              0          99900         100000          99000          99300          95900          99600          9800010 rows selected.

TEST >SELECT promo_id, promo_cost  2    FROM sh.promotions  3   WHERE promo_cost < ALL  4   (SELECT MAX(promo_cost)  5            FROM sh.promotions  6           GROUP BY (promo_end_date - promo_begin_date));no rows selected

改为<=再看

TEST >SELECT promo_id, promo_cost  2    FROM sh.promotions  3   WHERE promo_cost <= ALL  4   (SELECT MAX(promo_cost)  5            FROM sh.promotions  6           GROUP BY (promo_end_date - promo_begin_date));  PROMO_ID PROMO_COST---------- ----------       999          0        37          0        36          03 rows selected.

到这儿应该比较清楚了，该语句不是计算 less than the highest cost in the same time interval.
而是如下所示

TEST >SELECT MIN(max_promo_cost)  2    FROM (SELECT MAX(promo_cost) AS max_promo_cost  3            FROM sh.promotions  4           GROUP BY (promo_end_date - promo_begin_date));MIN(MAX_PROMO_COST)-------------------                  01 row selected.TEST >SELECT promo_id, promo_cost  2    FROM sh.promotions  3   WHERE promo_cost < 0;no rows selectedTEST >SELECT promo_id, promo_cost  2    FROM sh.promotions  3   WHERE promo_cost <  4         (SELECT MIN(max_promo_cost)  5            FROM (SELECT MAX(promo_cost) AS max_promo_cost  6                    FROM sh.promotions  7                   GROUP BY (promo_end_date - promo_begin_date)));no rows selected

是小于各组最大值里面的最小值

Answer: D

0 0