View the Exhibit and examine the structure of the PRODUCTS table.
You want to display the category with the maximum number of items.
You issue the following query:
SQL>SELECT COUNT(*),prod_category_id
FROM products
GROUP BY prod_category_id
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM products);
What is the outcome?
A. It executes successfully and gives the correct output.
B. It executes successfully but does not give the correct output.
C. It generates an error because the subquery does not have a GROUP BY clause.
D. It generates an error because = is not valid and should be replaced by the IN operator.
No, the answer is right. You cannot create a MAX(COUNT(*)) from the Subquery because it don’t have a group by clausse or a rows to be group with a maximum number of counts in the products.
SELECT COUNT(*), p.prod_category_id
FROM sh.products p
GROUP BY p.prod_category_id
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM sh.products p group by p.prod_category); The correct one
The subquery (SELECT MAX(COUNT(*)) FROM products) gives the following error:
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
I think, it should be:
It generates an error because = is not valid and should be replaced by the IN operator.
Mvh
Hiep