Some links in this article are affiliate links. We earn a commission at no extra cost to you when you purchase through them. Full disclosure.

SQL interviews separate developers who can write queries from developers who can write efficient queries. These 10 questions test the difference. (Some interviews also ask when to use NoSQL instead — see MongoDB vs PostgreSQL for that discussion.)

1. What’s the difference between WHERE and HAVING?

WHERE filters rows before grouping. HAVING filters groups after aggregation.

-- WHERE: filter individual rows
SELECT department, COUNT(*) FROM employees WHERE salary > 50000 GROUP BY department;

-- HAVING: filter groups
SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 10;

2. Explain window functions

Window functions perform calculations across a set of rows related to the current row — without collapsing them into groups:

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

Key window functions: ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), SUM() OVER(), AVG() OVER().

3. What’s a CTE and when do you use it?

Common Table Expressions make complex queries readable:

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

Recursive CTEs handle hierarchical data (org charts, category trees):

WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 0 as depth FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, o.depth + 1
  FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT * FROM org;

4. How do you optimize a slow query?

1. EXPLAIN ANALYZE — see the query plan and actual execution time
2. Add indexes on columns in WHERE, JOIN, and ORDER BY clauses
3. **Avoid SELECT *** — only fetch columns you need
4. Limit subqueries — rewrite as JOINs when possible
5. Check for missing indexes on foreign keys

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;
-- Look for: Seq Scan (bad) vs Index Scan (good)

5. What’s the difference between INNER JOIN, LEFT JOIN, and CROSS JOIN?

• INNER JOIN — only matching rows from both tables
• LEFT JOIN — all rows from left table, matching rows from right (NULL if no match)
• CROSS JOIN — every row from left × every row from right (cartesian product)

Follow-up: “When would you use a CROSS JOIN?” → Generating combinations (sizes × colors), date ranges, or test data.

6. Explain transaction isolation levels

Level	Dirty Read	Non-Repeatable Read	Phantom Read
READ UNCOMMITTED	✅ Yes	✅ Yes	✅ Yes
READ COMMITTED	❌ No	✅ Yes	✅ Yes
REPEATABLE READ	❌ No	❌ No	✅ Yes
SERIALIZABLE	❌ No	❌ No	❌ No

Default: PostgreSQL uses READ COMMITTED. MySQL InnoDB uses REPEATABLE READ. The choice of database engine matters — see Postgres vs SQLite vs MySQL for a full comparison.

Senior answer: “I use READ COMMITTED for most operations and SERIALIZABLE only for critical financial transactions where phantom reads would cause data corruption.”

7. What’s an index and when should you NOT add one?

An index is a data structure (usually B-tree) that speeds up lookups at the cost of slower writes and more storage.

Don’t add indexes on:

• Tables with fewer than 1,000 rows (full scan is fast enough)
• Columns with low cardinality (boolean, status with 3 values)
• Columns that are rarely queried
• Tables with heavy write workloads where read speed isn’t critical

8. Write a query to find the second highest salary

-- Using window function (cleanest)
SELECT salary FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rank
  FROM employees
) ranked WHERE rank = 2;

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

9. What’s the N+1 query problem?

Fetching a list of items, then fetching related data for each item individually:

-- 1 query for orders
SELECT * FROM orders;
-- N queries for customers (one per order)
SELECT * FROM customers WHERE id = 1;
SELECT * FROM customers WHERE id = 2;
-- ... 100 more queries

Fix: Use a JOIN or IN clause:

SELECT o.*, c.name FROM orders o JOIN customers c ON o.customer_id = c.id;

10. Explain ACID properties

• Atomicity — all operations in a transaction succeed or all fail
• Consistency — database moves from one valid state to another
• Isolation — concurrent transactions don’t interfere with each other
• Durability — committed data survives crashes

Follow-up: “Which property would you sacrifice for performance?” → Isolation (lower isolation level) is the most common trade-off. If you’re preparing for your first developer job, knowing ACID properties and being able to explain trade-offs will set you apart.

---

Related: SQL Cheat Sheet · PostgreSQL Cheat Sheet

Practice SQL hands-on: Pluralsight has interactive SQL courses with real database exercises — from basics to advanced query optimization. Start a free 10-day trial.