SQL Interview Questions India 2026: Queries, Joins, Window Functions, and Optimisation

Learn the pattern, not just the answer:

Second highest salary: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees). Or with DENSERANK: SELECT salary FROM (SELECT salary, DENSERANK() OVER (ORDER BY salary DESC) AS rnk FROM employees) t WHERE rnk = 2.

Employees earning more than their manager (self-join): SELECT e.name FROM employees e JOIN employees m ON e.manager_id = m.id WHERE e.salary > m.salary.

Duplicate records: SELECT email, COUNT() FROM users GROUP BY email HAVING COUNT() > 1.

Running total: SELECT name, salary, SUM(salary) OVER (ORDER BY hiredate) AS runningtotal FROM employees.

Employees with no manager: SELECT * FROM employees WHERE manager_id IS NULL.

Delete duplicates keeping one: DELETE FROM employees WHERE id NOT IN (SELECT MIN(id) FROM employees GROUP BY email).

Department with highest average salary: SELECT department FROM employees GROUP BY department ORDER BY AVG(salary) DESC LIMIT 1.

ROW_NUMBER(): unique sequential integer per row in partition. No ties. Use for: top-N per group, pagination, deduplication.

RANK(): same rank for tied rows with gaps (1, 1, 3). Use for: leaderboards where ties share a rank.

DENSE_RANK(): same rank for tied rows, no gaps (1, 1, 2). Use for: 'find all employees whose salary rank is in the top 3' where you want exactly 3 distinct ranks.

LAG(col, n, default): value from n rows before current row in partition. Use for: month-over-month comparison, detecting consecutive events.

LEAD(col, n, default): value from n rows after current row. Use for: time-to-next-event.

SUM/AVG/COUNT OVER: running totals, rolling averages, percentage of total without collapsing rows.

Syntax: FUNCTION() OVER (PARTITION BY col1 ORDER BY col2 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

Problem: find the top 3 highest-paid employees in each department.

Solution: SELECT * FROM ( SELECT name, department, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn FROM employees ) t WHERE rn <= 3

Why ROWNUMBER and not RANK? ROWNUMBER gives exactly N rows per department even with ties. RANK might give more than N rows when tied employees share rank 3.

Month-over-month growth: SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) AS prevmonth, ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month) * 100, 2) AS growthpct FROM monthly_revenue

Index types: B-tree (default; equality and range queries), Hash (equality only, no range), Composite (multiple columns; leftmost prefix rule), Covering (includes all columns a query needs, no table read required).

Clustered vs non-clustered: Clustered = physical row order = one per table (InnoDB: always the primary key). Non-clustered = separate structure pointing to rows; multiple allowed per table.

Query optimisation: EXPLAIN/EXPLAIN ANALYZE shows the execution plan. Look for: Seq Scan (full table scan, needs an index), Index Scan (good), Nested Loop on large tables (may need a hash join). Fixes: add index on WHERE/JOIN/ORDER BY columns; avoid functions on indexed columns in WHERE (WHERE YEAR(createdat) = 2026 prevents index use; use WHERE createdat BETWEEN '2026-01-01' AND '2026-12-31'); rewrite correlated subqueries as JOINs; avoid SELECT *; use CTEs for readability and optimisation opportunities.

INNER JOIN: matching rows in both tables only. Use when you only want records with a match on both sides.

LEFT JOIN: all rows from the left table + matching rows from the right (NULL for non-matches). Use when you want all records from the left table regardless of whether a match exists on the right.

FULL OUTER JOIN: all rows from both tables, NULL on the unmatched side. Use for: records in either table with no match in the other.

CROSS JOIN: Cartesian product (every row from A with every row from B). Use for: generating combinations, date series.

SELF JOIN: table joined to itself. Use for: hierarchical data (employees and managers), finding pairs within the same table.

Common mistake: using INNER JOIN when LEFT JOIN is needed (losing rows with no right-side match).