Voxtera AIVoxtera AI
Technical

SQL Interview Questions

SQL is tested in data, engineering, analytics, and backend roles. These questions cover everything from basic queries to advanced window functions, performance optimization, and complex multi-table analysis.

Practice with AI Feedback — FreeAI Mock Interview

Sample Questions & Strong Answers

1

What is the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN?

INNER JOIN returns only rows where the join condition matches in both tables. LEFT JOIN returns all rows from the left table, plus matching rows from the right (NULL for non-matches). RIGHT JOIN is the mirror. FULL OUTER JOIN returns all rows from both tables, with NULLs where there's no match.

💡

Pro tip: Draw a Venn diagram mentally when choosing join types. In practice, INNER and LEFT JOINs cover 95%+ of use cases.

2

What is a window function? Give an example.

Window functions perform calculations across a set of rows related to the current row, without collapsing rows (unlike GROUP BY). Example: SELECT name, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) as salary_rank FROM employees;

💡

Pro tip: Know ROW_NUMBER (unique rank), RANK (gaps allowed), DENSE_RANK (no gaps), LAG/LEAD (previous/next rows), and SUM/AVG OVER PARTITION BY.

3

What is the difference between WHERE and HAVING?

WHERE filters rows before grouping/aggregation. HAVING filters groups after aggregation. Use WHERE to filter individual rows on non-aggregated columns; use HAVING to filter results of aggregate functions like COUNT, SUM, AVG.

💡

Pro tip: You can use both in the same query: WHERE filters raw rows, HAVING filters the aggregated groups.

4

How do you find duplicate rows in a table?

SELECT column, COUNT(*) as count FROM table GROUP BY column HAVING COUNT(*) > 1. For multiple columns, group by all the columns that define uniqueness.

💡

Pro tip: A follow-up is often 'how do you delete duplicates while keeping one row?' — know ROW_NUMBER with DELETE for this.

Expert tips

1

Master the order of SQL operations: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.

2

Window functions are the most commonly tested advanced SQL topic — practice ROW_NUMBER, RANK, LAG, LEAD, and SUM OVER PARTITION BY.

3

For complex queries, build incrementally: start with a subquery or CTE, verify it works, then add the outer query.

4

Always think about edge cases: NULL values, empty tables, ties in ranking, and duplicate keys.

Related interview topics

Behavioral QuestionsSTAR MethodLeadership QuestionsSystem Design

SQL Interview Questions FAQ

A CTE (Common Table Expression) is a named temporary result set defined with the WITH keyword, used to simplify complex queries. Use CTEs when: you need to reference the same subquery multiple times, you want to break a complex query into readable logical steps, or you need to write recursive queries. CTEs improve readability significantly; some databases also optimize them differently than subqueries.

UNION combines two query result sets and removes duplicates (requires sorting). UNION ALL combines result sets and keeps all rows including duplicates — it's faster because no deduplication step is needed. Use UNION ALL unless you specifically need deduplication — it's significantly more performant at scale.

Step-by-step approach: 1) Use EXPLAIN/EXPLAIN ANALYZE to see the execution plan, 2) Check for missing indexes (full table scans on large tables), 3) Reduce data early — apply WHERE filters as early as possible, 4) Avoid SELECT * — only select needed columns, 5) Check for N+1 patterns — multiple queries where one would suffice, 6) Use appropriate JOINs, 7) Consider materialized views for frequently repeated complex queries.

Indexes are data structures that speed up data retrieval at the cost of write performance and storage. Use indexes on columns frequently used in WHERE clauses, JOIN conditions, and ORDER BY/GROUP BY. Don't index every column — heavy write tables pay too high an update cost. Composite indexes should match common query patterns. A B-tree index is the default; consider hash indexes for equality-only lookups and partial indexes for filtered subsets.

Practice SQL Interview Questions with real-time AI.

Stop reading sample answers and start practicing. Get personalized feedback on structure, delivery, and depth — every time.

Start Free PracticeAI Mock Interview

Free plan available · No credit card needed · Cancel anytime