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 is strange. the subquery contains prod_category in the group by-clause, which is not in the select clause. is that allowed?
In GROUP BY clause we can use any column from the table but it must contain all column name which are listed in select column-list.
The subquery returns data from the same table like in the main query, and grouped by prod_category – like in the main query.
But generally speaking – subquery does not need to return data from the same table like in the main query.
A is correct
But A, C are exactly the same. If A is correct then also the C?
Answer is A not C. The outer SELECT has aggregate function AVG needs a GROUP BY clause which is written before HAVING clause. The sub-query aggregate function MAX also needs a GROUP BY clause and it is written within parenthesis. If the column names are placed in SELECT statement (SELECT prod_catagory, ….) then GROUP BY clause must use this/these column to satisfy the aggregate function like AVG/MAX. If no column except aggregate functions are present in SELECT statement then any column(s) can be used in GROUP BY clause.
A ถูก