Skip to content
TryHired.
Interview prep · Technical · SQLite 3.49.1

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.

Updated Nov 12, 2026 · Every example run on SQLite 3.49.1 · questions: TryHired
Quick answer

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.

Sample data

The sample tables every query runs on

Advice

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

Basic sql interview questions

Advice

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.

SQL
SELECT 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.

SQL
SELECT 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.

SQL
SELECT
  (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.

SQL
SELECT 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.

SQL
SELECT 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

Intermediate sql interview questions

Advice

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.

SQL
SELECT 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.

SQL
SELECT 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.

SQL
SELECT 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.

SQL
WITH 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.

SQL
SELECT 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.

SQL
SELECT 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.

SQL
EXPLAIN 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

Advanced sql interview questions

Advice

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).

SQL
SELECT 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.

SQL
SELECT 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.

SQL
SELECT 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.

SQL
SELECT 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.

SQL
WITH 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.

SQL
BEGIN;
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.

Practice

How to practice with this page

Advice
  • 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.
Next

Related interview prep

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.

FAQ

Questions people ask

What SQL questions are asked in interviews?
Interviews commonly mix definitions (joins, keys, NULL, WHERE vs HAVING) with query-writing tasks such as second-highest salary, duplicates, top N per group and running totals with window functions. The exact mix depends on the role: analysts get more aggregation and window functions, engineers more indexing and transactions.
Which SQL dialect should I practice?
Practice standard SQL first; the questions here run on SQLite and use syntax that also works in PostgreSQL. MySQL and SQL Server differ in places, such as LIMIT (SQL Server uses TOP or OFFSET ... FETCH) and date functions. Ask the recruiter which database the team uses.
How do I practice for a SQL interview?
Create a small database like the one on this page, write each query yourself before reading the answer, and say out loud what each clause does. Interviewers usually care as much about your reasoning and edge cases (NULLs, ties, empty groups) as the final query.
Are window functions asked in SQL interviews?
Often, especially for data analyst and data engineering roles: ROW_NUMBER, RANK, DENSE_RANK, LAG and running totals with SUM() OVER are common.
What is the difference between SQL and MySQL?
SQL is the query language; MySQL is one database system that uses it, alongside PostgreSQL, SQL Server, Oracle and SQLite.
Method

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.

What this page can't tell you
  • 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).
Sources
  1. SQLite documentation, SQL language reference: https://sqlite.org/lang.html
  2. SQLite documentation, window functions: https://sqlite.org/windowfunctions.html
  3. PostgreSQL documentation, queries: https://www.postgresql.org/docs/current/queries.html