SQL Interview Questions (Top 100 Questions with Answers)

Master SQL Interview Questions with 100 production-oriented questions covering SQL fundamentals, joins, indexing, transactions, normalization, optimization, stored procedures, triggers, window functions, and real-world interview scenarios.

Introduction

SQL is one of the most important skills for

Java Developers
Backend Engineers
Full Stack Developers
Data Engineers
Database Administrators
Solution Architects

Almost every interview contains SQL questions ranging from basic SELECT statements to advanced performance tuning and database design.

This guide contains 100 production-oriented SQL interview questions frequently asked by companies such as

Amazon
Microsoft
Google
IBM
Oracle
Goldman Sachs
JPMorgan Chase
Walmart
Infosys
Accenture

SQL Interview Roadmap

SQL Basics
      │
      ▼
Filtering
      │
      ▼
Joins
      │
      ▼
Aggregation
      │
      ▼
Subqueries
      │
      ▼
CTE
      │
      ▼
Window Functions
      │
      ▼
Indexes
      │
      ▼
Transactions
      │
      ▼
Performance Tuning
      │
      ▼
Production Scenarios

SQL Fundamentals

1. What is SQL?

SQL (Structured Query Language) is the standard language used to store, retrieve, update, and manage relational databases.

2. What are the different SQL commands?

3. Difference between DELETE, TRUNCATE and DROP?

DELETE	TRUNCATE	DROP
Removes Rows	Removes All Rows	Removes Entire Table
Rollback Possible	Usually No	No
Structure Exists	Structure Exists	Structure Deleted

4. Difference between CHAR and VARCHAR?

CHAR	VARCHAR
Fixed Length	Variable Length
Faster	Saves Space
Wastes Memory	Efficient

5. Primary Key vs Unique Key?

Primary Key	Unique Key
One Per Table	Multiple Allowed
No NULL	Usually Allows NULL (DB dependent)
Entity Identifier	Alternate Identifier

6. What is a Foreign Key?

A Foreign Key creates a relationship between two tables and enforces referential integrity.

7. What are Constraints?

Primary Key
Foreign Key
Unique
Check
Not Null
Default

8. What is Normalization?

Normalization reduces redundancy and improves consistency.

9. What is Denormalization?

Adding controlled redundancy to improve read performance.

10. Explain ACID Properties.

Atomicity
Consistency
Isolation
Durability

SQL Queries

11. Difference between WHERE and HAVING?

WHERE	HAVING
Before GROUP BY	After GROUP BY
Filters Rows	Filters Groups

12. Difference between GROUP BY and ORDER BY?

GROUP BY creates groups.

ORDER BY sorts results.

13. Difference between DISTINCT and GROUP BY?

DISTINCT removes duplicates.

GROUP BY groups rows for aggregation.

14. Difference between COUNT(*) and COUNT(column)?

COUNT(*)

Counts all rows.

COUNT(column)

Ignores NULL values.

15. Difference between UNION and UNION ALL?

UNION	UNION ALL
Removes Duplicates	Keeps Duplicates
Slower	Faster

16. Explain CASE Statement.

Conditional logic inside SQL queries.

17. Difference between EXISTS and IN?

EXISTS stops after finding the first match and is often more efficient for correlated subqueries.

IN compares against a list or subquery.

18. What is a Correlated Subquery?

A subquery that depends on values from the outer query.

19. What is CTE?

Common Table Expression improves readability and supports recursion.

20. Recursive CTE use case?

Organization hierarchy
Folder structure
Bill of Materials

SQL Joins

21. Types of SQL Joins?

INNER
LEFT
RIGHT
FULL
CROSS
SELF

22. INNER JOIN vs LEFT JOIN?

INNER JOIN returns matching rows.

LEFT JOIN returns all left table rows.

23. FULL OUTER JOIN?

Returns all rows from both tables.

24. CROSS JOIN?

Returns Cartesian Product.

25. SELF JOIN?

Joins a table with itself.

Used for Employee-Manager relationships.

26. Which Join is fastest?

Depends on

Indexes
Execution Plan
Data Size

27. How do you optimize JOINs?

Join indexed columns
Filter early
Avoid unnecessary joins
Review execution plans

28. What causes duplicate rows after JOIN?

Incorrect join conditions
One-to-many relationships
Missing predicates

29. Difference between ON and WHERE?

ON defines join conditions.

WHERE filters the result after the join.

30. How do you find unmatched rows?

Using LEFT JOIN with IS NULL.

Window Functions

31. ROW_NUMBER()

Assigns unique numbers.

32. RANK()

Leaves gaps for ties.

33. DENSE_RANK()

No gaps after ties.

34. LEAD()

Reads next row.

35. LAG()

Reads previous row.

36. FIRST_VALUE()

Returns first value.

37. LAST_VALUE()

Returns last value in the current window frame.

38. NTILE()

Splits rows into buckets.

39. Running Total?

Using

SUM() OVER(...)

40. Moving Average?

Using

AVG() OVER(...)

Indexes

41. What is an Index?

Improves query performance.

42. Clustered vs Non-Clustered Index?

Clustered stores rows in index order.

Non-clustered stores separate index structure.

43. Composite Index?

Multiple columns.

44. Covering Index?

Contains all required query columns.

45. Why avoid too many indexes?

Slows INSERT, UPDATE and DELETE operations.

46. What is Index Selectivity?

Measures how unique indexed values are.

Higher selectivity usually results in better index performance.

47. What is a Full Table Scan?

Reading every row in a table.

Usually slower than index lookup.

