OCP 1Z0 051 148

来源：互联网发布：单片机plc哪个好学编辑：程序博客网时间：2024/04/30 11:15

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);

B D 两个>肯定是不对。
C 少了 group by 子句

SQL> SELECT prod_category, AVG(prod_list_price)  2    FROM sh.products  3  HAVING AVG(prod_list_price) < ALL (SELECT MAX(prod_list_price) / 2  4                                       FROM sh.products  5                                      GROUP BY prod_category);SELECT prod_category, AVG(prod_list_price)  FROM sh.productsHAVING AVG(prod_list_price) < ALL (SELECT MAX(prod_list_price) / 2                                     FROM sh.products                                    GROUP BY prod_category)ORA-00937: not a single-group group function

SQL> SELECT prod_category, AVG(prod_list_price)  2    FROM sh.products  3   GROUP BY prod_category  4  HAVING AVG(prod_list_price) < ALL (SELECT MAX(prod_list_price) / 2  5                                       FROM sh.products  6                                      GROUP BY prod_category);PROD_CATEGORY                                      AVG(PROD_LIST_PRICE)-------------------------------------------------- --------------------Software/Other                                         31.41307692307691 row selected

Answer: A

0 0