Top 20 SQL Interview Questions & Answers 2026 | sqlinterview

SQL Interview Preparation Guide

SQL interviews test both theoretical knowledge and practical problem-solving under time pressure. Below are the 20 questions that show up most frequently at tech companies — from startups to FAANG-style loops — grouped by difficulty, with detailed explanations, working code, and the follow-up questions interviewers tend to ask next.

Beginner Level

Q1: What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping happens. HAVING filters groups after aggregation (GROUP BY) has already collapsed rows.

SELECT department, AVG(salary) as avg_salary
FROM employees
WHERE salary > 30000        -- filters individual rows, before grouping
GROUP BY department
HAVING AVG(salary) > 50000; -- filters groups, after aggregation

Follow-up interviewers ask: "Can you use HAVING without GROUP BY?" Yes — it treats the whole result set as one group, but it's unusual and WHERE is almost always clearer for that case.

Q2: Explain the difference between DELETE, TRUNCATE, and DROP

DELETE — removes rows one at a time (can use a WHERE clause, fires triggers, is logged row-by-row, and can be rolled back inside a transaction)
TRUNCATE — removes all rows at once (cannot use WHERE, minimal logging, resets auto-increment counters, much faster on large tables)
DROP — removes the entire table structure and all its data permanently, including indexes and constraints

Q3: What is a PRIMARY KEY vs a UNIQUE constraint?

Both enforce uniqueness across a column or set of columns, but a table can have only one PRIMARY KEY (which also implies NOT NULL), while it can have multiple UNIQUE constraints, each of which allows a single NULL value (since two NULLs are never considered equal).

Q4: What is database normalization?

Normalization is the process of organizing tables to reduce data redundancy and prevent update anomalies, typically by splitting data into related tables connected by foreign keys. The most commonly referenced levels are:

1NF — every column holds atomic (indivisible) values, no repeating groups
2NF — 1NF plus every non-key column depends on the whole primary key, not part of it
3NF — 2NF plus no non-key column depends on another non-key column (no transitive dependency)

Most production schemas target 3NF, then selectively denormalize specific tables for read performance where it's justified (see Q20).

Q5: What's the difference between UNION and UNION ALL?

UNION combines the results of two queries and removes duplicate rows, which requires an implicit sort/dedupe step. UNION ALL combines results and keeps every row, including duplicates — it's faster because it skips deduplication entirely.

-- Removes duplicate rows across both result sets
SELECT city FROM customers
UNION
SELECT city FROM suppliers;

-- Keeps everything, including duplicates — faster
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;

Use UNION ALL by default unless you specifically need deduplication — it's a common performance mistake to reach for UNION out of habit.

Q6: What are the main types of SQL JOINs?

INNER JOIN (only matching rows), LEFT JOIN (all left rows plus matches), RIGHT JOIN (all right rows plus matches), FULL OUTER JOIN (all rows from both sides), and CROSS JOIN (every combination of both tables). For a full breakdown with examples and result sets for each, see our dedicated guide: SQL JOINs Explained.

Q7: What's the difference between CHAR and VARCHAR?

CHAR(n) is fixed-length — it always stores exactly n characters, padding shorter values with spaces. VARCHAR(n) is variable-length — it stores only the characters given, up to a max of n, plus a small length prefix. Use CHAR for fixed-format data like country codes; use VARCHAR for almost everything else.

Intermediate Level

Q8: Write a query to find the second highest salary

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
OFFSET 1 LIMIT 1;

-- Alternative using a subquery (works even on engines without OFFSET)
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- Alternative using DENSE_RANK, generalizes cleanly to "Nth highest"
SELECT salary FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk = 2;

Why the DISTINCT matters: without it, if two people tie for the highest salary, a naive OFFSET 1 LIMIT 1 would return that same top value again instead of the true second-highest.

Q9: What are window functions? Give an example.

Window functions perform a calculation across a set of rows related to the current row — a "window" — without collapsing them into a single output row the way GROUP BY does. Each input row keeps its own row in the result.

SELECT
  name,
  department,
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank,
  AVG(salary) OVER (PARTITION BY department) as dept_avg
FROM employees;

This is one of the highest-value topics to master for interviews — it comes up constantly in "top N per group" and running-total style questions.

Q10: What is a CTE (Common Table Expression)?

A CTE is a temporary, named result set defined with WITH that exists only for the duration of a single query. It improves readability by breaking a complex query into logical steps, and it's the only way to write a recursive query in standard SQL.

WITH high_earners AS (
  SELECT * FROM employees WHERE salary > 100000
)
SELECT department, COUNT(*)
FROM high_earners
GROUP BY department;

