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
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);
答案:A
解析:题目说的是平均的prod_list_price小于每个category最大的prod_list_price的一半,因此排除
〉号的BD,C这里没有group by不正确,虽然可以没有group by只有having,但是那种的select里面不能有非聚合的列
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.
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);
答案:A
解析:题目说的是平均的prod_list_price小于每个category最大的prod_list_price的一半,因此排除
〉号的BD,C这里没有group by不正确,虽然可以没有group by只有having,但是那种的select里面不能有非聚合的列