Top 50 SQL Interview Questions � Crack Your Next Interview

Why SQL Is the #1 Interview Skill

In 2026, SQL remains the most in-demand skill for data-related roles. A comprehensive analysis of job postings on Naukri.com and LinkedIn shows that 92% of Data Analyst roles, 85% of Data Scientist roles, and 95% of Data Engineer roles require SQL proficiency. Whether you are interviewing in Lucknow, Bangalore, or for remote positions, SQL questions are a certainty.

But here is the reality most candidates face: they know SQL basics but freeze when asked about window functions, correlated subqueries, or query optimization. The gap between "I know SQL" and "I can solve complex SQL problems under pressure" is significant. This guide bridges that gap with 50 questions organized from basic to advanced, each with code examples and interview tips.

At DSWallah, we have prepared over 300 students for SQL interviews. Our curriculum covers every question in this guide with hands-on practice on real datasets. Students who complete our program report 3x higher confidence in SQL interviews.

How to Use This Guide

This guide covers 50 SQL questions organized into three difficulty tiers. Each question includes the answer, code example, and an interview tip explaining what the interviewer is really testing. Work through all three tiers sequentially � do not skip to advanced questions without solidifying basics.

Basic Questions (1-15): Foundation

These appear in every SQL interview. If you cannot answer these fluently, you will not pass. Master these first.

Intermediate Questions (16-35): Core Skills

These separate good candidates from great ones. Window functions, CTEs, and optimization knowledge are tested here.

Advanced Questions (36-50): Expert Level

These are asked at top companies and for senior roles. Recursive CTEs, complex analytics, and performance optimization.

Basic SQL Questions (1�15)

1. What is the difference between WHERE and HAVING?

WHERE filters rows before the GROUP BY clause is applied. HAVING filters groups after GROUP BY aggregation. You cannot use aggregate functions (COUNT, SUM, AVG) in WHERE.

SELECT city, COUNT(*) as cnt
FROM students WHERE age > 18
GROUP BY city HAVING COUNT(*) > 5;

Interview Tip: Always mention that WHERE cannot use aggregate functions � this shows deeper understanding.

2. What is the difference between DELETE and TRUNCATE?

DELETE: DML operation. Uses WHERE clause. Logged, can rollback. Triggers fire. Slower.

TRUNCATE: DDL operation. No WHERE clause. Minimal logging. Cannot rollback. Resets identity. Faster.

Interview Tip: Mention that TRUNCATE resets the identity counter � this detail impresses interviewers.

3. What is a PRIMARY KEY?

A column or set of columns that uniquely identifies each row. Cannot be NULL. Must be unique. Only one per table. Automatically creates a clustered index.

CREATE TABLE students (
 id INT PRIMARY KEY,
 name VARCHAR(100) NOT NULL
);

4. What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN: Returns rows with matches in both tables.

LEFT JOIN: Returns all rows from the left table. Matching rows from right. NULLs where no match.

SELECT s.name, e.course_id
FROM students s LEFT JOIN enrollments e ON s.id = e.student_id;

5. What is a FOREIGN KEY?

A column that references the PRIMARY KEY of another table. Ensures referential integrity � prevents inserting orphan records.

6. What are aggregate functions?

COUNT(), SUM(), AVG(), MAX(), MIN() � perform calculations on sets of rows and return single values. Used with GROUP BY.

SELECT department, AVG(salary) as avg_salary
FROM employees GROUP BY department;

7. What is GROUP BY?

Groups rows with identical values into summary rows. Used with aggregates. Every non-aggregate column in SELECT must appear in GROUP BY.

8. What is a subquery?

A query nested inside another query. Can appear in WHERE, SELECT, FROM, or HAVING.

SELECT name FROM students
WHERE id IN (SELECT student_id FROM enrollments WHERE course_id = 1);

9. What is the difference between UNION and UNION ALL?

UNION: Combines and removes duplicates. Slower.

UNION ALL: Combines and keeps all rows. Faster. Use when duplicates are acceptable.

10. What is NULL?

Represents missing or unknown data. Not zero, not empty string. Use IS NULL / IS NOT NULL for comparison. Aggregate functions ignore NULLs except COUNT(*).

11. What is the difference between WHERE and ON?

WHERE: Filters after joins. ON: Defines join condition. In LEFT JOINs, ON vs WHERE produces different results.