-- Recursive CTE: walk an employee-manager hierarchy
WITH RECURSIVE org_chart AS (
  SELECT id, name, manager_id, 1 AS level
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, oc.level + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart;

Q11: Explain the difference between RANK, DENSE_RANK, and ROW_NUMBER

ROW_NUMBER() — assigns unique sequential numbers regardless of ties (1, 2, 3, 4)
RANK() — gives the same rank to ties, then skips the next number(s) (1, 1, 3, 4)
DENSE_RANK() — gives the same rank to ties, with no gaps afterward (1, 1, 2, 3)

A very common interview follow-up: "which one would you use to remove duplicates while keeping one row per group?" — ROW_NUMBER(), partitioned by the dedup key, then filter WHERE rn = 1.

Q12: What's the difference between a subquery, a CTE, and a temp table?

Subquery — a query nested inside another query; scoped to that single statement, can hurt readability when nested deeply
CTE — a named subquery defined once with WITH, reusable multiple times within the same statement, supports recursion
Temp table — a physical (or in-memory) table that persists for the session or transaction, can be indexed, and is useful when the same intermediate result is reused across multiple separate statements

Q13: What's the difference between GROUP BY and PARTITION BY?

GROUP BY collapses multiple rows into one row per group — you lose row-level detail. PARTITION BY (used inside window functions) divides rows into groups for the calculation but keeps every original row in the output. If you need both a detail row and an aggregate value on the same line, PARTITION BY is the tool; if you only need the aggregate, GROUP BY is simpler and often faster.

Q14: What is a correlated subquery, and how is it different from a regular subquery?

A correlated subquery references a column from the outer query, so it must be re-evaluated once for every row the outer query processes — unlike a regular (uncorrelated) subquery, which runs once and its result is reused. Correlated subqueries are powerful but can be slow on large tables; they can often be rewritten as a JOIN or a window function for better performance.

-- Correlated subquery: re-evaluated per outer row
SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (
  SELECT AVG(salary) FROM employees e2 WHERE e2.department = e.department
);

Advanced Level

Q15: What is an execution plan and how do you read one?

An execution plan shows the exact steps the database engine will take to run a query — which access method it uses per table, the join strategy, and estimated vs. actual row counts. Key things to look for:

Seq Scan / Table Scan on a large table — a strong signal an index is missing
Nested Loop vs. Hash Join vs. Merge Join — different join strategies suited to different data sizes and index availability
Large gaps between estimated and actual row counts — usually a sign of stale statistics

Q16: How do indexes work internally?

Most relational databases implement indexes as B-tree structures. The index stores column values in sorted order with pointers back to the corresponding rows. A lookup on an indexed column traverses the tree in O(log n) time instead of scanning the whole table in O(n) time — the difference between milliseconds and seconds once a table reaches millions of rows. Some databases also support hash indexes (fast equality lookups only, no range queries) and specialized index types like GIN/GiST in PostgreSQL for full-text search and JSON.

Q17: What is a deadlock, and how do you prevent it?

A deadlock occurs when two transactions each hold a lock the other one needs, so neither can proceed — the database detects this and forcibly rolls back one of them (the "deadlock victim"). Prevention strategies:

Access tables and rows in a consistent order across every transaction in the codebase
Keep transactions as short as possible — acquire locks late, release them early
Use appropriate isolation levels for the workload (see Q19)
Implement retry logic in the application for deadlock-victim errors, since they're expected under concurrency, not bugs by themselves

Q18: What are the ACID properties?

Atomicity — a transaction either fully completes or fully rolls back; there's no partial state
Consistency — a transaction moves the database from one valid state to another, respecting all constraints
Isolation — concurrent transactions don't see each other's uncommitted changes (the exact degree depends on isolation level)
Durability — once committed, a transaction's changes survive a crash or power loss

Q19: What are transaction isolation levels, and what problems do they prevent?

Isolation levels trade consistency for concurrency. From weakest to strongest: Read Uncommitted, Read Committed, Repeatable Read, and Serializable. They protect against three classic phenomena:

Dirty read — reading another transaction's uncommitted changes
Non-repeatable read — re-reading the same row within a transaction and getting a different value because another transaction committed a change in between
Phantom read — re-running the same query within a transaction and getting a different set of rows because another transaction inserted or deleted matching rows

Most applications default to Read Committed (PostgreSQL's and Oracle's default) as a practical balance; Serializable gives the strongest guarantees but the most locking overhead.

Q20: When would you denormalize a database, and what are the trade-offs?

Denormalization deliberately introduces redundancy — duplicating data or pre-computing aggregates — to reduce the number of JOINs a read-heavy query needs. It's a reasonable trade when read performance is critical and the data changes infrequently (analytics dashboards, reporting tables, caching layers). The cost is update complexity: the same fact now lives in multiple places, so writes must keep every copy in sync, and it becomes easier for data to drift out of consistency if that discipline slips. The general rule: normalize by default for correctness, denormalize selectively and deliberately once you have a measured performance problem — not preemptively.

Practical Query Challenges

Beyond definitions, most interviews include one or two hands-on query problems. Here are the patterns that recur most often:

Find employees who earn more than their department's average

SELECT e.name, e.salary, e.department
FROM employees e
JOIN (
  SELECT department, AVG(salary) as avg_sal
  FROM employees
  GROUP BY department
) d ON e.department = d.department
WHERE e.salary > d.avg_sal;

-- Equivalent, often cleaner, using a window function
SELECT name, salary, department FROM (
  SELECT name, salary, department,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg
  FROM employees
) t
WHERE salary > dept_avg;

Find duplicate records in a table

SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Find the Nth highest salary per department (top-N per group)

SELECT name, department, salary FROM (
  SELECT name, department, salary,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk <= 3;   -- top 3 earners in each department

Calculate a running total

SELECT order_date, amount,
  SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

Find gaps in a sequence (e.g. missing order IDs)

SELECT id + 1 AS gap_start
FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM orders WHERE id = o.id + 1)
ORDER BY id;

How to Prepare in the Final Week

Practice writing queries by hand or in a plain text editor, not just in an IDE with autocomplete — most interview platforms don't offer it
Say your reasoning out loud as you write, especially around edge cases like NULLs and ties — interviewers are grading your thought process, not just the final query
Get comfortable with window functions specifically; they're the single most common gap between candidates who pass and candidates who don't
Review the execution plan basics (Q15) even for roles that aren't explicitly "performance" focused — it comes up as a follow-up question constantly

Conclusion

These 20 questions cover the range interviewers actually draw from — fundamentals, aggregation, window functions, and the transaction/indexing internals that separate a confident answer from a memorized one. Practice each pattern until you can write it without hesitating, then move on to timed, realistic problems.

Put these into practice with our interactive SQL questions, where you write and execute queries against live databases, our real-world case studies that simulate actual business problems, and our companion guides on SQL JOINs and query optimization.