Top 100 SQL Interview Questions with Answers and Examples

Basic SQL Interview Questions

1. What is SQL?

2. SQL (Structured Query Language) is used to manage and manipulate relational databases.

3. Types of SQL statements?

4. DDL (CREATE, ALTER), DML (INSERT, UPDATE), DCL (GRANT, REVOKE), TCL (COMMIT, ROLLBACK)

5. Difference between DDL, DML, DCL?

6. DDL: Defines data structure.

7. DML: Manipulates data.

8. DCL: Controls access.

9. Primary Key?

10. Uniquely identifies each row in a table. Cannot be NULL.

11. Foreign Key?

12. Enforces referential integrity between tables.

13. Unique Key?

14. Ensures uniqueness but allows NULL.

15. Primary Key vs Unique Key?

16. Primary: One per table, no NULLs.

17. Unique: Multiple allowed, NULLs allowed.

18. NOT NULL constraint?

19. Ensures a column cannot have NULL values.

20. Default Constraint?

21. Sets default value if no value is provided.

22. Index?

1
23. Improves search performance.

24. View?

25. Virtual table based on SQL query.

26. Table?

27. Collection of rows and columns.

28. Schema?

29. Logical grouping of database objects.

30. Joins?

31. Combines rows from two or more tables.

32. Inner Join?

33. Returns matching rows.

34. Left Join?

35. All rows from left table, matching from right.

36. Right Join?

37. All rows from right table, matching from left.

38. Full Outer Join?

39. Returns all matching and non-matching rows.

40. Self Join?

41. Joins a table with itself.

42. Cross Join?

43. Returns Cartesian product.

44. Normalization?

45. Organizes data to reduce redundancy.

46. Denormalization?

47. Increases redundancy for read performance.

2
48. ACID Properties?

49. Atomicity, Consistency, Isolation, Durability.

50. Subquery?

51. Query inside another query.

52. WHERE vs HAVING?

53. WHERE: Filters rows before grouping.

54. HAVING: Filters groups after aggregation.

55. DELETE vs TRUNCATE vs DROP?

56. DELETE: Removes rows.

57. TRUNCATE: Removes all rows (fast).

58. DROP: Deletes the table.

59. CHAR vs VARCHAR?

60. CHAR: Fixed length.

61. VARCHAR: Variable length.

62. UNION vs UNION ALL?

63. UNION: Removes duplicates.

64. UNION ALL: Keeps duplicates.

65. Composite Key?

66. Combination of two or more columns as Primary Key.

67. Auto Increment?

68. Automatically generates unique numbers.

69. CASE Statement?

70. Conditional logic in SQL.

71. IS NULL vs = NULL?

72. Use IS NULL to check for NULLs.

3
73. LIKE Operator?

74. Pattern matching (% for any sequence, _ for single char).

75. GROUP BY?

76. Groups rows for aggregation.

77. ORDER BY?

78. Sorts result set.

79. Aggregate Functions?

80. COUNT, SUM, AVG, MIN, MAX.

81. COUNT(*) vs COUNT(column)?

82. COUNT(*) counts all rows.

83. COUNT(column) ignores NULLs.

84. Cursor?

85. Row-by-row processing.

86. Stored Procedure?

87. Precompiled SQL statements.

88. Trigger?

89. Automatically executes on events (INSERT, UPDATE).

90. Function?

91. Returns a single value.

92. Stored Procedure vs Function?

93. Procedure: No return needed.

94. Function: Must return value.

95. Handle Duplicates?

96. Use DISTINCT or ROW_NUMBER().

97. Transaction?

4
 98. Logical unit of work.

99. Rollback?

100. Undo transactions.

101. SQL Injection?

102. Security vulnerability in dynamic queries.

103. Foreign Key Violation?

104. Occurs when related data is missing.

105. Set Operators?

106. UNION, INTERSECT, MINUS.

107. BETWEEN vs IN?

108. BETWEEN: Range check.

109. IN: Matches specific values.

110. Materialized View?

111. Stored view with data.

Intermediate SQL Interview Questions

1. Second Highest Salary?

SELECT MAX(Salary) FROM Employee WHERE Salary < (SELECT MAX(Salary) FROM
Employee);

1. Find Duplicates?

SELECT Name, COUNT(*) FROM Employee GROUP BY Name HAVING COUNT(*) > 1;

1. Remove Duplicates?

DELETE FROM Employee WHERE id NOT IN (SELECT MIN(id) FROM Employee GROUP BY
Name);

1. COALESCE()?

5
 2. Returns first non-null value.

3. NVL()?

4. Oracle function similar to COALESCE.

5. RANK() vs DENSE_RANK() vs ROW_NUMBER()?