48. When will indexes not be used?

Functions on indexed columns
Leading wildcard searches
Very low selectivity
Type mismatches

49. What is an Execution Plan?

Shows how the database executes a query.

50. How do you optimize slow queries?

Review execution plan
Add indexes
Rewrite SQL
Reduce returned data

Transactions

51. What is a Transaction?

Logical unit of work.

52. COMMIT vs ROLLBACK?

COMMIT saves.

ROLLBACK undoes changes.

53. SAVEPOINT?

Partial rollback marker.

54. Isolation Levels?

Read Uncommitted
Read Committed
Repeatable Read
Serializable

55. Dirty Read?

Reading uncommitted data.

56. Phantom Read?

New rows appear during the same transaction.

57. Deadlock?

Transactions waiting on each other indefinitely.

58. Optimistic Locking?

Uses version numbers.

59. Pessimistic Locking?

Acquires database locks before modification.

60. MVCC?

Multiple row versions improve concurrency.

Stored Programs

61. What is a Stored Procedure?

Precompiled SQL program.

62. Procedure vs Function?

Functions return values.

Procedures perform operations.

63. Trigger?

Automatically executes on database events.

64. BEFORE Trigger?

Runs before INSERT, UPDATE or DELETE.

65. AFTER Trigger?

Runs after database operation.

66. Views?

Virtual tables.

67. Materialized View?

Stores physical data.

68. Sequences?

Generate unique numbers.

69. Packages (Oracle)?

Group procedures, functions and variables.

70. Dynamic SQL?

SQL built during runtime.

Production Questions

71. Why is SELECT * discouraged?

Returns unnecessary columns and increases network traffic.

72. Why should transactions be short?

Reduces blocking and deadlocks.

73. Why use Prepared Statements?

Prevents SQL Injection.

74. What causes slow queries?

Missing indexes
Poor joins
Full table scans
Large datasets

75. How do you analyze slow queries?

Using

EXPLAIN
Execution Plans
Slow Query Logs

76. Why is pagination important?

Improves response time for large datasets.

77. OFFSET vs Keyset Pagination?

Keyset pagination is generally more efficient for large datasets.

78. What is connection pooling?

Reusing database connections to improve performance.

79. What is database partitioning?

Splitting large tables into smaller partitions.

80. What is sharding?

Distributing data across multiple databases.

Scenario-Based Questions

81. Design a Banking Database.

Discuss

Accounts
Transactions
Customers
Audit
ACID

82. Optimize a 10-million-row table.

Discuss

Indexing
Partitioning
Execution Plan

83. Handle Deadlocks.

Retry
Lock Ordering
Short Transactions

84. Design Inventory Management.

Focus

Stock
Orders
Warehouse
Transactions

85. Handle High Traffic.

Use

Cache
Replication
Read Replicas

86. Design Audit Logging.

Triggers

Application Logging.

87. Prevent Duplicate Orders.

Use

Constraints
Unique Keys
Idempotency

88. Archive Historical Data.

Partitioning

Archival Tables.

89. Design Employee Hierarchy.

Recursive CTE.

90. Scale Database.

Replication
Sharding
Caching

Senior-Level Questions

91. Explain CAP Theorem.

Consistency

Availability

Partition Tolerance

92. SQL vs NoSQL?

Use cases

and trade-offs.

93. Explain Eventual Consistency.

Common in distributed databases.

94. How would you migrate a large database?

Plan

Backup
Validation
Incremental Migration
Rollback

95. How do you troubleshoot production database issues?

Slow Queries
Locks
Deadlocks
CPU
I/O
Execution Plans

96. Explain Read Replica.

Replica used for read-only workloads.

97. Explain High Availability.

Failover

Replication

Monitoring.

98. Explain Disaster Recovery.

Backup

Replication

Recovery Plan

99. Database Monitoring Tools?

AWR
pg_stat_statements
MySQL Performance Schema
Grafana
Prometheus

100. Most important SQL optimization techniques?

Indexing
Execution Plans
Query Rewriting
Partitioning
Caching
Connection Pooling

Enterprise SQL Interview Workflow

Requirement
      │
      ▼
Write Query
      │
      ▼
Review Execution Plan
      │
      ▼
Optimize Indexes
      │
      ▼
Test Performance
      │
      ▼
Deploy
      │
      ▼
Monitor

Quick Revision

Area	Focus
SQL Basics	DDL, DML, DCL, TCL
Queries	SELECT, WHERE, GROUP BY
Joins	INNER, LEFT, RIGHT, FULL
Window Functions	ROW_NUMBER, RANK, LAG
Indexes	Clustered, Composite
Transactions	ACID, COMMIT, ROLLBACK
Locking	Shared, Exclusive, MVCC
Performance	Execution Plans
Design	Normalization
Production	Optimization

Interview Tips

For SQL interviews:

Explain your thought process before writing queries.
Optimize for readability first, then performance.
Mention indexes when discussing optimization.
Discuss transaction handling for data modifications.
Always consider edge cases such as NULL values and duplicate records.
Be comfortable reading execution plans.
Practice writing SQL without IDE assistance.
Understand both SQL syntax and database internals.

Summary

SQL remains one of the most important skills for backend and database professionals. Strong interview performance requires more than memorizing syntax—it requires understanding query optimization, indexing, transactions, locking, execution plans, normalization, and production troubleshooting.

Mastering these 100 SQL interview questions provides a solid foundation for interviews ranging from Junior Developer to Senior Engineer, Database Administrator, and Solution Architect roles.