What is the difference between WHERE and HAVING in SQL?

WHERE filters individual rows BEFORE grouping (before GROUP BY). It cannot use aggregate functions. HAVING filters groups AFTER GROUP BY aggregation. It can use aggregate functions like COUNT(), SUM(), AVG(). Rule of thumb: WHERE filters rows, HAVING filters groups.

What are window functions in SQL?

Window functions perform calculations across a set of rows related to the current row (its 'window'), without collapsing the rows into a single output like GROUP BY does. Common window functions: ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD(), LAG(), SUM() OVER(), AVG() OVER(). The OVER() clause defines the window (PARTITION BY and ORDER BY).

SQL Interview Questions India 2026 – Top 60 Questions with Answers

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

INNER JOIN returns only rows where there is a match in BOTH tables. LEFT JOIN returns all rows from the left table + matching rows from the right (NULL if no match). RIGHT JOIN returns all rows from the right table + matching rows from the left (NULL if no match). FULL OUTER JOIN returns all rows from both tables, with NULLs where there is no match on either side.

Why SQL Is Asked in Every Tech Interview

SQL is the universal language of data. Whether you're joining a startup or a MAANG company, SQL is tested in almost every software engineer interview in India. Data engineers, backend engineers, fullstack developers, and even data scientists are expected to know SQL well.

This guide covers 60 of the most frequently asked SQL interview questions in India, organized from basic to advanced, with concise answers and code examples you can use directly in interviews.

💡

What Indian companies test: Service companies (TCS, Infosys, Wipro) focus on basic SELECT, JOINs, GROUP BY, and simple subqueries. Product companies (Amazon, Flipkart, Paytm, Razorpay) go deeper — window functions, CTEs, query optimization, and indexing.

Basic SQL (Q1–Q15)

These questions are asked at every SQL interview, from fresher to 5-year experience. Master these before moving on.

Q1 · Basics

What is the difference between WHERE and HAVING?

WHERE filters individual rows before GROUP BY. It cannot use aggregate functions (COUNT, SUM, etc.).

HAVING filters groups after GROUP BY. It can use aggregate functions.

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

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

Q2 · Basics

What is the difference between DELETE, TRUNCATE, and DROP?

DELETE: Removes rows one by one. Can use WHERE to delete specific rows. Transactional — can be rolled back. Slower. Fires triggers.

TRUNCATE: Removes all rows at once (no WHERE). Faster (deallocates pages). Not easily rolled back in MySQL (in PostgreSQL it is transactional). Does not fire row-level triggers.

DROP: Removes the entire table structure (schema + data). Cannot be rolled back. Table no longer exists after DROP.

Q3 · Constraints

What is the difference between PRIMARY KEY and UNIQUE KEY?

PRIMARY KEY: Uniquely identifies each row. Does NOT allow NULL values. Only one primary key per table (can be composite). Automatically creates a clustered index in MySQL InnoDB.

UNIQUE KEY: Also enforces uniqueness, but allows one NULL value (in most databases). A table can have multiple UNIQUE constraints. Creates a non-clustered index.

Q4 · Subqueries

What is the difference between a correlated and non-correlated subquery?

Non-correlated subquery: The inner query executes once and its result is used by the outer query. Independent of the outer query.

SELECT name FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Correlated subquery: The inner query references the outer query and executes once for each row of the outer query. Much slower.

SELECT e1.name FROM employees e1
WHERE salary > (SELECT AVG(salary) FROM employees e2
                WHERE e2.department = e1.department);

Q5 · NULL Handling

How do you handle NULL values in SQL?

NULL represents missing or unknown data. Key rules:
• Any comparison with NULL returns NULL (not true/false): NULL = NULL → NULL (use IS NULL instead)
• NULL + 5 → NULL. Aggregates ignore NULLs (SUM, AVG, COUNT(col) skip NULLs; COUNT(*) counts all rows).

Useful functions: COALESCE(col, 'default') — returns first non-NULL value. NULLIF(a, b) — returns NULL if a = b (useful to avoid division by zero). IFNULL(col, val) (MySQL), NVL(col, val) (Oracle).

Q6 · DISTINCT

What is the difference between DISTINCT and GROUP BY?

Both eliminate duplicates, but they are used for different purposes:
DISTINCT: Returns unique combinations of the selected columns. No aggregation.

SELECT DISTINCT department FROM employees;

GROUP BY: Groups rows to apply aggregate functions. Can do what DISTINCT does, but also allows COUNT(), SUM(), etc. on groups.

SELECT department, COUNT(*) FROM employees GROUP BY department;

Performance: Modern optimizers often execute them similarly, but GROUP BY is more flexible.

