In SQL and relational databases, what does the HAVING clause do in a SELECT statement that uses GROUP BY?

Introduction / Context:
This question tests your understanding of basic SQL, which is very useful for computer science, data analysis, and many technical exams. Specifically, it asks about the role of the HAVING clause in a SELECT query that uses GROUP BY. Confusing HAVING and WHERE is a common mistake, so clear understanding is essential.

Given Data / Assumptions:
- The focus is on the HAVING clause in SQL.
- We assume the presence of a GROUP BY clause, which creates groups of rows based on one or more columns.
- Options describe different relationships between HAVING, WHERE, rows, columns, and groups.

Concept / Approach:
In SQL, the WHERE clause filters individual rows before grouping, while the HAVING clause filters groups after the GROUP BY operation. You can think of HAVING as a kind of "WHERE for groups". This makes HAVING useful when you want to keep only those groups that meet a certain condition, often involving aggregate functions like COUNT, SUM, AVG, MAX, or MIN.

Step-by-Step Solution:
Step 1: Recall the order of operations in a SELECT statement: FROM, WHERE, GROUP BY, HAVING, SELECT list, ORDER BY.
Step 2: Note that WHERE applies to each row before any grouping happens.
Step 3: Recognise that HAVING is evaluated after GROUP BY, when groups have been formed and aggregate values are available.
Step 4: Match this description with the option that says HAVING acts like a WHERE clause but is used for groups rather than rows.

Verification / Alternative check:
Consider an example: you want to list only those departments where the number of employees is greater than five. You might write "SELECT department_id, COUNT(*) FROM employees GROUP BY department_id HAVING COUNT(*) > 5". Here, HAVING filters groups of rows (each department group) based on the aggregate COUNT(*). If you tried to use WHERE COUNT(*) > 5, it would fail, because WHERE cannot use aggregate functions in that way. This confirms that HAVING is specifically designed for group level filtering.

Why Other Options Are Wrong:
- Acts like a WHERE clause but is used for columns rather than groups: incorrect, HAVING is not about columns versus rows but about groups versus individual rows.
- Acts like a WHERE clause but is used for rows rather than columns: simply describes WHERE itself, not HAVING.
- Acts exactly like a WHERE clause in every situation: false, because WHERE and HAVING are evaluated at different stages and cannot always be interchanged.
- Filters rows before any grouping is applied: this describes WHERE, not HAVING.

Common Pitfalls:
Many learners treat HAVING and WHERE as interchangeable, which leads to errors when using aggregate functions. A useful memory trick is: WHERE filters rows, HAVING filters groups. If a condition does not involve aggregate functions and you want to remove rows early, use WHERE. If a condition depends on aggregated values, use HAVING after GROUP BY.

Final Answer:
The HAVING clause acts like a WHERE clause but is used for groups rather than individual rows.