Question

Which of the following statement will correctly filter products having an average price of 50?

• A. SELECT Category FROM Products GROUP BY Category HAVING AVG(Price) $>$ 50;
• B. SELECT Category FROM Products GROUP BY WHERE AVG(Price) $>$ 50;
• C. SELECT Category FROM Products HAVING AVG(Price) $>$ 50;
• D. SELECT Category FROM Products GROUP BY AVG(Price) $>$ 50;

Hint:

Always remember: WHERE filters rows before grouping; HAVING filters groups after grouping.
You can never use an aggregate function like AVG() or SUM() in a WHERE clause!

Answer 1

The Correct Option is A

Step 1: Understanding the Concept:
In SQL, the ‘HAVING‘ clause is used to filter results after a ‘GROUP BY‘ operation, whereas the ‘WHERE‘ clause filters individual rows before grouping.

Step 2: Detailed Explanation:
1. To calculate an average per category, you must use ‘GROUP BY Category‘.
2. To filter the aggregated result (the average price), you must use the ‘HAVING‘ clause rather than ‘WHERE‘.

Step 3: Final Answer:
The correct query is: SELECT Category FROM Products GROUP BY Category HAVING AVG(Price) > 50;