20 SQL Interview Questions That Test How You Really Think

SQL interviews usually test three things at once: whether you understand relational data, whether you can turn an unclear request into a correct query, and whether you can explain your choices while someone watches. Prepare for all three. Learn the core concepts below, practise a repeatable live-query workflow, and adjust your topic mix to the role instead of memorizing a hundred disconnected definitions.

Which SQL topics should you prepare for your role?

The right preparation depends on what the job will do with data. An analyst interview tends to spend more time on joins, aggregations, date logic, window functions, and explaining business assumptions. A data engineering interview often adds query plans, indexes, partitioning, data quality, and large-table performance. A backend interview may focus on schema design, transactions, concurrency, constraints, and the behaviour of the application's chosen database.

Role	Highest-priority SQL topics	What interviewers often watch
Data analyst	joins, grouping, dates, CTEs, window functions	correct business logic and readable results
Data engineer	transformations, deduplication, query plans, partitions	reliability, scale, and maintainability
Backend engineer	keys, indexes, transactions, isolation, schema choices	correctness under concurrent application traffic

Use that distinction to allocate practice time, not to create blind spots. Everyone should be able to explain joins, grouping, NULL behaviour, keys, and basic performance reasoning. Then spend roughly half your remaining time on the column that matches the role.

Build tiny test tables when you practise. Include one unmatched row, one duplicate, one NULL, one tie, and a date at the edge of the requested range. Those five cases reveal more than a large clean dataset because they force you to check the assumptions hidden in your join, ranking, and filtering logic. In a live round, you can describe the same compact test set even when the editor does not let you insert data. It shows the interviewer that you are verifying behaviour instead of treating a query that happens to run as proof that it is correct.

If the round includes collaborative coding, the communication habits in this pair programming interview guide transfer directly to SQL. The interviewer needs to see how you clarify a requirement and respond to feedback, not only whether the final query runs.

What basic SQL interview questions should you answer clearly?

These questions sound simple, which is exactly why vague answers stand out. Give the definition, explain the practical consequence, and add a short example when it helps.

1. What is the difference between WHERE and HAVING?

WHERE filters rows before grouping and aggregation. HAVING filters the groups produced after GROUP BY, so it can apply conditions to values such as COUNT or SUM. For example, use WHERE to include only orders from 2026, then use HAVING to keep customers whose 2026 order total exceeds a threshold.

A common follow-up is whether you can use a non-aggregate condition in HAVING. Many databases allow it in some situations, but WHERE communicates intent better and may reduce the rows processed before grouping.

2. How do INNER JOIN and LEFT JOIN differ?

INNER JOIN returns only matching rows from both sides. LEFT JOIN keeps every row from the left table and fills the right-side columns with NULL where no match exists. The real test is choosing the correct preserved population. If the question asks for all customers, including those with no orders, customers belong on the left of a LEFT JOIN.

Interviewers often follow with a trap: placing a right-table condition in the WHERE clause can remove the NULL rows and make a LEFT JOIN behave like an inner join. Put the condition in the ON clause when unmatched left-side rows must remain.

3. What is the difference between UNION and UNION ALL?

Both stack compatible result sets. UNION removes duplicates, while UNION ALL retains them. Duplicate removal needs extra work, commonly a sort or hash operation, so UNION ALL is usually faster. Choose based on the data requirement, not speed alone. If two sources can legitimately contain the same row and both events matter, UNION would silently change the meaning.

4. What happens when SQL compares a value with NULL?

NULL represents a missing or unknown value, so comparisons such as column = NULL do not return true. Use IS NULL or IS NOT NULL. SQL uses three-valued logic: a condition can be true, false, or unknown. This matters with NOT IN because a NULL inside the comparison set can make the result unknown for every candidate row. NOT EXISTS is often safer for an anti-join.

5. What is the difference between a primary key and a unique constraint?