6. RANK(): Skips ranks.

7. DENSE_RANK(): No skips.

8. ROW_NUMBER(): Unique sequence.

9. PARTITION BY?

10. Divides result into partitions for aggregate/window functions.

11. Window Functions?

12. Functions like ROW_NUMBER() with OVER().

13. CTE?

WITH Temp AS (SELECT * FROM Employee) SELECT * FROM Temp;

1. Recursive CTE?

WITH Rec(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM Rec WHERE n < 5) SELECT *
FROM Rec;

1. EXISTS vs IN?

2. EXISTS: Stops at first match.

3. IN: Checks all values.

4. Optimize Query?

5. Use indexes, avoid SELECT *, use joins properly.

6. Execution Plan?

7. Shows how SQL engine executes query.

8. Index Fragmentation?

9. Occurs due to frequent DML. Use REBUILD.

6
10. Nth Highest Salary?

SELECT Salary FROM (SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC)
AS r FROM Employee) WHERE r = N;

1. NULL Handling?

2. Use COALESCE, IS NULL.

3. Correlated Subquery?

4. Subquery depends on outer query.

5. Update with Join?

UPDATE e SET [Link] = [Link] * 1.1 FROM Employee e JOIN Dept d ON

[Link] = [Link] WHERE [Link] = 'Sales';

1. PIVOT/UNPIVOT?

2. Converts rows to columns and vice versa.

3. Dynamic SQL?

4. SQL generated and executed at runtime.

5. Temporary Table vs Table Variable?

6. Temp Table: Stored in tempdb.

7. Table Variable: Stored in memory.

8. Bulk Insert?

9. Import large data quickly.

10. Check Table Size?

EXEC sp_spaceused 'Employee';

1. Last Record?

SELECT * FROM Employee ORDER BY ID DESC LIMIT 1;

1. First Non-Null Value?

7
SELECT COALESCE(col1, col2, col3) FROM Table;

1. Surrogate Key?

2. System-generated key.

3. Natural Join?

4. Join on columns with same name.

5. Check Constraint?

ALTER TABLE Employee ADD CONSTRAINT chk_salary CHECK (Salary > 0);

1. Composite Index?

CREATE INDEX idx_name_dept ON Employee(Name, Dept);

1. Data Integrity?

2. Accuracy and consistency of data.

3. Error Handling?

BEGIN TRY
-- SQL Code
END TRY
BEGIN CATCH
-- Handle Error
END CATCH

1. Sharding?

2. Splitting database horizontally.

3. Partitioning?

4. Divides tables into partitions.

5. Delete All Records?

DELETE FROM Employee;

1. OLTP vs OLAP?

8
 2. OLTP: Transactional.

3. OLAP: Analytical.

4. WITH TIES?

SELECT TOP 3 WITH TIES * FROM Employee ORDER BY Salary DESC;

1. MERGE Statement?

MERGE INTO Target USING Source ON [Link] = [Link] WHEN MATCHED THEN
UPDATE SET ... WHEN NOT MATCHED THEN INSERT ...;

1. Window Frames?

2. Specifies row range in window functions.

3. Logical vs Bitwise Operators?

4. Logical: AND, OR.

5. Bitwise: &, |.

6. Soft Delete?

7. Use a flag column (e.g., IsDeleted).

Advanced SQL Interview Questions

1. Database Replication?

2. Copying data across databases.

3. Deadlock?

4. Two transactions waiting on each other.

5. Resolve Deadlocks?

6. Use consistent order of locks, set timeouts.

7. Index Scan vs Table Scan?

8. Index scan: Uses index.

9. Table scan: Reads entire table.

9
 10. Concurrency?

11. Multiple users accessing data simultaneously.

12. Query Plan Caching?

13. SQL Server caches execution plans for reuse.

14. Database Hints?

15. Force optimizer behavior.

16. NOLOCK Hint?

17. Reads uncommitted data (dirty reads).

18. Row-Level Security?

19. Restricts row access for users.

20. Normalization Forms?

21. 1NF: Atomic columns.

22. 2NF: No partial dependency.

23. 3NF: No transitive dependency.
24. BCNF: Stronger version of 3NF.

Practice Queries Summary

• CRUD Operations: INSERT , UPDATE , DELETE , SELECT .

• Joins: INNER JOIN , LEFT JOIN , RIGHT JOIN , FULL JOIN .
• Aggregation: SUM() , AVG() , COUNT() , GROUP BY .
• Ranking: ROW_NUMBER() , RANK() , DENSE_RANK() .
• CTE & Recursive Queries: WITH clause.
• Real-world Scenarios: Employee salary, Product sales, Customer orders.

This document can be used for quick revision before interviews or hands-on practice with SQL.

10