12. What is ORDER BY?

Sorts results by columns. ASC (default) or DESC. Can sort by column name, alias, or position.

SELECT name, salary FROM employees ORDER BY salary DESC;

13. What is DISTINCT?

Removes duplicate rows from results. Applies to all selected columns. Can be expensive on large datasets � the database must sort or hash all rows.

14. What is the difference between CHAR and VARCHAR?

CHAR: Fixed-length. Padded with spaces. Faster for fixed-size data.

VARCHAR: Variable-length. More efficient for varying data. Uses extra byte for length.

15. What is a view?

A stored query that acts as a virtual table. Does not store data. Simplifies complex queries, provides security, maintains consistency.

CREATE VIEW high_earners AS
SELECT name, salary FROM employees WHERE salary > 80000;

Intermediate SQL Questions (16�35)

16. What are Window Functions?

Calculations across rows related to the current row without collapsing them. Use OVER() clause. Essential for ranking, running totals, and analytics.

SELECT name, department, salary,
 RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM employees;

17. ROW_NUMBER vs RANK vs DENSE_RANK?

ROW_NUMBER: Unique sequential numbers. No ties. 1, 2, 3, 4.

RANK: Same rank for ties, skips numbers. 1, 2, 2, 4.

DENSE_RANK: Same rank for ties, no gaps. 1, 2, 2, 3.

18. What is a CTE?

Common Table Expression � temporary named result set using WITH. Improves readability. Can be referenced multiple times. Supports recursion.

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

19. What is a correlated subquery?

References columns from the outer query. Executes once per outer row. Often slow � can usually rewrite with JOINs.

20. What is an Index?

Data structure that speeds up data retrieval. Like a book index. Types: B-tree, hash, composite. Trade-off: faster reads, slower writes.

CREATE INDEX idx_email ON students(email);

21. What is a Self Join?

Table joined with itself. Used for hierarchical data like employee-manager relationships.

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

22. What is the difference between WHERE and HAVING with GROUP BY?

WHERE filters rows before grouping. HAVING filters groups after aggregation. WHERE cannot use aggregate functions.

23. What is a Stored Procedure?

Precompiled SQL statements stored in database. Accepts parameters. Improves performance, security, reusability.

24. What is a Transaction?

Sequence of operations as single logical unit. ACID properties. BEGIN, COMMIT, ROLLBACK.

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
COMMIT;

25. What are ACID properties?

Atomicity: All or nothing. Consistency: Valid state. Isolation: Concurrent transactions independent. Durability: Committed changes persist.

26. What is Normalization?

Organizing tables to reduce redundancy. Normal forms: 1NF, 2NF, 3NF, BCNF. Goal: each fact stored once.

27. What is Denormalization?

Intentionally adding redundancy for performance. Avoids expensive JOINs. Common in data warehouses and read-heavy systems.

28. What is the difference between DELETE and DROP?

DELETE: Removes rows. Table structure remains. Can use WHERE.

DROP: Removes entire table structure and data. Cannot recover without backup.

29. What is a Trigger?

Special stored procedure that executes automatically on INSERT, UPDATE, DELETE events. Enforces business rules, audit trails.

30. What is the difference between IN and EXISTS?

IN: Compares against list. Subquery runs once. Good for small sets.

EXISTS: Returns TRUE on first match. Short-circuits. Better for large sets.

31. What is a Clustered Index?

Determines physical data order. One per table. Typically on PRIMARY KEY. Like dictionary alphabetical order.

32. What is a Non-Clustered Index?

Separate structure with indexed columns and pointers. Multiple per table. Like book index with page numbers.

33. What is a Execution Plan?

Shows how database executes a query. Displays operations, join types, index usage. Use EXPLAIN to view. Essential for optimization.

EXPLAIN SELECT * FROM employees WHERE department = 'Engineering';

34. What is the difference between ALL and ANY?

ALL: Compares value against all values in subquery. Returns TRUE if condition is true for all.

ANY: Returns TRUE if condition is true for at least one value.

35. What is COALESCE?

Returns the first non-NULL value from a list. Useful for handling missing data and providing defaults.

SELECT name, COALESCE(email, 'No email') as email FROM students;

Advanced SQL Questions (36�50)

36. What is LAG() and LEAD()?