JOINs (Q16–Q25)

JOINs are the most commonly asked SQL topic in Indian interviews. Know all 4 types and when to use each.

Q16 · JOINs

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

INNER JOIN: Returns only rows with a match in both tables.
LEFT JOIN: All rows from left + matching rows from right (NULL if no match).
RIGHT JOIN: All rows from right + matching rows from left (NULL if no match).
FULL OUTER JOIN: All rows from both — NULLs where no match.

SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;
-- employees without dept get NULL for dept_name

CROSS JOIN: Cartesian product — every row of table A with every row of table B. O(m×n) rows. Rarely used directly.

Q17 · Self JOIN

What is a SELF JOIN? Give an example.

A self JOIN joins a table with itself using table aliases. Classic use case: find employees and their managers (both stored in the same table).

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Other uses: find pairs (e.g., find all pairs of employees in the same city), hierarchical data (org charts, category trees).

Q18 · Interview Problem

Find employees who earn more than their manager.

Classic LeetCode #181 — asked at Amazon, Flipkart, and JPMC India.

SELECT e.name AS Employee
FROM Employee e
INNER JOIN Employee m ON e.managerId = m.id
WHERE e.salary > m.salary;

Window Functions (Q31–Q45)

Window functions are the most-asked advanced SQL topic at Indian product companies. Master ROW_NUMBER, RANK, DENSE_RANK, LEAD, and LAG.

Q31 · Window Functions

What are window functions? How are they different from GROUP BY?

Window functions perform calculations across a set of rows related to the current row without collapsing rows (unlike GROUP BY which produces one row per group).

The OVER() clause defines the "window" of rows. PARTITION BY divides into groups. ORDER BY determines row order within the window.

-- GROUP BY: one row per department
SELECT dept, AVG(salary) FROM emp GROUP BY dept;

-- Window function: keeps all rows, adds avg per dept
SELECT name, dept, salary,
  AVG(salary) OVER (PARTITION BY dept) AS dept_avg
FROM emp;

Q32 · Ranking

What is the difference between ROW_NUMBER, RANK, and DENSE_RANK?

Given scores: 100, 90, 90, 80:

Score | ROW_NUMBER | RANK | DENSE_RANK
  100 |          1 |    1 |          1
   90 |          2 |    2 |          2
   90 |          3 |    2 |          2
   80 |          4 |    4 |          3

ROW_NUMBER: Unique sequential number — no ties.
RANK: Same rank for ties, but skips the next rank(s) — 2, 2, 4 (no 3).
DENSE_RANK: Same rank for ties, does NOT skip — 2, 2, 3.

Q33 · Interview Problem

Find the second highest salary in each department.

Classic interview problem — asked at Amazon, Google, and Flipkart:

