SQL Interview Questions and Answers — 50+ Questions to Crack Your Interview

Why SQL Interview Preparation Matters

SQL is the single most tested skill in data-related interviews. According to a 2026 LinkedIn survey, 92% of Data Analyst, Data Engineer, and Data Scientist job postings require SQL proficiency. Whether you are interviewing at a startup in Lucknow or a tech giant in Bangalore, SQL questions are guaranteed.

The challenge is not just knowing SQL syntax — it is understanding how to think in SQL, optimize queries, and solve complex data problems under time pressure. This guide covers 50+ questions organized by difficulty level, with detailed explanations, code examples, and tips from real interview experiences.

At DSWallah, our students practice these exact questions as part of interview preparation. Our SQL curriculum covers everything from basic queries to advanced window functions and optimization techniques.

Basic SQL Questions (1—15)

1. What is the difference between WHERE and HAVING?

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

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

In this query, WHERE age > 18 filters individual students first, then HAVING COUNT(*) > 5 keeps only cities with more than 5 students after grouping.

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

DELETE: Removes specific rows using WHERE clause. Logged operation, can be rolled back. Triggers fire. Slower for large tables.

TRUNCATE: Removes all rows. Cannot use WHERE. Minimal logging, faster than DELETE. Cannot rollback in most databases. Resets identity counter.

DROP: Removes the entire table structure and data. Cannot be rolled back without backup. Deletes table from database entirely.

DELETE FROM students WHERE age < 18; -- Remove specific rows
TRUNCATE TABLE students; -- Remove all rows
DROP TABLE students; -- Remove table entirely

3. What is a PRIMARY KEY?

A PRIMARY KEY is a column (or combination of columns) that uniquely identifies each row in a table. It cannot contain NULL values and must contain unique values. A table can have only one PRIMARY KEY, but it can span multiple columns (composite primary key).

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

4. What is the difference between PRIMARY KEY and UNIQUE KEY?

PRIMARY KEY: Cannot be NULL, only one per table, automatically creates a clustered index.

UNIQUE KEY: Can accept one NULL value (in most databases), multiple UNIQUE constraints per table, creates a non-clustered index.

5. What is a FOREIGN KEY?

A FOREIGN KEY is a column that creates a link between two tables. It references the PRIMARY KEY of another table, ensuring referential integrity. Foreign keys prevent inserting rows that reference non-existent records in the parent table.

CREATE TABLE enrollments (
 id INT PRIMARY KEY,
 student_id INT,
 course_id INT,
 FOREIGN KEY (student_id) REFERENCES students(id),
 FOREIGN KEY (course_id) REFERENCES courses(id)
);

6. What are the different types of JOINs?

INNER JOIN: Returns rows with matching values in both tables.

LEFT JOIN: Returns all rows from the left table and matching rows from the right. NULLs for non-matching right rows.

RIGHT JOIN: Returns all rows from the right table and matching rows from the left. NULLs for non-matching left rows.

FULL OUTER JOIN: Returns all rows from both tables. NULLs where there is no match.

CROSS JOIN: Returns the Cartesian product — every row from the first table paired with every row from the second.

SELF JOIN: A table joined with itself. Useful for hierarchical data like employee-manager relationships.

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

INNER JOIN returns only rows that have matching records in both tables. LEFT JOIN returns all rows from the left table and the matched rows from the right table. If there is no match in the right table, LEFT JOIN returns NULL for those columns.

-- Students with their enrollments (only enrolled students)
SELECT s.name, e.course_id
FROM students s
INNER JOIN enrollments e ON s.id = e.student_id;

-- All students, even those not enrolled
SELECT s.name, e.course_id
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id;

8. What is a subquery?

A subquery is a query nested inside another query. It can appear in SELECT, FROM, WHERE, or HAVING clauses. Subqueries in WHERE are called inner queries and can use operators like IN, EXISTS, ANY, ALL.

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

9. What is the difference between IN and EXISTS?

IN: Compares a value against a list of values. Good for small subquery results. The subquery runs once.

EXISTS: Returns TRUE if the subquery returns any rows. Better for large datasets because it stops as soon as a match is found. The outer query drives the execution.

