ocp题库解析：1Z0-051-V9.02-第148题
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的值。
2 GROUP BY prod_category;
3、平均值小于1/2最大值的所有值 ，即小于最小值
故选A答案。
B与题意不符，> ANY大于最小值
C语法错误，缺少group by
D与题意不符，没有in each category分组。
精华阅读