SQL interview questions: answers with real query output
Joins, aggregates, NULLs, window functions and recursive CTEs, each answered in plain words and shown on real sample tables.
Common SQL interview questions test joins (INNER vs LEFT), filtering groups (WHERE vs HAVING), NULL handling, finding duplicates and the second-highest value, and window functions such as ROW_NUMBER, RANK, running totals and LAG. Below are 21 questions from basic to advanced; each query runs on the same small sample tables, and the output shown is the real result.
The sample tables every query runs on
Three small tables with fictional data: departments, employees (with a manager and a department, both nullable) and orders. Every query below runs against a fresh copy, so examples do not affect each other.
CREATE TABLE departments (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
INSERT INTO departments VALUES (1, 'Engineering'), (2, 'Sales'), (3, 'Support');
CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT NOT NULL, dept_id INTEGER REFERENCES departments(id), manager_id INTEGER REFERENCES employees(id), salary INTEGER NOT NULL);
INSERT INTO employees VALUES
(1, 'Ava', 1, NULL, 150000), (2, 'Ben', 1, 1, 120000), (3, 'Cara', 1, 1, 120000), (4, 'Dev', 2, 1, 95000),
(5, 'Eli', 2, 4, 70000), (6, 'Fay', 3, 1, 65000), (7, 'Gus', 3, 6, 52000), (8, 'Hana', NULL, 1, 60000);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT NOT NULL, amount INTEGER NOT NULL, ordered_on TEXT NOT NULL);
INSERT INTO orders VALUES
(1, 'acme', 200, '2026-01-05'), (2, 'globex', 150, '2026-01-20'), (3, 'acme', 300, '2026-02-02'),
(4, 'initech', 120, '2026-02-14'), (5, 'acme', 200, '2026-03-01'), (6, 'globex', 400, '2026-03-09');Basic sql interview questions
Basic1. What is the difference between WHERE and HAVING?
WHERE filters rows before they are grouped; HAVING filters groups after GROUP BY, so it can use aggregates such as COUNT or SUM. A query can use both: WHERE to drop rows, then HAVING to drop groups.
SQLSELECT dept_id, COUNT(*) AS people
FROM employees
WHERE salary > 60000
GROUP BY dept_id
HAVING COUNT(*) >= 2;Output (SQLite 3.49.1)dept_id | people --------+------- 1 | 3 2 | 2
Basic2. What is the difference between INNER JOIN and LEFT JOIN?
INNER JOIN keeps only rows that match in both tables. LEFT JOIN keeps every row from the left table and fills the right side with NULL when there is no match, which is how you find rows with nothing to join to. Hana has no department, so she appears only in the LEFT JOIN.
SQLSELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON d.id = e.dept_id
WHERE e.salary < 66000;Output (SQLite 3.49.1)name | department -----+----------- Fay | Support Gus | Support Hana | NULL
Basic3. What is the difference between a primary key and a foreign key?
A primary key uniquely identifies each row in its own table and cannot repeat. A foreign key is a column that refers to a primary key in another (or the same) table, such as employees.dept_id pointing to departments.id. It lets the database enforce that the referenced row exists. (SQLite only enforces foreign keys when PRAGMA foreign_keys is ON.)
Basic4. How does NULL behave in comparisons?
NULL means unknown, so any comparison with = or <> returns unknown rather than true, and the row is filtered out. Use IS NULL or IS NOT NULL instead.
SQLSELECT
(SELECT COUNT(*) FROM employees WHERE dept_id = NULL) AS equals_null,
(SELECT COUNT(*) FROM employees WHERE dept_id IS NULL) AS is_null;Output (SQLite 3.49.1)equals_null | is_null ------------+-------- 0 | 1
Basic5. What is the difference between COUNT(*) and COUNT(column)?
COUNT(*) counts rows. COUNT(column) counts only rows where that column is not NULL. COUNT(DISTINCT column) counts distinct non-NULL values.
SQLSELECT COUNT(*) AS all_rows, COUNT(dept_id) AS with_dept, COUNT(DISTINCT dept_id) AS departments
FROM employees;Output (SQLite 3.49.1)all_rows | with_dept | departments ---------+-----------+------------ 8 | 7 | 3
Basic6. What is the difference between UNION and UNION ALL?
UNION combines two result sets and removes duplicate rows; UNION ALL keeps every row, including duplicates, and is faster because it skips the duplicate check.
SQLSELECT customer FROM orders WHERE amount >= 300
UNION
SELECT customer FROM orders WHERE ordered_on >= '2026-03-01';
SELECT customer FROM orders WHERE amount >= 300
UNION ALL
SELECT customer FROM orders WHERE ordered_on >= '2026-03-01';Output (SQLite 3.49.1)customer -------- acme globex customer -------- acme globex acme globex
Basic7. What is the difference between DELETE, TRUNCATE and DROP?
DELETE removes rows (all, or those matching a WHERE clause) and can be rolled back inside a transaction. TRUNCATE removes all rows at once without row-by-row logging in databases that support it, such as PostgreSQL, MySQL and SQL Server. DROP removes the table itself, including its structure. SQLite has no TRUNCATE: a DELETE without WHERE does the same job.
Intermediate sql interview questions
Intermediate8. How do you find the second-highest salary?
Two common answers: take the maximum salary below the overall maximum, or rank salaries with DENSE_RANK and pick rank 2. DENSE_RANK handles ties correctly: Ben and Cara share the second-highest salary, so both are returned.
SQLSELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
SELECT name, salary FROM (
SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r FROM employees
) WHERE r = 2;Output (SQLite 3.49.1)second_highest -------------- 120000 name | salary -----+------- Ben | 120000 Cara | 120000
Intermediate9. How do you find duplicate values?
Group by the column (or columns) that should be unique and keep groups with more than one row. Here, acme placed two orders of the same amount.
SQLSELECT customer, amount, COUNT(*) AS times
FROM orders
GROUP BY customer, amount
HAVING COUNT(*) > 1;Output (SQLite 3.49.1)customer | amount | times ---------+--------+------ acme | 200 | 2
Intermediate10. How do you show each employee with their manager's name?
Use a self join: join the employees table to itself, matching manager_id to id. A LEFT JOIN keeps the top manager, whose manager_id is NULL.
SQLSELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id
ORDER BY e.id
LIMIT 4;Output (SQLite 3.49.1)employee | manager ---------+-------- Ava | NULL Ben | Ava Cara | Ava Dev | Ava
Intermediate11. What is a CTE, and when would you use one instead of a subquery?
A common table expression (WITH ...) names a subquery so you can read the query top to bottom and reuse the result. It is usually clearer than nesting, and it is required for recursive queries.
SQLWITH dept_pay AS (
SELECT dept_id, SUM(salary) AS total FROM employees GROUP BY dept_id
)
SELECT d.name, p.total
FROM dept_pay p JOIN departments d ON d.id = p.dept_id
ORDER BY p.total DESC;Output (SQLite 3.49.1)name | total ------------+------- Engineering | 390000 Sales | 165000 Support | 117000
Intermediate12. How do you use CASE to bucket values?
CASE returns a value from the first matching WHEN condition, and the ELSE value otherwise. It works in SELECT, ORDER BY and inside aggregates.
SQLSELECT name,
CASE WHEN salary >= 100000 THEN 'high'
WHEN salary >= 65000 THEN 'mid'
ELSE 'entry' END AS band
FROM employees
ORDER BY salary DESC
LIMIT 5;Output (SQLite 3.49.1)name | band -----+----- Ava | high Ben | high Cara | high Dev | mid Eli | mid
Intermediate13. How do you find employees who earn more than the mean salary of their department?
Use a correlated subquery that computes AVG(salary), the mean, for the same department as the outer row (or join to a grouped CTE). Hana has no department, so the comparison is unknown and she is excluded.
SQLSELECT e.name, e.salary
FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees x WHERE x.dept_id = e.dept_id)
ORDER BY e.salary DESC;Output (SQLite 3.49.1)name | salary -----+------- Ava | 150000 Dev | 95000 Fay | 65000
Intermediate14. What is an index, and how do you know a query uses it?
An index is a separate sorted structure that lets the database find rows without scanning the whole table, at the cost of extra storage and slower writes. Check the query plan: before the index SQLite scans the table; after, it searches the index.
SQLEXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer = 'acme';
CREATE INDEX idx_orders_customer ON orders(customer);
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer = 'acme';Output (SQLite 3.49.1)id | parent | notused | detail ---+--------+---------+------------ 2 | 0 | 216 | SCAN orders id | parent | notused | detail ---+--------+---------+----------------------------------------------------------- 3 | 0 | 62 | SEARCH orders USING INDEX idx_orders_customer (customer=?)
Advanced sql interview questions
Advanced15. What is the difference between ROW_NUMBER, RANK and DENSE_RANK?
All three number rows in a window. ROW_NUMBER gives every row a unique number. RANK gives ties the same number and then skips (1, 2, 2, 4). DENSE_RANK gives ties the same number without gaps (1, 2, 2, 3).
SQLSELECT name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
RANK() OVER (ORDER BY salary DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense
FROM employees
LIMIT 4;Output (SQLite 3.49.1)name | salary | row_num | rnk | dense -----+--------+---------+-----+------ Ava | 150000 | 1 | 1 | 1 Ben | 120000 | 2 | 2 | 2 Cara | 120000 | 3 | 2 | 2 Dev | 95000 | 4 | 4 | 3
Advanced16. How do you calculate a running total?
Use SUM as a window function with ORDER BY. Each row's total includes every earlier row in that order.
SQLSELECT ordered_on, amount,
SUM(amount) OVER (ORDER BY ordered_on) AS running_total
FROM orders;Output (SQLite 3.49.1)ordered_on | amount | running_total -----------+--------+-------------- 2026-01-05 | 200 | 200 2026-01-20 | 150 | 350 2026-02-02 | 300 | 650 2026-02-14 | 120 | 770 2026-03-01 | 200 | 970 2026-03-09 | 400 | 1370
Advanced17. How do you get the top N rows per group?
Number the rows inside each group with ROW_NUMBER() OVER (PARTITION BY ...), then keep the rows numbered up to N. Here: the highest-paid person in each department.
SQLSELECT department, name, salary FROM (
SELECT d.name AS department, e.name, e.salary,
ROW_NUMBER() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS rn
FROM employees e JOIN departments d ON d.id = e.dept_id
) WHERE rn = 1
ORDER BY salary DESC;Output (SQLite 3.49.1)department | name | salary ------------+------+------- Engineering | Ava | 150000 Sales | Dev | 95000 Support | Fay | 65000
Advanced18. How do you compare each row with the previous one?
LAG returns a value from an earlier row in the window (LEAD looks ahead). It is the standard way to compute change from the previous period.
SQLSELECT substr(ordered_on, 1, 7) AS month, SUM(amount) AS revenue,
SUM(amount) - LAG(SUM(amount)) OVER (ORDER BY substr(ordered_on, 1, 7)) AS change
FROM orders
GROUP BY month;Output (SQLite 3.49.1)month | revenue | change --------+---------+------- 2026-01 | 350 | NULL 2026-02 | 420 | 70 2026-03 | 600 | 180
Advanced19. How do you walk a hierarchy with a recursive CTE?
A recursive CTE has an anchor query (the starting rows) and a recursive part that joins back to the CTE until no new rows appear. Here: Gus's chain of managers up to the top.
SQLWITH RECURSIVE chain(id, name, manager_id, depth) AS (
SELECT id, name, manager_id, 0 FROM employees WHERE name = 'Gus'
UNION ALL
SELECT e.id, e.name, e.manager_id, c.depth + 1
FROM employees e JOIN chain c ON e.id = c.manager_id
)
SELECT depth, name FROM chain;Output (SQLite 3.49.1)depth | name ------+----- 0 | Gus 1 | Fay 2 | Ava
Advanced20. What does a transaction ensure?
A transaction groups statements so they succeed or fail together (atomicity), leave the data valid (consistency), do not see each other's half-finished work (isolation) and survive once committed (durability): ACID. ROLLBACK undoes everything since BEGIN.
SQLBEGIN;
DELETE FROM orders;
SELECT COUNT(*) AS during FROM orders;
ROLLBACK;
SELECT COUNT(*) AS after_rollback FROM orders;Output (SQLite 3.49.1)during ------ 0 after_rollback -------------- 6
Advanced21. What is normalization?
Normalization organizes tables so each fact is stored once: first normal form removes repeating groups, second removes columns that depend on only part of a composite key, and third removes columns that depend on other non-key columns. Here, department names live in departments, not repeated on every employee row. Analytics tables are sometimes deliberately denormalized to make reads faster.
How to practice with this page
- Read the question, write your own query against the sample tables, then compare.
- Say each clause's job out loud: interviewers listen for your reasoning.
- Check the edge cases every time: NULLs, ties, empty groups and duplicates.
- Copy the sample schema into any SQLite tool (or db-fiddle) to run the queries yourself.
Related interview prep
Core language questions with real output.
Open →RoleDatabase administrator interview questionsRole-specific questions for DBAs.
Open →RoleData analyst interview questionsThe questions analysts prepare for.
Open →GuideCommon interview questionsThe behavioral questions technical interviews include too.
Open →Disclosure: TryHired makes HIRED, a paid job-search program, so we have a conflict of interest when we mention it. This question bank is free, with no TryHired signup.
Analyst and data roles: continue with data science, Power BI and Excel interview questions.
Questions people ask
What SQL questions are asked in interviews?
Which SQL dialect should I practice?
How do I practice for a SQL interview?
Are window functions asked in SQL interviews?
What is the difference between SQL and MySQL?
How this page is built
TryHired wrote these questions and answers. Every code block on this page was executed with SQLite 3.49.1 when the page was built, and the output shown is the real output; the build stops if any example fails. The question list is general practice: it does not predict what a particular employer will ask.
- Interviewers ask different questions for different roles and levels.
- Answers are short explanations, not complete language references.
- Examples use SQLite; PostgreSQL accepts the same syntax here, while MySQL and SQL Server differ in places (such as LIMIT and date functions).
- SQLite documentation, SQL language reference: https://sqlite.org/lang.html
- SQLite documentation, window functions: https://sqlite.org/windowfunctions.html
- PostgreSQL documentation, queries: https://www.postgresql.org/docs/current/queries.html