SQL Interview Levels Explained
Interviewers follow a progression to test depth of knowledge:
- Level 1 (Basic): SELECT, WHERE, ORDER BY, GROUP BY, HAVING, JOINs
- Level 2 (Intermediate): Subqueries, CASE WHEN, aggregate functions, string functions
- Level 3 (Advanced): Window functions (ROW_NUMBER, RANK, LAG, LEAD), CTEs, EXISTS, PIVOT
- Level 4 (Expert): Query optimisation, indexing strategy, execution plans, stored procedures
Must-Know SQL Concepts for Any Interview
These topics appear in 90%+ of SQL interviews:
- Joins: INNER, LEFT, RIGHT, FULL OUTER, CROSS, SELF
- Aggregation: COUNT, SUM, AVG, MAX, MIN with GROUP BY + HAVING
- Subqueries vs. JOINs — when to use which
- Window functions: ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD()
- Common Table Expressions (CTEs) with WITH clause
- NULL handling: IS NULL, COALESCE, NULLIF
- String functions: TRIM, CONCAT, SUBSTRING, UPPER, LOWER, REPLACE
- Date functions: DATEADD, DATEDIFF, YEAR(), MONTH(), FORMAT()
- CASE WHEN for conditional logic
- Index types and when to use them
Easy SQL Questions (Freshers & Junior Level)
These basics should be perfect — any mistake here is a red flag:
- What is the difference between WHERE and HAVING?
- What is the difference between INNER JOIN and LEFT JOIN?
- How do you find duplicate records in a table?
- What is the difference between UNION and UNION ALL?
- How do you find the second highest salary in a table?
Intermediate SQL Questions
Expected from candidates with 1–3 years of experience:
- Write a query to find customers who have placed more than 5 orders
- Find employees earning more than the average salary of their department
- Write a query to calculate the running total of sales by date
- Find all managers who have more than 3 direct reports
- Write a query to transpose rows to columns (PIVOT)
Advanced SQL Questions (Senior Level)
Expected from data analysts, data engineers, and senior developers:
- Explain the difference between ROW_NUMBER(), RANK(), and DENSE_RANK()
- Write a query using LAG() to calculate month-over-month growth
- What is a CTE and when would you use it over a subquery?
- How would you optimise a slow-running query?
- Explain the difference between a clustered and non-clustered index
Common Interview Questions & Answers
Q1. How do you find the second highest salary in a table?
SELECT MAX(salary) AS second_highest FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); -- Or using window functions (preferred in modern SQL): SELECT salary FROM ( SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees ) ranked WHERE rnk = 2;
Always mention the window function approach — it shows modern SQL knowledge.
Q2. What is the difference between RANK() and DENSE_RANK()?
RANK() leaves gaps after tied values (1, 1, 3, 4). DENSE_RANK() does not leave gaps (1, 1, 2, 3). For salary ranking questions, DENSE_RANK() is usually the correct choice.
Explain with an example — don't just define it.
Q3. Write a query to find duplicate emails in a Users table.
SELECT email, COUNT(*) AS count FROM users GROUP BY email HAVING COUNT(*) > 1;
Mention that you can extend this to delete duplicates using a CTE with ROW_NUMBER().
Q4. What is the difference between WHERE and HAVING?
WHERE filters rows BEFORE grouping. HAVING filters groups AFTER GROUP BY. You cannot use aggregate functions in a WHERE clause — that's what HAVING is for.
Draw a simple query example and explain the execution order.
Common Mistakes to Avoid
Using WHERE instead of HAVING for aggregate conditions
Not knowing when to use a subquery vs. a JOIN (JOINs are usually faster)
Selecting * in production queries — always specify columns
Forgetting that NULL ≠ empty string in comparisons
Not being able to explain query optimisation or indexes
Expert Tips
Practice on LeetCode SQL problems daily — start with Easy, move to Medium
Know the execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
Always explain your thought process as you write the query — interviewers value this
Practice writing queries from memory, not just reading them
Pre-Interview Checklist
6 itemsFrequently Asked Questions
Which SQL dialect should I learn for interviews?
Learn ANSI SQL first — it works everywhere. Then learn the specifics of your target stack: T-SQL for SQL Server, PL/pgSQL for PostgreSQL, or MySQL syntax.
How many SQL interview questions should I prepare?
Master 30–50 questions across all difficulty levels. Depth matters more than breadth — being able to solve 30 questions confidently is better than having seen 100 without understanding.
Ready to ace your next interview?
Practice with SpeakWell AI. Upload your resume → get resume-based questions → practice with AI interviewers → improve communication → track progress → get instant AI feedback.