10. What is a NULL value?

NULL represents missing or unknown data. It is not the same as zero or an empty string. NULL comparisons use IS NULL or IS NOT NULL — you cannot use = or != with NULL. Aggregate functions ignore NULLs except COUNT(*).

SELECT * FROM students WHERE email IS NULL;
SELECT * FROM students WHERE email IS NOT NULL;

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

UNION: Combines results from two queries and removes duplicate rows. Slower due to duplicate elimination.

UNION ALL: Combines results and keeps all rows including duplicates. Faster than UNION.

Use UNION ALL when you know there are no duplicates or when duplicates are acceptable — it is significantly faster.

12. What are aggregate functions?

Aggregate functions perform calculations on a set of rows and return a single value. Common aggregate functions include COUNT(), SUM(), AVG(), MAX(), MIN(). They are used with GROUP BY to aggregate data by categories.

SELECT department, COUNT(*) as headcount, AVG(salary) as avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

13. What is GROUP BY?

GROUP BY groups rows that have the same values in specified columns into summary rows. It is used with aggregate functions to perform calculations per group. Every column in SELECT that is not an aggregate must be in GROUP BY.

14. What is the difference between WHERE and WHERE 1=1?

Functionally identical in results. WHERE 1=1 is a technique used in dynamic SQL to simplify adding conditions with AND. Since 1=1 is always true, it does not filter any rows but allows you to append conditions without worrying about the first AND.

15. What is the ORDER BY clause?

ORDER BY sorts the result set by one or more columns. ASC (ascending) is the default. DESC sorts in descending order. You can order by column name, alias, column position, or expressions.

SELECT name, salary FROM employees
ORDER BY salary DESC, name ASC;

Intermediate SQL Questions (16—35)

16. What is a Window Function?

Window functions perform calculations across a set of rows related to the current row without collapsing them (unlike GROUP BY). They use the OVER() clause to define the window frame. Essential for ranking, running totals, moving averages, and complex analytics.

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

17. What is the difference between ROW_NUMBER(), RANK(), and DENSE_RANK()?

ROW_NUMBER(): Assigns unique sequential numbers. No ties — always 1, 2, 3, 4.

RANK(): Assigns same rank for ties, then skips numbers. If two rows tie for rank 2, next row is rank 4.

DENSE_RANK(): Assigns same rank for ties, no gaps. If two rows tie for rank 2, next row is rank 3.

SELECT name, salary,
 ROW_NUMBER() OVER (ORDER BY salary DESC) as row_num,
 RANK() OVER (ORDER BY salary DESC) as rank_num,
 DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank_num
FROM employees;

18. What is a CTE (Common Table Expression)?

A CTE is a temporary named result set defined with WITH. It improves readability of complex queries and can be referenced multiple times. CTEs can be recursive, making them useful for hierarchical data traversal.

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

19. What is the difference between DELETE and TRUNCATE?

DELETE: DML operation, logged, can rollback, fires triggers, slower, can use WHERE.

TRUNCATE: DDL operation, minimal logging, cannot rollback (in most databases), resets identity, faster, cannot use WHERE.

20. What is a Self Join?

A Self Join joins a table with itself. Used when comparing rows within the same table, such as finding employee-manager relationships or comparing consecutive records.

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

21. What is a Correlated Subquery?

A correlated subquery references columns from the outer query. It executes once for each row in the outer query, making it potentially slow. Often can be rewritten with JOINs or window functions for better performance.

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

22. What is an Index?

An index is a data structure that improves the speed of data retrieval operations. It works like a book index — instead of scanning every page, the database uses the index to jump directly to the relevant rows. Common types are B-tree, hash, and composite indexes.

CREATE INDEX idx_email ON students(email);
CREATE INDEX idx_dept_salary ON employees(department, salary);

23. What is the difference between WHERE and ON?

WHERE: Filters rows after joins are performed.

ON: Defines the join condition between tables. In LEFT JOINs, filtering in ON vs WHERE produces different results — ON preserves non-matching rows from the left table.

24. What is a View?

