SQL interview practice exam

Which clause is evaluated first?

1. SELECT
2. FROM
3. WHERE
4. ORDER BY

Answer: FROM — FROM/JOIN establishes the row set before any filtering or projection.

A LEFT JOIN with a WHERE filter on the right table behaves like:

1. LEFT JOIN
2. INNER JOIN
3. CROSS JOIN
4. FULL OUTER JOIN

Answer: INNER JOIN — Unmatched rows have NULL and fail the WHERE, so they are dropped.

Values 100, 90, 90, 80 with RANK() descending produce:

1. 1,2,2,3
2. 1,2,2,4
3. 1,2,3,4
4. 1,1,2,3

Answer: 1,2,2,4 — RANK repeats for ties then skips the intervening number.

Which is NULL-safe for "rows with no match"?

1. NOT IN
2. NOT EXISTS
3. != ANY
4. LEFT JOIN with WHERE

Answer: NOT EXISTS — NOT EXISTS handles NULLs correctly; NOT IN returns nothing if a NULL appears.

COUNT(*) is 500, COUNT(email) is 480. Explanation?

1. Duplicate rows
2. 20 NULL emails
3. A bad join
4. Index missing

Answer: 20 NULL emails — COUNT(col) ignores NULLs.

You need only groups whose SUM exceeds a threshold. Use:

1. WHERE
2. HAVING
3. ON
4. FILTER

Answer: HAVING — The condition applies to an aggregate, which exists only after grouping.

Joining orders (1 row) to line_items (many) inflates SUM(total). Best fix?

1. SELECT DISTINCT
2. Pre-aggregate line_items before joining
3. Add an index
4. Use RIGHT JOIN

Answer: Pre-aggregate line_items before joining — Pre-aggregation removes the fan-out; DISTINCT cannot fix a summed measure.

WHERE YEAR(order_date) = 2024 ignores the index on order_date because:

1. YEAR is slow
2. A function on the column prevents a seek
3. The index is unused
4. Dates cannot be indexed

Answer: A function on the column prevents a seek — Rewrite as a half-open range so the index can be used.

SUM(x) OVER (ORDER BY d) returns a running total rather than the grand total because:

1. ORDER BY sorts the output
2. ORDER BY inside OVER changes the default frame
3. SUM behaves differently in windows
4. A PARTITION BY is missing

Answer: ORDER BY inside OVER changes the default frame — With ORDER BY the frame defaults to unbounded preceding through current row.

Which set operator returns rows in the first query but not the second?

1. UNION
2. INTERSECT
3. EXCEPT
4. UNION ALL

Answer: EXCEPT — EXCEPT (MINUS in Oracle) is the difference operator.

An index on (region, product) will NOT efficiently serve:

1. WHERE region = 'East'
2. WHERE product = 'Widget'
3. Both columns together
4. ORDER BY region

Answer: WHERE product = 'Widget' — Composite indexes are usable on a left-to-right prefix.

In a plan, estimated rows differ wildly from actual rows. Most likely cause?

1. Missing index
2. Stale statistics
3. Too much memory
4. Wrong join type

Answer: Stale statistics — The planner is working from out-of-date statistics; refresh them.

Which counts only shipped orders in one pass alongside a total?

1. Two separate queries
2. COUNT(CASE WHEN status = 'shipped' THEN 1 END)
3. COUNT(*) with HAVING
4. DISTINCT status

Answer: COUNT(CASE WHEN status = 'shipped' THEN 1 END) — Conditional aggregation pivots inside a single query.

A recursive CTE requires:

1. A window function
2. An anchor member, UNION ALL, and a recursive member
3. An index on the join key
4. A temporary table

Answer: An anchor member, UNION ALL, and a recursive member — That three-part structure is what makes the recursion work — plus a termination guarantee.

Which date filter is safest against timestamp edge cases?

1. BETWEEN '2024-01-01' AND '2024-01-31'
2. >= '2024-01-01' AND < '2024-02-01'
3. LIKE '2024-01%'
4. YEAR(d)=2024 AND MONTH(d)=1

Answer: >= '2024-01-01' AND < '2024-02-01' — A half-open range avoids both endpoint inclusion problems and function-on-column issues.