OCP 1Z0 051 143

来源：互联网发布：淘宝摄影师编辑：程序博客网时间：2024/06/10 17:24

143. View the Exhibit and examine the structure of the PROMOTIONS table.
You have to generate a report that displays the promo name and start date for all promos that started after
the last promo in the 'internet' category.
Which query would give you the required output?

A. SELECT promo_name, promo_begin_date FROM promotions
WHERE promo_begin_date > ALL (SELECT MAX(promo_begin_date)
FROM promotions )AND
promo_category = 'internet';
B. SELECT promo_name, promo_begin_date FROM promotions
WHERE promo_begin_date IN (SELECT promo_begin_date
FROM promotions
WHERE promo_category='internet');
C. SELECT promo_name, promo_begin_date FROM promotions
WHERE promo_begin_date > ALL (SELECT promo_begin_date
FROM promotions
WHERE promo_category = 'internet');
D. SELECT promo_name, promo_begin_date FROM promotions
WHERE promo_begin_date > ANY (SELECT promo_begin_date
FROM promotions
WHERE promo_category = 'internet');

要在'INTERNET'的时间之后。
A 主查询中查'INTERNET'的数据，显然不对。且加上子查询后估计会没数据

SYS >SELECT promo_name, promo_begin_date  2    FROM sh.promotions  3   WHERE promo_begin_date > ALL  4   (SELECT MAX(promo_begin_date) FROM sh.promotions)  5     AND promo_category = 'internet';no rows selected

B 用IN，显然关系不对

SQL> SELECT promo_name, promo_begin_date, promo_category  2    FROM sh.promotions  3   WHERE promo_begin_date IN  4         (SELECT promo_begin_date  5            FROM sh.promotions  6           WHERE promo_category = 'internet')  7     AND rownum <= 5;PROMO_NAME                     PROMO_BEGIN_DATE PROMO_CATEGORY------------------------------ ---------------- ------------------------------newspaper promotion #16-108    2000-12-23       newspaperpost promotion #20-232         1998-09-25       postinternet promotion #14-471     2000-02-26       internetTV promotion #13-448           2000-08-06       TVinternet promotion #25-86      1998-09-20       internet5 rows selectedExecuted in 0.063 seconds

C D promo_category = 'internet' 有多条数据。用> any不对，应该用 > all。这点从字面意思理解即可

SQL> SELECT promo_name, promo_begin_date  2    FROM sh.promotions  3   WHERE promo_begin_date > ALL (SELECT promo_begin_date  4            FROM sh.promotions  5           WHERE promo_category = 'internet');PROMO_NAME                     PROMO_BEGIN_DATE------------------------------ ----------------radio promotion #31-501        2000-12-30NO PROMOTION #                 9999-01-012 rows selected

SQL> SELECT promo_name, promo_begin_date, promo_category  2    FROM sh.promotions  3   WHERE promo_begin_date > ANY (SELECT promo_begin_date  4            FROM sh.promotions  5           WHERE promo_category = 'internet')  6     AND rownum <= 5;PROMO_NAME                     PROMO_BEGIN_DATE PROMO_CATEGORY------------------------------ ---------------- ------------------------------NO PROMOTION #                 9999-01-01       NO PROMOTIONradio promotion #31-501        2000-12-30       radioflyer promotion #23-162        2000-12-28       flyermagazine promotion #18-428     2000-12-28       magazineinternet promotion #29-361     2000-12-28       internet5 rows selected

Answer: C

0 0