A View is a stored query that acts as a virtual table. It does not store data itself but retrieves data from underlying tables when queried. Views simplify complex queries, provide security by restricting column access, and maintain consistency.

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

25. What is a Stored Procedure?

A Stored Procedure is a precompiled collection of SQL statements stored in the database. It can accept parameters, perform operations, and return results. Procedures improve performance (compiled once, executed many times), security, and code reusability.

26. What is a Transaction?

A Transaction is a sequence of operations performed as a single logical unit. It follows ACID properties: Atomicity (all or nothing), Consistency (valid state), Isolation (concurrent transactions don't interfere), Durability (committed changes persist).

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

27. What are the ACID properties?

Atomicity: All operations in a transaction succeed or all fail.

Consistency: A transaction brings the database from one valid state to another.

Isolation: Concurrent transactions do not interfere with each other.

Durability: Once committed, changes survive system failures.

28. What is Normalization?

Normalization is the process of organizing database tables to reduce redundancy and improve data integrity. Normal forms (1NF, 2NF, 3NF, BCNF) define progressively stricter rules. The goal is to ensure each fact is stored in only one place.

29. What is Denormalization?

Denormalization intentionally introduces redundancy to improve query performance. By storing pre-computed values or duplicating data, you avoid expensive JOINs. Common in data warehouses and read-heavy applications where query speed matters more than storage efficiency.

30. What is the difference between UNION and INTERSECT?

UNION: Combines results from two queries, removing duplicates.

INTERSECT: Returns only rows that appear in both query results.

EXCEPT: Returns rows from the first query that are not in the second.

31. What is a Trigger?

A Trigger is a special type of stored procedure that automatically executes in response to events (INSERT, UPDATE, DELETE) on a table. Triggers enforce business rules, maintain audit trails, and validate data. Use sparingly as they can impact performance.

32. What is the difference between CHAR and VARCHAR?

CHAR: Fixed-length string. Always stores the specified number of characters, padding with spaces if shorter. Faster for fixed-size data.

VARCHAR: Variable-length string. Stores only the actual characters plus a length prefix. More efficient for varying-length data.

33. What is a Clustered Index?

A Clustered Index determines the physical order of data in a table. The table data is sorted according to the clustered index key. A table can have only one clustered index, typically on the PRIMARY KEY. It is like a dictionary where words are sorted alphabetically.

34. What is a Non-Clustered Index?

A Non-Clustered Index creates a separate structure that contains the indexed columns and a pointer to the actual data row. A table can have multiple non-clustered indexes (up to 999 in SQL Server). It is like an index at the back of a book pointing to page numbers.

35. What is Query Execution Plan?

A Query Execution Plan shows how the database engine will execute a query. It displays the order of operations, join types, index usage, and estimated rows. Use EXPLAIN (MySQL) or EXPLAIN ANALYZE (PostgreSQL) to view it. Essential for optimizing slow queries.

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

Advanced SQL Questions (36—50+)

36. What are Window Functions with frame clauses?

Window functions can specify a frame clause to define which rows participate in the calculation. ROWS BETWEEN defines a physical offset, while RANGE BETWEEN uses logical offsets.

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

37. What is the LAG() and LEAD() function?

LAG(): Accesses data from a previous row in the result set.

LEAD(): Accesses data from a next row in the result set.

Used for comparing current values with previous or next values — growth calculations, gap detection, 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;

38. What is a Recursive CTE?

A Recursive CTE references itself to traverse hierarchical data like org charts, product categories, or graph structures. It has an anchor member (base case) and a recursive member that calls itself until no more rows are returned.

WITH RECURSIVE org_chart 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, oc.level + 1
 FROM employees e
 JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart;

39. How do you find the second highest salary?

Multiple approaches exist. The most common are using LIMIT/OFFSET, subqueries, or window functions.

-- Method 1: LIMIT/OFFSET
SELECT DISTINCT salary FROM employees
ORDER BY salary DESC LIMIT 1 OFFSET 1;

-- Method 2: Subquery
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- Method 3: Window function
SELECT salary FROM (
 SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rnk
 FROM employees
) ranked WHERE rnk = 2;

40. How do you delete duplicate rows?

Use window functions to identify duplicates and keep only the first occurrence.

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
);