WITH ranked AS (
  SELECT name, department, salary,
    DENSE_RANK() OVER (PARTITION BY department
                        ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT name, department, salary
FROM ranked
WHERE rnk = 2;

Note: DENSE_RANK is used (not RANK) so that if two employees share rank 1, rank 2 still appears.

Q34 · LAG / LEAD

What are LAG and LEAD window functions? Give a use case.

LAG(col, n): Returns the value of col from n rows before the current row in the window.
LEAD(col, n): Returns the value of col from n rows after the current row.

Use case: Calculate month-over-month revenue growth:

SELECT month, revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
  revenue - LAG(revenue) OVER (ORDER BY month) AS growth
FROM monthly_revenue;

Q35 · CTE

What is a CTE (Common Table Expression)? How is it different from a subquery?

A CTE is a named temporary result set defined with WITH. It can be referenced multiple times in the same query, making complex queries more readable.

WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY dept_id
)
SELECT e.name, e.salary, d.avg_sal
FROM employees e
JOIN dept_avg d ON e.dept_id = d.dept_id
WHERE e.salary > d.avg_sal;

CTE vs Subquery: CTEs are more readable, can be recursive (for hierarchical data), and can be referenced multiple times. Subqueries are inline and can't be reused in the same query.

Indexes & Query Optimization (Q46–Q55)

Index questions are asked at product companies, especially for data-heavy systems. Know when indexes help, when they hurt, and how to check query plans.

Q46 · Indexes

What is a database index? What are the types of indexes?

An index is a data structure (usually a B-tree or hash) that speeds up data retrieval at the cost of additional storage and slower write operations.

Types:
• Clustered index: Determines the physical order of data in the table. One per table. In MySQL InnoDB, the primary key IS the clustered index.
• Non-clustered index: Separate structure that stores index key + pointer to actual row. Multiple allowed per table.
• Composite index: Index on multiple columns. Follows "leftmost prefix" rule — useful for queries filtering on the first column(s) of the index.
• Covering index: An index that includes all columns needed by the query — no need to look up the actual row (index-only scan, very fast).
• Full-text index: For text search (MATCH ... AGAINST). Different from B-tree.

Q47 · Optimization

When should you NOT use an index?

Indexes can actually hurt performance in these cases:
1. Low-cardinality columns: A column with only 2 values (e.g., boolean gender) — the optimizer may prefer a full table scan.
2. Small tables: Full scan is faster than index lookup + row fetch for tiny tables.
3. Heavy write tables: Every INSERT/UPDATE/DELETE must update all indexes — too many indexes slow down writes.
4. Functions on indexed columns: WHERE YEAR(created_at) = 2026 — function prevents index use. Use range instead: WHERE created_at BETWEEN '2026-01-01' AND '2026-12-31'.

Q48 · EXPLAIN

How do you check if a query is using an index? What is EXPLAIN?

Run EXPLAIN SELECT ... before your query. The output shows the query execution plan:

Key fields in MySQL EXPLAIN:
• type: ALL = full table scan (bad), index = full index scan, range = index range scan, ref / eq_ref = efficient index lookup (good).
• key: Which index was used. NULL means no index.
• rows: Estimated rows examined. Lower is better.
• Extra: "Using filesort" and "Using temporary" are warning signs of inefficiency.

Transactions & ACID (Q56–Q60)

Transaction questions are asked at companies building financial systems (Paytm, Razorpay, PhonePe, JPMC). Know ACID, isolation levels, and common anomalies.

Q56 · ACID

What does ACID stand for in database transactions?

Atomicity: A transaction is "all or nothing" — either all operations succeed or none take effect. If one step fails, the entire transaction is rolled back.
Consistency: A transaction brings the database from one valid state to another, maintaining all defined rules (constraints, cascades, triggers).
Isolation: Concurrent transactions execute as if they were serial. One transaction's intermediate state is not visible to others (level depends on isolation setting).
Durability: Once a transaction commits, it persists even if the system crashes — typically ensured by write-ahead logging (WAL).

Q57 · Isolation

What are the four transaction isolation levels and what anomalies do they prevent?

Isolation 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

Dirty Read: Read uncommitted data that may be rolled back later.
Non-repeatable Read: Same query returns different data if another transaction commits in between.
Phantom Read: A query returns different sets of rows if another transaction inserts/deletes rows in between.

MySQL InnoDB default: REPEATABLE READ. PostgreSQL default: READ COMMITTED.

Q58 · Interview Problem

Write a query to find duplicate rows in a table.

Find duplicate emails in a Users table (LeetCode #182):

SELECT email
FROM Person
GROUP BY email
HAVING COUNT(id) > 1;

To find and delete duplicates while keeping one (using CTE + ROW_NUMBER):

WITH cte AS (
  SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
  FROM Person
)
DELETE FROM Person WHERE id IN (SELECT id FROM cte WHERE rn > 1);

Q59 · Interview Problem

Find the Nth highest salary from a table.

Using window functions (cleanest approach for modern MySQL/PostgreSQL):

SELECT DISTINCT salary AS NthHighestSalary
FROM (
  SELECT salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM Employee
) ranked
WHERE rnk = N; -- replace N with the desired rank

Alternative without window functions (for older MySQL):

SELECT MAX(salary) FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee); -- 2nd highest

Q60 · Interview Problem

Write a query to find customers who have placed more than 3 orders in the last 30 days.

SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE order_date >= CURDATE() - INTERVAL 30 DAY
GROUP BY customer_id
HAVING COUNT(*) > 3
ORDER BY order_count DESC;

Note: In PostgreSQL, use CURRENT_DATE - INTERVAL '30 days'. This uses a WHERE clause for date filtering (which can use an index on order_date) combined with HAVING for the aggregate filter — demonstrating both WHERE vs HAVING knowledge.

⚠️

Common SQL interview mistake: Using WHERE on an aggregated column. Always remember — if you're filtering on COUNT(), SUM(), or any aggregate, use HAVING, not WHERE. This mistake alone disqualifies many candidates.

Pranjal Jain

Ex-Microsoft SDE · IIT Kanpur · Founder of Prepflix. Helps engineers crack product company interviews across India.

Top 60 SQL Interview Questions & Answers India 2026

Why SQL Is Asked in Every Tech Interview

Basic SQL (Q1–Q15)

JOINs (Q16–Q25)

Window Functions (Q31–Q45)

Indexes & Query Optimization (Q46–Q55)

Transactions & ACID (Q56–Q60)