Question bank — SQL clauses — WHERE, GROUP BY, HAVING, ORDER BY, LIMIT

False — it runs fifth, after FROM, WHERE, GROUP BY, HAVING. Writing order is not evaluation order; SQL is declarative.

Strictly: some engines (MySQL, SQLite) tolerate HAVING without GROUP BY and produce the same rows, but standard/ANSI-strict engines may reject it. Even where it works, using HAVING for a pure row condition is semantically wrong, can block index use on the filter, and may be optimized differently — use WHERE.

True in most engines (PostgreSQL, MySQL, SQLite) — ORDER BY runs after SELECT, so aliases exist. Note it is not universally guaranteed by strict ANSI SQL, though nearly all mainstream dialects allow it.

False everywhere — WHERE runs before SELECT, so the alias does not exist yet. You must repeat the full expression instead.

False — without ORDER BY there is no defined order, so LIMIT 5 returns 5 arbitrary rows. "Top N" requires ORDER BY first.

False — it evaluates to NULL (unknown), so WHERE x = NULL matches nothing. Use IS NULL instead. See NULL handling and three-valued logic.

False in standard SQL — only grouping columns or aggregate functions are allowed; other columns are ambiguous. (MySQL historically allowed it and picked an arbitrary value, a dialect quirk to avoid.)

Dialect-dependent — most engines let HAVING without GROUP BY treat the whole result as one group (HAVING COUNT(*) > 100 is legal), but strict ANSI parsers can reject it. Don't rely on it for portable code.

True — WHERE drops rows before grouping, so fewer rows get grouped and aggregated, and it may enable an index seek. Only aggregate conditions genuinely need HAVING.

True for producing distinct combinations, but GROUP BY additionally lets you attach aggregates per group, which DISTINCT alone cannot.

True — these are ordinal positions referring to the SELECT list, not literal numbers. Handy but fragile: reordering your SELECT columns silently changes the sort. Prefer explicit column names or aliases.

COUNT(*) is a group property but WHERE runs before GROUP BY, so no groups exist yet. Move it to HAVING COUNT(*) > 5 with a GROUP BY dept.

The alias n is created in SELECT, which runs after WHERE. Also n is an aggregate, so it belongs in HAVING COUNT(*) > 5, not WHERE.

name is neither a grouping column nor an aggregate, so it is ambiguous which name from the group to show. In standard SQL this is an error — add name to GROUP BY or wrap it (e.g. MAX(name)).

= NULL is never TRUE (it yields NULL), so this returns zero rows even when NULL managers exist. Use WHERE manager_id IS NULL.

There is no ORDER BY, so LIMIT 3 returns 3 arbitrary departments, not the top 3. Add ORDER BY headcount DESC before LIMIT.

Two problems. First, selecting the non-aggregated column dept with an aggregate but no GROUP BY is a syntax/semantic error in standard SQL (you can't mix a bare column with an aggregate over one implicit whole-table group). Second, even if you meant a per-department filter, there is no grouping. Fix: GROUP BY dept HAVING AVG(salary) > 50000.

The alias annual doesn't exist at WHERE time. Repeat the expression: WHERE salary*12 > 60000.

Not a syntax error, but a subtle one: dept != 'HR' alone silently drops rows where dept IS NULL (the comparison is NULL, not TRUE), which is why the extra OR dept IS NULL is needed. Forgetting it loses NULL-dept rows.

They filter at different stages: WHERE filters rows before grouping (cannot see aggregates), HAVING filters groups after grouping (can see aggregates like COUNT(*)).

SELECT (which defines aliases) runs after WHERE but before ORDER BY. So by the time ORDER BY runs, aliases exist; at WHERE time they do not.

Each group collapses to one output row. A grouping column has one shared value; an aggregate collapses many values into one number. Any other column has many candidate values with no rule for choosing — so it is undefined.

LIMIT trims after sorting. With no ORDER BY, row order is engine-dependent (index/insertion order), so "top N" is meaningless and silently wrong. See Pagination patterns.

WHERE cuts rows before the expensive grouping/aggregation step, so fewer rows flow through. In an execution plan you can see this: a WHERE predicate often becomes an index seek or a filter below the aggregate node, whereas HAVING appears above the aggregate — it can only run after every group is built. See Indexes.

A filter keeps a row only when the predicate is exactly TRUE. NULL is "unknown", not TRUE, so any row whose condition evaluates to NULL is dropped — the same as if it were FALSE. See NULL handling and three-valued logic.

Aggregates in SELECT (like AVG(salary)) are computed per group, so the groups must already exist. Grouping first, then computing the summary columns, is the only order that makes the math defined.

It binds the sort to a position in the SELECT list, not a name. If someone later inserts or reorders SELECT columns, the sort silently changes with no error — a maintenance trap. Named columns/aliases are self-documenting.

All NULL-dept rows form a single group together (SQL treats NULLs as equal for grouping purposes, unlike the = NULL comparison rule). You get one row for the NULL group.

COUNT(*) is 0, the whole-table group's HAVING 0 > 0 is FALSE, so zero rows are returned — not a row containing 0.

COUNT(*) counts all rows in the group; COUNT(column) counts only rows where that column is non-NULL. They differ exactly by the number of NULLs. See Aggregate functions (COUNT, SUM, AVG, MIN, MAX).

It returns zero rows — a valid (if odd) way to fetch only column metadata. It is not a shorthand for "unlimited".

Nothing — sorting an empty set gives an empty set, and LIMIT of nothing is still nothing. No error is raised.

The order among ties is not guaranteed unless you add a tie-breaker column (e.g. ORDER BY headcount DESC, dept ASC). Relying on the accidental order is a silent pagination bug.

NULL — AVG ignores NULLs, and with no non-NULL values there is nothing to average. It is not 0. See Aggregate functions (COUNT, SUM, AVG, MIN, MAX).