LAG(): Accesses previous row. LEAD(): Accesses next row. Used for growth calculations and trend analysis.

SELECT month, revenue,
 LAG(revenue, 1) OVER (ORDER BY month) as prev_month,
 revenue - LAG(revenue, 1) OVER (ORDER BY month) as growth
FROM monthly_sales;

37. What is a Recursive CTE?

Self-referencing CTE for hierarchical data. Anchor member + recursive member. Traverses org charts, categories, graphs.

WITH RECURSIVE tree AS (
 SELECT id, name, manager_id, 1 as level FROM employees WHERE manager_id IS NULL
 UNION ALL
 SELECT e.id, e.name, e.manager_id, t.level + 1
 FROM employees e JOIN tree t ON e.manager_id = t.id
)
SELECT * FROM tree;

38. How to find the second highest salary?

SELECT DISTINCT salary FROM employees
ORDER BY salary DESC LIMIT 1 OFFSET 1;

-- Or with subquery
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

39. How to delete duplicate rows?

DELETE FROM students WHERE id IN (
 SELECT id FROM (
 SELECT id, ROW_NUMBER() OVER (PARTITION BY name, email ORDER BY id) as rn
 FROM students
 ) t WHERE rn > 1
);

40. What is a Pivot Table?

Transforms rows into columns. Uses CASE statements or PIVOT operator. Creates cross-tabulation reports.

SELECT department,
 SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) as male,
 SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) as female
FROM employees GROUP BY department;

41. What is a Gap-and-Island problem?

Finding consecutive sequences (islands) or missing sequences (gaps) in data. Solved with ROW_NUMBER() and date arithmetic.

42. What is query optimization?

Techniques: indexes, avoid SELECT *, EXPLAIN analysis, avoid correlated subqueries, LIMIT large results, partition tables, update statistics.

43. What is the difference between COUNT(*) and COUNT(column)?

COUNT(*): Counts all rows including NULLs.

COUNT(column): Counts only non-NULL values.

44. What is a Materialized View?

Stores query results physically on disk. Must be refreshed periodically. Used for expensive aggregations and reporting.

45. What is the difference between UNION and INTERSECT?

UNION: All rows from both queries. INTERSECT: Only rows in both. EXCEPT: Rows in first but not second.

46. What is a Window Function frame clause?

ROWS BETWEEN or RANGE BETWEEN defines which rows participate in calculation. ROWS uses physical offset, RANGE uses logical offset.

SELECT date, revenue,
 AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as moving_avg
FROM daily_sales;

47. What is the difference between RANK and PERCENT_RANK?

RANK: Position in ordered set. PERCENT_RANK: Relative position as percentage (0 to 1). Useful for percentile calculations.

48. What is the difference between FIRST_VALUE and NTH_VALUE?

FIRST_VALUE: Returns value from first row in window. NTH_VALUE: Returns value from nth row in window.

49. What is the difference between OVER(ORDER BY) and OVER(PARTITION BY ORDER BY)?

OVER(ORDER BY): Window function operates over entire result set.

PARTITION BY ORDER BY: Window function restarts for each partition.

50. How would you optimize this query?

Given: SELECT * FROM orders WHERE YEAR(order_date) = 2025 AND status = 'completed';

Optimization: (1) Avoid function on indexed column � use range instead. (2) Don't SELECT *. (3) Add composite index.

-- Optimized
SELECT order_id, customer_id, amount
FROM orders
WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01'
AND status = 'completed';

CREATE INDEX idx_date_status ON orders(order_date, status);

Practice Strategy

Consistent practice beats weekend cramming. Follow this schedule:

Week 1-2: Basics

• Master SELECT, WHERE, GROUP BY, ORDER BY
• Practice all JOIN types with 20+ queries
• Build queries on a practice database

Week 3-4: Intermediate

• Window functions: ROW_NUMBER, RANK, LAG, LEAD
• Subqueries and CTEs
• LeetCode easy and medium problems

Week 5-6: Advanced

• Recursive CTEs, complex window functions
• Query optimization and execution plans
• Hard problems on LeetCode and StrataScratch
• Mock interviews

External Resources

• LeetCode SQL Problems
• HackerRank SQL Practice
• StrataScratch
• PostgreSQL Documentation
• MySQL Documentation
• DB Tutorial (GitHub)

Key Takeaways