SELECT chooses the columns or calculated values returned by a query; WHERE filters individual rows; GROUP BY combines rows into groups; HAVING filters those groups; and ORDER BY sorts the final result.
These clauses form part of SQL query syntax in A3.3 Database programming. They are normally written in this order:
SELECT column_or_function
FROM table
WHERE row_condition
GROUP BY grouping_column
HAVING group_condition
ORDER BY sorting_column;
| Clause | Function | Example |
|---|---|---|
SELECT | Specifies the fields, expressions, or aggregate results to return. | SELECT category, COUNT(*) |
WHERE | Filters records before grouping occurs. | WHERE price > 20 |
GROUP BY | Places rows with the same value into groups, usually for an aggregate function such as COUNT, SUM, AVG, MIN, or MAX. | GROUP BY category |
HAVING | Filters groups after aggregate values have been calculated. | HAVING COUNT(*) >= 3 |
ORDER BY | Sorts the final result in ascending (ASC) or descending (DESC) order. | ORDER BY COUNT(*) DESC |
For example:
SELECT category, COUNT(*) AS number_of_products
FROM Product
WHERE price > 20
GROUP BY category
HAVING COUNT(*) >= 3
ORDER BY number_of_products DESC;
First, WHERE removes products costing 20 or less. GROUP BY then groups the remaining products by category, and COUNT(*) calculates each group's size. HAVING retains only categories containing at least three qualifying products, while ORDER BY lists the largest groups first.
A common misconception is that WHERE and HAVING are interchangeable. They are not: WHERE applies to individual rows before grouping, whereas HAVING applies to grouped or aggregated results. Also, the semicolon ends the complete SQL statement; it does not separate these clauses.
For an IB exam response, state each clause's function precisely and trace the query in order. When asked to construct SQL, place the clauses in the correct syntactic order and distinguish row filtering from group filtering.
