Which query would give the correct output?

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?

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



Leave a Reply 7

Your email address will not be published. Required fields are marked *


awiersma

awiersma

answer a is strange. the subquery contains prod_category in the group by-clause, which is not in the select clause. is that allowed?

Anand Aili

Anand Aili

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.

Justyna

Justyna

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.

KK

KK

But A, C are exactly the same. If A is correct then also the C?

Sayed

Sayed

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.

The Tuk

The Tuk

A ถูก