Why SQL Decides More Interviews Than Any Other Skill
Data analysts, backend developers, QA engineers, business analysts, data engineers — every one of these roles screens on SQL in India. The good news: interviews draw from a surprisingly small pool of questions. Master the 30 below and you've covered what 90% of interviewers ask.
Level 1: Basics (Freshers — Always Asked)
1. Difference between DELETE, TRUNCATE and DROP
- DELETE — removes rows, can have WHERE, can be rolled back, fires triggers
- TRUNCATE — removes all rows, faster, resets identity, minimal logging
- DROP — removes the table itself, structure and all
2. WHERE vs HAVING
WHERE filters rows before grouping; HAVING filters groups after GROUP BY. The classic test: “find departments with more than 10 employees” — that condition must go in HAVING.
3. Explain the types of JOINs
INNER (matching rows only), LEFT (all left rows + matches), RIGHT, FULL OUTER (everything, matched where possible), CROSS (cartesian). Interviewers follow up with: “how many rows does a LEFT JOIN return if the right table has duplicates?” — answer: one row per match, so duplicates multiply.
4. PRIMARY KEY vs UNIQUE KEY
Both enforce uniqueness; primary key allows no NULLs and there's one per table; unique keys allow one NULL (DB-dependent) and can be many.
5. What is normalisation? Explain 1NF, 2NF, 3NF briefly
Organising data to reduce redundancy: 1NF — atomic values; 2NF — no partial dependency on a composite key; 3NF — no transitive dependencies. Follow-up: “when would you deliberately denormalise?” — for read-heavy analytics/reporting.
Level 2: The Query Round (Where Most Candidates Fail)
6. Find the second highest salary
SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); -- or the version they really want: SELECT salary FROM ( SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) rnk FROM employees ) t WHERE rnk = 2;
7. Find duplicate rows in a table
SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;
8. Delete duplicates, keep one
DELETE FROM users WHERE id NOT IN ( SELECT MIN(id) FROM users GROUP BY email );
9. Employees earning more than their manager
SELECT e.name FROM employees e JOIN employees m ON e.manager_id = m.id WHERE e.salary > m.salary;
Self-joins are the most common “can you actually think in SQL” test.
10. Department-wise highest salary with employee name
SELECT dept, name, salary FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) rn FROM employees ) t WHERE rn = 1;
11–15. The rest of the standard query set
- Count of employees per department (GROUP BY warm-up)
- Customers who never placed an order (LEFT JOIN … IS NULL, or NOT EXISTS)
- Monthly sales totals from an orders table (date truncation + GROUP BY)
- Top 3 products by revenue per category (ROW_NUMBER / DENSE_RANK)
- Running total of sales by date (SUM() OVER (ORDER BY date))
Level 3: Window Functions (The 2026 Differentiator)
Window functions now appear in almost every analyst and data-engineering interview — they're the sharpest divider between candidates.
16. ROW_NUMBER vs RANK vs DENSE_RANK
For salaries 100, 100, 90: ROW_NUMBER gives 1,2,3 (arbitrary tie order); RANK gives 1,1,3 (skips); DENSE_RANK gives 1,1,2 (no skip). Knowing which to use for “second highest” and “top N per group” questions is the whole game.
17. LAG and LEAD
Month-over-month growth is the standard question:
SELECT month, revenue, revenue - LAG(revenue) OVER (ORDER BY month) AS growth FROM monthly_sales;
18. Running totals and moving averages
AVG(revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
19. NTILE for quartiles
“Split customers into 4 spending tiers” — NTILE(4) OVER (ORDER BY total_spend DESC).
20. PARTITION BY vs GROUP BY
GROUP BY collapses rows; PARTITION BY keeps every row and adds a computed column alongside. If the interviewer asks for “each employee's salary AND the department average in one query”, that's PARTITION BY.
Level 4: Concepts for 2–5 Years Experience
- 21. Indexes: how B-tree indexes work, clustered vs non-clustered, why too many indexes slow writes
- 22. Query optimisation: reading an execution plan, why SELECT * hurts, sargable predicates (avoid functions on indexed columns in WHERE)
- 23. Transactions & ACID: isolation levels and what dirty/phantom reads are
- 24. Stored procedures vs functions and when to use either
- 25. UNION vs UNION ALL — and why UNION ALL is faster (no dedup sort)
- 26. EXISTS vs IN — correlated subquery behaviour and NULL traps with NOT IN
- 27. CTEs and recursive CTEs — org-hierarchy traversal is the classic recursive question
- 28. Views vs materialised views
- 29. SQL vs NoSQL — when a document store actually makes sense
- 30. Design a schema for a given scenario (e-commerce orders is the favourite) — normalise to 3NF, then defend your keys and indexes
How to Prepare (2-Week Plan)
- Days 1–4: joins + GROUP BY/HAVING until automatic
- Days 5–9: the 15 query patterns above, written by hand, no autocomplete
- Days 10–12: window functions daily — they're 40% of modern interviews
- Days 13–14: concepts (indexes, transactions) + one mock interview
Where SQL Takes You
SQL is the gateway skill for data analyst (₹4–25 LPA), business analyst (₹5–35 LPA) and data engineer (₹6–45 LPA) careers. Pair this prep with our HR round guide and you've covered both halves of the interview.