Both enforce uniqueness, but a primary key identifies the row's main identity and cannot contain NULL. A table has one primary key, which may contain several columns. A table can have several unique constraints, and NULL handling for them varies by database. Mention that implementation detail instead of claiming identical behaviour across every SQL engine.

6. What does database normalization solve?

Normalization organizes data to reduce duplication and prevent update, insert, and delete anomalies. In practical interview terms, avoid storing the same customer address in every order row if that address represents a customer attribute. Split stable entities into related tables and connect them with keys. Then acknowledge the trade-off: reporting or high-read workloads sometimes use deliberate denormalization when measured performance needs justify it.

Which SQL query questions appear in live exercises?

Most live tasks are variations of a few patterns. Before typing, restate the requested output grain: one row per customer, order, day, or product. Then clarify how duplicates, ties, missing values, and date boundaries should work. Those questions often matter more than syntax.

7. How would you find duplicate values?

Group by the column or columns that define a duplicate, count the rows, and retain groups with a count above one.

SELECT email, COUNT(*) AS row_count
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

The interviewer may then ask for the full duplicate rows. Explain that the grouped result identifies duplicate keys, after which you can join it back to the original table or use a window count. Ask whether one canonical row should be retained and how to choose it before writing a deletion statement.

8. How would you return the second-highest salary?

First ask whether the second-highest distinct salary is required and what to return when fewer than two values exist. A ranking solution makes the tie rule explicit.

WITH ranked AS (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
  FROM employees
)
SELECT DISTINCT salary
FROM ranked
WHERE salary_rank = 2;

DENSE_RANK gives the second distinct value. ROW_NUMBER would assign a different number to each row, while RANK could leave gaps after ties.

9. How do you calculate a running total?

Use SUM as a window function with a clear partition, order, and frame. For a customer's cumulative spend, partition by customer and order by the event timestamp. Add a stable tie-breaker such as order_id when timestamps can match. State whether the database's default frame handles peers as intended, because an explicit ROWS frame often prevents surprises.

10. How would you find the top three products in each category?

Aggregate sales at the product and category level, rank products inside each category, and filter the ranked result. The important choice is tie behaviour. ROW_NUMBER returns exactly three rows per category, while RANK or DENSE_RANK can return more when products tie. Ask which outcome the product requirement expects.

11. How do you find records that have no match in another table?

Use NOT EXISTS or a LEFT JOIN followed by an IS NULL check on a non-nullable right-side key. NOT EXISTS often states the intent cleanly and avoids the NULL problem associated with NOT IN. For example, to find customers without orders, correlate each customer with the orders table and keep customers for whom no matching order exists.

12. How would you calculate a seven-day rolling average?

First clarify whether "seven-day" means seven calendar dates or the current row plus six earlier observations. Missing dates make those definitions different. If the table has one row per date with no gaps, a window frame over six preceding rows can work. Otherwise, build or join a calendar series, aggregate to one row per day, and use the database's supported date-range frame or a self-join with explicit date boundaries.

13. How do you keep the latest row for each customer?

Use ROW_NUMBER partitioned by customer_id and ordered by the relevant timestamp descending, plus a stable tie-breaker descending. Keep row number one. Ask which timestamp defines "latest": ingestion time, event time, or last update time. In data pipelines those values can disagree, and picking the wrong one causes quiet data loss.

14. How would you compare each month's revenue with the previous month?

Aggregate revenue to one row per month, then use LAG to retrieve the prior month's value. Calculate either an absolute change or percentage change based on the question. Guard percentage calculations against a zero or NULL prior value. Also clarify whether months with no revenue should appear, because you may need a calendar table to expose the gap.

For more help keeping your explanation clear while you type, use the same clarify, plan, test, and recover habits covered in these live coding interview tips.

What advanced SQL questions test engineering judgment?

Advanced questions rarely need a single perfect sentence. Interviewers want to hear what you would inspect, what trade-offs you recognize, and how you would verify the result.

15. How would you investigate a slow query?