41. What is a Pivot Table in SQL?

Pivot transforms rows into columns. It aggregates data and rotates it so that values from one column become new column headers. Useful for cross-tabulation reports.

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

42. What is query optimization?

Key optimization techniques include creating indexes on frequently queried columns, avoiding SELECT *, using EXPLAIN to analyze query plans, avoiding correlated subqueries (use JOINs instead), using LIMIT for large result sets, partitioning large tables, and denormalizing for read-heavy workloads.

43. What is the difference between EXISTS and IN?

IN: Compares each value from the outer query against the list returned by the subquery. Best when the subquery returns a small, fixed set.

EXISTS: Returns TRUE as soon as the subquery finds any matching row. Better for large subquery results because it short-circuits. The outer query drives the execution.

44. What is a Materialized View?

A Materialized View stores the result of a query physically on disk. Unlike a regular view, it does not re-execute the query each time. It must be refreshed periodically to stay current. Used for expensive aggregations and reporting queries.

45. What is the difference between ROW_NUMBER() and LIMIT?

LIMIT: Restricts the number of rows returned by the query. Does not provide ranking or ordering context.

ROW_NUMBER(): Assigns a unique number to each row within a partition. Allows you to select specific rows based on rank within groups.

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

Gap-and-Island problems involve finding consecutive sequences (islands) or missing sequences (gaps) in data. Common in time-series analysis. Solved using ROW_NUMBER(), LAG(), and date arithmetic.

SELECT MIN(date) as gap_start, MAX(date) as gap_end
FROM (
 SELECT date, date - ROW_NUMBER() OVER (ORDER BY date) as grp
 FROM dates_with_gaps
) t GROUP BY grp HAVING COUNT(*) > 1;

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

COUNT(*): Counts all rows including those with NULL values.

COUNT(column): Counts only non-NULL values in that specific column. If all values are NULL, it returns 0.

48. What are Window Functions with PARTITION BY?

PARTITION BY divides the result set into partitions for the window function to operate on independently. Similar to GROUP BY but does not collapse rows — each row retains its identity.

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

49. What is the difference between TRUNCATE and DELETE?

DELETE: DML operation, logged row-by-row, can use WHERE, fires triggers, can be rolled back, slower for large tables.

TRUNCATE: DDL operation, minimal logging, removes all rows, resets identity counter, cannot be rolled back in most databases, much faster.

50. How do you optimize a slow SQL query?

Follow these steps: (1) Use EXPLAIN to understand the execution plan. (2) Add indexes on columns used in WHERE, JOIN, and ORDER BY. (3) Avoid SELECT * — only select needed columns. (4) Replace correlated subqueries with JOINs or CTEs. (5) Use appropriate join types. (6) Consider partitioning for large tables. (7) Update table statistics. (8) Avoid functions on indexed columns in WHERE clauses.

How to Prepare for SQL Interviews

SQL interview preparation requires consistent practice. Here is a structured approach:

Week 1-2: Fundamentals

• Master SELECT, WHERE, GROUP BY, HAVING, ORDER BY
• Practice all types of JOINs with real examples
• Understand NULL behavior and aggregate functions
• Build 10+ queries on a practice database

Week 3-4: Intermediate

• Learn window functions: ROW_NUMBER, RANK, LAG, LEAD
• Master subqueries and CTEs
• Practice on LeetCode SQL problems (easy and medium)
• Understand indexes and basic optimization

Week 5-6: Advanced

• Recursive CTEs and complex window functions
• Query optimization and execution plans
• Practice hard LeetCode and StrataScratch problems
• Mock interviews with peers or mentors

External Resources for Practice

• LeetCode SQL Problems — 50+ SQL problems from easy to hard
• HackerRank SQL — Practice SQL by difficulty
• StrataScratch — Real interview questions from companies
• W3Schools SQL — Reference and tutorials
• MySQL Documentation — Official docs
• PostgreSQL Documentation — Official docs

Key Takeaways