SQL Query Practice Questions (Basic to Advanced)

Why interviewers ask this

Not every SQL question is about joins or ranking. A large share of real interview time, and real day-to-day query writing, goes into things that are individually simple but collectively separate a comfortable SQL writer from someone still looking things up: pagination that doesn't degrade at scale, readable multi-step logic instead of deeply nested subqueries, and correct handling of missing data.

The first five questions get full treatment: working query, a less robust alternative, and what changes between them. The remaining five are tighter: one working example and the insight it tests.

How interviewers read a query-practice answer

Layer	What a weaker answer does	What a stronger answer does
Readability	Nests subqueries several levels deep to avoid a CTE	Uses `WITH` to name intermediate steps, making the query easier to follow and debug
NULL handling	Assumes columns are always populated	Uses `COALESCE`/`IFNULL` deliberately, and can explain what breaks without it
Scale awareness	Uses `OFFSET` for pagination without knowing its cost	Knows `OFFSET` gets slower on later pages and can name the keyset alternative
Data correctness	Writes an `UPDATE` or `INSERT` without considering conflicting rows	Thinks about what happens on a duplicate key or a conditional update before writing it

1How do you paginate results, and why does OFFSET get slower on later pages?

SQL · paginate_with_offset.sql

-- Page 3, 20 rows per page
SELECT id, name, created_at
FROM users
ORDER BY id
LIMIT 20 OFFSET 40;

This works, but OFFSET doesn't skip rows for free. To return rows 41-60, the database still scans and discards the first 40 rows every single time. On page 10,000, that's tens of thousands of discarded rows on every request.

The keyset (or "seek") alternative avoids this by remembering the last row seen:

SQL · paginate_with_keyset.sql

-- Assuming the last row on the previous page had id = 40
SELECT id, name, created_at
FROM users
WHERE id > 40
ORDER BY id
LIMIT 20;

Approach	Cost on page 3	Cost on page 10,000
`LIMIT ... OFFSET`	Small	Scans and discards everything before it
Keyset (`WHERE id > last_seen`)	Small	Small, stays constant

Evaluates: whether the candidate knows OFFSET pagination has a hidden scaling cost, and can name keyset pagination as the fix. This is a common follow-up in interviews for any role touching user-facing lists or feeds.

2How do you bucket rows into categories using conditional logic?

SQL · categorize_with_case_when.sql

SELECT name, salary,
       CASE
           WHEN salary < 50000 THEN 'Entry'
           WHEN salary BETWEEN 50000 AND 100000 THEN 'Mid'
           ELSE 'Senior'
       END AS salary_band
FROM employees;

The less readable alternative runs three separate queries with different WHERE clauses and stitches the results together in application code, or with a UNION:

SQL · categorize_with_union_alternative.sql

-- Works, but triples the query count for one categorization
SELECT name, salary, 'Entry' AS salary_band FROM employees WHERE salary < 50000
UNION
SELECT name, salary, 'Mid' FROM employees WHERE salary BETWEEN 50000 AND 100000
UNION
SELECT name, salary, 'Senior' FROM employees WHERE salary > 100000;

Evaluates: knowing CASE WHEN handles branching logic inside a single query, avoiding both the extra round trips and the risk of the three ranges overlapping or leaving gaps.

3How do you find records within a rolling date range, like the last 30 days?

SQL · filter_rolling_date_range.sql

SELECT order_id, order_date
FROM orders
WHERE order_date >= CURDATE() - INTERVAL 30 DAY;

A common mistake hardcodes a specific date instead of calculating it relative to the current date, which quietly stops being correct the day after it's written:

SQL · filter_hardcoded_date_bug.sql

-- Wrong: stops working correctly the moment "today" changes
WHERE order_date >= '2026-06-13';

Evaluates: whether the candidate reaches for a relative date calculation (CURDATE() - INTERVAL) rather than a hardcoded literal, which is the difference between a query that's correct once and one that stays correct.

4How do you write a multi-step query using a CTE instead of nested subqueries?

SQL · multi_step_query_with_cte.sql

WITH department_totals AS (
    SELECT department, SUM(salary) AS total_salary
    FROM employees
    GROUP BY department
),
above_average AS (
    SELECT department, total_salary
    FROM department_totals
    WHERE total_salary > (SELECT AVG(total_salary) FROM department_totals)
)
SELECT * FROM above_average;

The same logic without a CTE nests every step inside the next, which works but gets hard to read past two levels:

SQL · multi_step_query_nested_subquery_alternative.sql

-- Correct, but harder to follow and harder to debug one step at a time
SELECT department, total_salary FROM (
    SELECT department, SUM(salary) AS total_salary
    FROM employees GROUP BY department
) dt
WHERE total_salary > (
    SELECT AVG(total_salary) FROM (
        SELECT department, SUM(salary) AS total_salary
        FROM employees GROUP BY department
    ) dt2
);

