Question

Which query correctly finds a category in the 'Commodity' table that has more than 5 products with a Price greater than 600?

• A. SELECT category FROM Commodity GROUP BY category HAVING COUNT(*) $>$ 5 AND Price $>$ 600;
• B. SELECT category FROM Commodity WHERE Price $>$ 600 GROUP BY category HAVING COUNT(*) $>$ 5;
• C. SELECT category, COUNT(*) FROM Commodity GROUP BY Category HAVING Price $>$ 600 AND COUNT(*) $>$ 5;
• D. SELECT category FROM Commodity GROUP BY HAVING COUNT(Price $>$ 600) $>$ 5;

Hint:

SQL clause order: WHERE filters rows before grouping $\rightarrow$ GROUP BY divides rows into groups $\rightarrow$ HAVING filters groups.
Never place individual non-aggregated column conditions into the HAVING clause unless they are in the GROUP BY clause.

Answer 1

The Correct Option is B

Concept:
In SQL, row filtering on individual record attributes is performed using the WHERE clause before aggregation.
Filtering on grouped results based on aggregate functions must be performed using the HAVING clause after the GROUP BY operation.

Step 1: Filtering Row-Level Conditions:
The problem statement states that products must have a Price > 600.
Because Price is an attribute of individual products, it must be evaluated using a WHERE clause before rows are placed into groups:
WHERE Price > 600

Step 2: Grouping and Group-Level Filtering:
Next, the qualifying individual products must be grouped by their respective categories:
GROUP BY category
Finally, the categories must have more than 5 such qualifying products, which is an aggregate property:
HAVING COUNT(*) > 5
Placing non-aggregate conditions like Price > 600 inside the HAVING clause (as in options A and C) is syntactically invalid because Price is not part of the GROUP BY clause or an aggregate function.
Final Answer:
Combining the clauses in the prescribed order of execution gives Option (B).