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

148. View the Exhibit and examine the structure of the PRODUCTS tables.

You want to generate a report that displays the average list price of product categories where the average

list price is less than half the maximum in each category.

Which query would give the correct output? 

A. SELECT prod_category,avg(prod_list_price)

FROM products   

GROUP BY prod_category   

HAVING avg(prod_list_price) < ALL   小于所有，小于最小值

(SELECT max(prod_list_price)/2   

FROM products   

GROUP BY prod_category);

B. SELECT prod_category,avg(prod_list_price)

FROM products   

GROUP BY prod_category   

HAVING avg(prod_list_price) > ANY   

(SELECT max(prod_list_price)/2

FROM products   

GROUP BY prod_category);

C. SELECT prod_category,avg(prod_list_price)   

FROM products   

HAVING avg(prod_list_price) < ALL   

(SELECT max(prod_list_price)/2   

FROM products   

GROUP BY prod_category);

D. SELECT prod_category,avg(prod_list_price)

FROM products   

GROUP BY prod_category   

HAVING avg(prod_list_price) > ANY   

(SELECT max(prod_list_price)/2   

FROM products);

Answer: A

 

答案解析：

1、首先查询子查询中出每个PROD_CATEGORY的prod_list_price的最大值的1/2的值。

 

 

sh@TEST0924> SELECT max(prod_list_price)/2 FROM products

  2  GROUP BY prod_category;

 

MAX(PROD_LIST_PRICE)/2

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

                99.995

               649.995

               299.995

               549.995

               499.995

 

2、找出prod_list_price的平均值

 

sh@TEST0924> SELECT prod_category,avg(prod_list_price)

  2  FROM products  

  3  GROUP BY prod_category;

 

PROD_CATEGORY                                      AVG(PROD_LIST_PRICE)

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

Software/Other                                               31.4130769

Hardware                                                        1149.99

Electronics                                                  121.682308

Photo                                                            230.19

Peripherals and Accessories                                  145.085238

 

3、平均值小于1/2最大值的所有值 ，即小于最小值

sh@TEST0924> SELECT prod_category,avg(prod_list_price) FROM products GROUP BY prod_category

  2  having avg(prod_list_price)<all(SELECT max(prod_list_price)/2 FROM products GROUP BY prod_category);

 

PROD_CATEGORY                                      AVG(PROD_LIST_PRICE)

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

Software/Other                                               31.4130769

故选A答案。

 

B与题意不符，> ANY大于最小值

sh@TEST0924> SELECT prod_category,avg(prod_list_price)

  2   FROM products

  3  GROUP BY prod_category

  4  HAVING avg(prod_list_price) > ANY

  5  (SELECT max(prod_list_price)/2

  6  FROM products

  7  GROUP BY prod_category);

 

PROD_CATEGORY                                      AVG(PROD_LIST_PRICE)

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

Hardware                                                        1149.99

Electronics                                                  121.682308

Photo                                                            230.19

Peripherals and Accessories                                  145.085238

 

C语法错误，缺少group by

sh@TEST0924> SELECT prod_category,avg(prod_list_price)

  2  FROM products

  3  HAVING avg(prod_list_price) < ALL

  4  (SELECT max(prod_list_price)/2

  5  FROM products

  6  GROUP BY prod_category);

SELECT prod_category,avg(prod_list_price)

       *

ERROR at line 1:

ORA-00937: not a single-group group function

 

D与题意不符，没有 in each category分组。

sh@TEST0924> SELECT max(prod_list_price)/2 FROM products;

 

MAX(PROD_LIST_PRICE)/2

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

               649.995

 

sh@TEST0924> SELECT prod_category,avg(prod_list_price)

  2  FROM products

  3  GROUP BY prod_category 

  4  HAVING avg(prod_list_price) > ANY

  5  (SELECT max(prod_list_price)/2 

  6  FROM products);

 

PROD_CATEGORY                                      AVG(PROD_LIST_PRICE)

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

Hardware                                                        1149.99

 

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

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