Notice the nested version also repeats the aggregation logic twice, once for the main calculation and once inside the AVG subquery, which the CTE version avoids entirely by naming the intermediate result once.

Evaluates: whether the candidate reaches for WITH to name and reuse intermediate steps, rather than repeating logic or nesting subqueries several levels deep. This is as much about maintainability as correctness.

5How do you turn rows into columns (a basic pivot)?

SQL · pivot_rows_to_columns.sql

SELECT
    department,
    SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_count,
    SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) AS inactive_count
FROM employees
GROUP BY department;

MySQL has no native PIVOT keyword (unlike SQL Server), so conditional aggregation with CASE WHEN inside SUM or COUNT is the standard workaround. Each CASE expression becomes its own column.

Evaluates: knowing this pattern exists at all. Candidates who haven't seen it before often assume pivoting requires application-side code, when a single grouped query handles it.

6How do you concatenate columns into a single formatted string?

SQL · concatenate_columns_with_separator.sql

SELECT CONCAT_WS(', ', last_name, first_name) AS full_name
FROM employees;

Evaluates: knowing CONCAT_WS (concatenate with separator) skips NULL values gracefully, unlike manually joining strings with + or plain CONCAT, which can produce a NULL result if any single piece is missing.

7How do you handle NULL values in a calculation?

SQL · handle_null_with_coalesce.sql

SELECT name, COALESCE(bonus, 0) + salary AS total_pay
FROM employees;

Evaluates: knowing that NULL + anything evaluates to NULL in SQL, silently breaking the calculation. COALESCE substitutes a default value before the arithmetic happens.

8How do you search for a pattern within a text column?

SQL · pattern_match_with_like.sql

SELECT name, email
FROM users
WHERE email LIKE '%@gmail.com';

Evaluates: basic LIKE and wildcard fluency. A common follow-up asks about REGEXP for more complex pattern matching, and whether the candidate knows LIKE is generally faster for simple prefix or suffix matches.

9How do you conditionally update rows based on a column's value?

SQL · conditional_update_with_case.sql

UPDATE employees
SET bonus = CASE
    WHEN performance_rating = 'excellent' THEN salary * 0.10
    WHEN performance_rating = 'good' THEN salary * 0.05
    ELSE 0
END;

Evaluates: knowing CASE works inside an UPDATE statement, not just SELECT, which avoids running three separate UPDATE statements for three different conditions.

10How do you insert a row, or update it if it already exists?

SQL · upsert_on_duplicate_key.sql

INSERT INTO inventory (product_id, quantity)
VALUES (101, 50)
ON DUPLICATE KEY UPDATE quantity = quantity + 50;

Evaluates: knowing MySQL's upsert pattern (ON DUPLICATE KEY UPDATE), which avoids a separate SELECT to check existence before deciding whether to INSERT or UPDATE. This comes up often in questions about inventory counts or idempotent write operations.

How should you prepare for SQL query-practice interviews?

Practise writing the same query two ways. For pagination, filtering, and multi-step logic especially, knowing both the simple version and the more scalable version, and being able to say why one is better, is exactly what these questions are testing.
Get comfortable with CTEs early. They come up constantly once queries have more than one logical step, and interviewers read a well-structured WITH clause as a sign of someone who writes maintainable SQL, not just correct SQL.
Don't skip NULL handling. It's an easy category to underprepare for, and it's one of the most common sources of silently wrong results in both interviews and real production queries.

Frequently asked questions

Who is this guide for, and what SQL experience does it assume? It's structured to work for candidates at multiple levels: the first few questions assume only basic SELECT/WHERE familiarity, while the later ones (CTEs, upserts) are more commonly asked at intermediate to senior levels.

Why doesn't this guide cover joins or window functions? Those get their own dedicated guides, SQL JOIN Interview Questions and SQL Subquery & Window Function Questions, since they're substantial enough topics on their own. This guide focuses on everything else that comes up in practical query writing.

Is OFFSET pagination ever the right choice? Yes, for small datasets or early pages where the performance cost is negligible. Keyset pagination adds complexity that isn't worth it unless the dataset or page depth is large enough for OFFSET's cost to matter.

Why use a CTE instead of just a subquery? Both can produce the same result. CTEs are preferred when a query has multiple logical steps, since naming each step makes the query easier to read, debug, and reuse within the same statement, particularly when the same intermediate result is needed more than once.

How is this different from the general SQL interview guide? The Top SQL Interview Questions guide focuses on joins, aggregation, and ranking. This guide covers the practical query-writing patterns, dates, strings, NULLs, pagination, and upserts, that come up just as often but are less commonly grouped together.