Start with the execution plan rather than guessing. Check whether the database scans far more rows than the query returns, whether join estimates are wrong, whether a sort spills, and whether an index is used as expected. Then inspect predicate selectivity, stale statistics, data types, functions applied to indexed columns, and unnecessary work such as fetching unused columns.

Propose one change at a time and measure it with representative data. An index can speed reads while increasing storage and write cost, so "add an index" is not a complete answer.

16. When does an index help, and when can it hurt?

An index helps when it lets the database locate a selective subset, satisfy a useful ordering, or cover the needed columns without reading the full table. It can hurt when the indexed value has poor selectivity, the table is tiny, writes dominate, or too many indexes make every insert and update more expensive. Column order matters in a composite index, and the best order depends on actual predicates and sorting patterns.

17. What are transaction isolation levels protecting you from?

Isolation levels control what one transaction can observe while others run. Discuss dirty reads, non-repeatable reads, and phantom rows, then connect the level to the business operation. A payment or inventory update may need stricter guarantees than an approximate dashboard query. Database implementations differ, especially when multi-version concurrency control is involved, so name the engine before making detailed locking claims.

18. CTE, subquery, or temporary table: how do you choose?

Use the form that makes the logic correct and readable first. A CTE is helpful for naming stages and supporting recursion. A subquery can keep a small transformation near its use. A temporary table can be useful when an intermediate result is reused, needs an index, or should be materialized to break a difficult plan into stages. Do not claim that a CTE always materializes or always improves performance; behaviour depends on the database and version.

19. How would you design an audit-friendly order status history?

Keep the current order record and store status changes as separate immutable events with an order key, status, effective timestamp, actor or source, and a stable event identifier. Define allowed transitions in application or database logic, and decide how concurrent changes are serialized. The design should answer both "what is the current status?" and "how did it get there?" without overwriting history.

This is where SQL interviews overlap with architecture rounds. If the discussion expands into storage, consistency, and traffic trade-offs, the system design interview preparation guide gives you a framework for structuring the wider answer.

20. How would you prevent SQL injection?

Use parameterized queries or prepared statements so values are not interpreted as executable SQL. Validate input for business rules, apply least-privilege database permissions, and avoid exposing detailed database errors to users. Escaping strings manually is fragile and should not replace parameter binding. For dynamic identifiers such as a selectable sort column, map a small set of allowed user choices to known SQL fragments rather than passing the raw input through.

How should you talk through a live SQL problem?

Use a consistent sequence: clarify the output grain and edge cases, sketch the stages in plain language, write the simplest correct query, test it with a tiny example, and only then discuss performance. Keep talking, but make each sentence useful. Explain why a join preserves the right population, why a window partition matches the requested grouping, and what a NULL or tie will do.

When your first query fails, do not start over silently. Read the error, isolate the failing clause, and state the smallest correction. Interviewers regularly learn more from a calm recovery than from a lucky first attempt.

Hiintly can help you rehearse this pressure before the real call. Its live interview copilot can listen during a coding interview and surface resume-personalized suggestions in a private overlay. The permanently available free option gives you a 10-minute session, followed by a 5-minute cooldown before another session, so you can practise a short SQL prompt without starting a subscription.

What is an effective SQL interview practice plan?

Run three timed sessions instead of reading question lists repeatedly. In the first, answer six concept questions aloud in 15 minutes. In the second, solve two medium query tasks in 35 minutes while explaining the output grain, assumptions, and test cases. In the third, take one imperfect query and diagnose its correctness and performance for 25 minutes.

After each session, score four areas from zero to two: correctness, clarification, explanation, and testing. A zero means the behaviour was missing, one means it was partial, and two means it was clear and reliable. Review the lowest category before choosing the next exercise. This prevents easy syntax drills from hiding a communication or reasoning weakness.

Finish by practising with the SQL dialect named in the job description. LIMIT versus TOP, date functions, string functions, NULL ordering, and execution-plan tools vary. You do not need every dialect memorized, but you should notice the difference and say what you would verify rather than bluffing.