OCP认证051考试题库：11g-v9.02-第148题
最新学讯：近期OCP认证正在报名中，因考试人员较多请尽快报名获取最近考试时间，报名费用请联系在线老师，甲骨文官方认证，报名从速！
我要咨询OCP认证051考试题库：11g-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分组。
