Database Interview Questions

1. What is Normalization in Database?

2. What are ACID Properties in DBMS?

3. Difference Between SQL and NoSQL Databases

4. What is an Index in a Database?

5. What is a JOIN in SQL?

6. Difference Between Primary Key and Foreign Key

7. Difference Between DELETE, TRUNCATE and DROP

8. What is a View in SQL?

9. What is a Transaction in a Database?

10. What is Database Sharding?

11. Clustered vs Non-Clustered Index: What is the Difference?

12. What is a Stored Procedure in SQL?

13. What is Database Replication?

14. What is a Deadlock in Database?

15. What is Database Partitioning?

16. Optimistic vs Pessimistic Locking: What is the Difference?

17. What is a Trigger in SQL?

18. What is Denormalization in Database?

19. Inner Join vs Outer Join

20. What is a Composite Key?

21. HAVING vs WHERE Clause

22. What is a Cursor in SQL?

23. CHAR vs VARCHAR

24. What is CAP Theorem?

25. What is Connection Pooling?

26. What is the Difference Between GROUP BY and ORDER BY?

27. What is a Materialized View?

28. What is Referential Integrity?

29. How Does a B-Tree Index Work Internally?

30. Hash Index vs B-Tree Index: What is the Difference?

31. What is a Covering Index in SQL?

32. What is a Partial Index and When Should You Use One?

33. What Are Index Selectivity and Cardinality?

34. What is the Read Uncommitted Isolation Level?

35. What is the Read Committed Isolation Level?

36. What is the Repeatable Read Isolation Level?

37. What is the Serializable Isolation Level?

38. What is a Phantom Read in Database Transactions?

39. Dirty Read vs Non-Repeatable Read: What is the Difference?

40. What is MVCC (Multi-Version Concurrency Control)?

41. What is the Write Skew Anomaly?

42. What is the Lost Update Problem in Databases?

43. What is the Two-Phase Locking (2PL) Protocol?

44. What is a Query Execution Plan?

45. What Does EXPLAIN ANALYZE Do?

46. Cost-Based vs Rule-Based Query Optimization: What is the Difference?

47. Index Scan vs Sequential Scan: When Does the Optimizer Choose Each?

48. How Does the Nested Loop Join Algorithm Work?

49. How Does the Hash Join Algorithm Work?

50. How Does the Merge Join (Sort-Merge Join) Algorithm Work?

51. What is Query Plan Caching in a Database?

52. What is the Parameter Sniffing Problem in SQL?

53. What Role Do Statistics and Histograms Play in Query Planning?

54. What are Window Functions in SQL?

55. ROW_NUMBER vs RANK vs DENSE_RANK: What is the Difference?

56. What Does PARTITION BY Do in SQL?

57. What are LEAD and LAG Functions in SQL?

58. How Do You Calculate a Running Total with Window Functions?

59. What is a Common Table Expression (CTE) in SQL?

60. What is a Recursive CTE and When Would You Use One?

61. CTE vs Subquery: Which Performs Better?

62. CTE vs Temporary Table: What is the Difference?

63. What Does It Mean for a CTE to be Materialized?

64. UNION vs UNION ALL: What is the Difference?

65. What are the INTERSECT and EXCEPT Operators in SQL?

66. What is a Self-Join and When Would You Use One?

67. What is a CROSS JOIN and When is it Useful?

68. What is the Difference Between a Semi-Join and an Anti-Join?

69. What is the Document Database Model?

70. What is a Column-Family Database Model?

71. What is the Graph Database Model?

72. What is a Key-Value Store and When Should You Use One?

73. Wide-Column Store vs Relational Database: What is the Difference?

74. How Do You Design Document Schemas in MongoDB?

75. What Are the Basics of Cassandra Data Modeling?

76. What Data Structures Does Redis Support and When to Use Them?

77. What Are the Basics of Graph Queries in Neo4j?

78. How Do You Design a Good Partition Key in DynamoDB?

79. How Do You Choose a Sharding Key?

80. What is Range-Based Sharding?

81. What is Hash-Based Sharding?

82. What is Consistent Hashing and Why Do Databases Use It?

83. What Challenges Arise When Resharding and Rebalancing a Database?

84. What is a Master-Slave Replication Topology?

85. What is a Master-Master Replication Topology?

86. Synchronous vs Asynchronous Replication: What is the Difference?

87. What Causes Replication Lag and How Do You Reduce It?

88. What is Quorum-Based Replication for Reads and Writes?

89. Full vs Incremental Database Backups: What is the Difference?

90. What is Point-in-Time Recovery (PITR)?

91. Why is Restore Testing Important for Database Backups?

92. What are RPO and RTO in Database Disaster Recovery?

93. Hot Backup vs Cold Backup: What is the Difference?

94. What is a Write-Ahead Log (WAL) and Why Do Databases Use One?

95. How Does the Write-Ahead Log Enable Crash Recovery?

96. What is Checkpointing in a Database and Why is it Needed?

97. Redo Log vs Undo Log: What is the Difference?

98. What is Transaction Log Shipping and How is it Used?

99. What is the VACUUM Process in PostgreSQL?

100. How Do You Tune Autovacuum in PostgreSQL?

101. What Causes Table Bloat and How Do You Fix It?

102. How Does PostgreSQL Clean Up Dead Tuples?

103. What is Index Bloat and When Should You Reindex?

104. What is a Reliable Methodology for Tuning a Slow SQL Query?

105. How Do You Analyze a Slow Query Log to Find Optimization Targets?

106. What is the N+1 Query Problem and How Do You Fix It?

107. ORM Lazy Loading vs Eager Loading: What is the Difference?

108. What Query Batching Strategies Reduce Database Round Trips?

109. How Do You Tune a Database Connection Pool?

110. How Do Prepared Statements Improve Database Performance?

111. What Techniques Prevent SQL Injection?

112. What Are Parameterized Queries and Why Do They Matter?

113. Stored Procedures vs ORM: What Are the Trade-offs?

114. How Do You Choose Numeric Data Type Precision in a Database?

115. DECIMAL vs FLOAT: How Do They Store Numbers Differently?

116. How Should Databases Handle Dates, Times, and Time Zones?

117. When Should You Use a JSON Column Type in a Relational Database?

118. ENUM Type vs Lookup Table: Which Should You Use?

119. What Is a CHECK Constraint and When Would You Use One?

120. What Is the Difference Between a UNIQUE Constraint and a UNIQUE Index?

121. How Should You Decide Which Columns Get a NOT NULL Constraint?

122. How Does a DEFAULT Value Constraint Behave in SQL?

123. What Do ON DELETE CASCADE, SET NULL, and RESTRICT Mean for Foreign Keys?

124. What Are 1NF, 2NF, and 3NF in Schema Design?

125. What is Boyce-Codd Normal Form (BCNF)?

126. Star Schema vs Snowflake Schema: What is the Difference?

127. What Are the Basics of Entity-Relationship (ER) Modeling?

128. What Are Fact and Dimension Tables in a Data Warehouse?

129. OLAP vs OLTP: What is the Difference?

130. Columnar Storage vs Row Storage: What is the Difference?

131. Data Lake vs Data Warehouse: What is the Difference?

132. ETL vs ELT Pipelines: What is the Difference?

133. What is Change Data Capture (CDC)?

134. What Are Time-Series Databases and When Should You Use One?

135. What Are TimescaleDB Hypertables and How Do They Work?

136. How Does the InfluxDB Data Model Work?

137. What Is Downsampling in Time-Series Databases?

138. What Are Retention Policies in Time-Series Databases?

139. What is the Cache-Aside (Lazy-Loading) Caching Pattern?

140. Write-Through vs Write-Back Cache: What is the Difference?

141. What are the Main Cache Invalidation Strategies?

142. Redis as a Cache vs a Primary Data Store: What Changes?

143. What are the Trade-offs of Caching Database Query Results?

144. How Do You Design a Zero-Downtime Database Migration?

145. What Are Schema Versioning Tools and Why Use Them?

146. How Does Blue-Green Deployment Apply to Databases?

147. What Makes a Database Schema Change Backward-Compatible?

148. What Are Online Schema Change Tools and How Do They Work?

149. What is Two-Phase Commit for Distributed Transactions?

150. What is the Saga Pattern in Microservices?

151. What is Eventual Consistency in Databases?

152. Strong Consistency vs Eventual Consistency: What is the Difference?

153. What are Raft and Paxos? Consensus Algorithms Explained

154. How Do You Tune Database Connection Timeouts?

155. How Do Databases Detect Deadlocks?

156. What is Lock Escalation in Databases?

157. Row-Level Locking vs Table-Level Locking: What is the Difference?

158. What are Advisory Locks and When Should You Use Them?

159. How Does Full-Text Search Work in Relational Databases?

160. Elasticsearch vs Built-In Database Full-Text Search: When to Use Which?

161. How Does Geospatial Indexing Work for GIS Queries?

162. What Are Spatial Data Types and How Are They Used in SQL?

163. How Do Trigram Indexes Enable Fuzzy Search in SQL?

164. What is Role-Based Access Control (RBAC) in Database Security?

165. How Do You Implement Row-Level Security in a Database?

166. What is Data Encryption at Rest in a Database?

167. What is Data Encryption in Transit for Databases?

168. What is Database Auditing and Compliance Logging?

169. What are Multi-Tenant Database Architecture Patterns?

170. Schema-per-Tenant vs Shared-Schema: Which to Choose?

171. What is Database Connection Multiplexing?

172. What are Read Replica Routing Strategies?

173. How Does Load Balancing Across Database Replicas Work?

174. What Key Metrics Should You Monitor for a Database?

175. How Do You Detect a Query Performance Regression?

176. How Do You Approach Database Capacity Planning?

177. Why and How Should You Schedule Index Maintenance?

178. What Strategies Exist for Upgrading a Production Database?

179. What is Tunable Consistency and the N/R/W Quorum Model in NoSQL?

180. What is a Global Secondary Index (GSI) in DynamoDB?

181. How Do Consistency Levels Work in Apache Cassandra?

182. How Does the MongoDB Aggregation Pipeline Work?

183. How Does MongoDB Sharding Architecture Work?

184. GraphQL vs REST: How Do They Fetch Data From Databases Differently?

185. What is the Database-per-Service Pattern in Microservices?

186. API Composition vs Database Joins: How Do They Differ?

187. What is the CQRS Pattern and When Should You Use It?

188. What is Event Sourcing and How Does It Differ From Storing Current State?

189. How Do You Design a Rollback Strategy for Schema Migrations?

190. What Makes a Database Migration Idempotent, and Why Does It Matter?

191. How Do You Manage Seed Data Across Environments?

192. What Are Effective Database Testing Strategies Using Fixtures?

193. What Techniques Keep Test Databases Isolated from Each Other?

194. What Are Connection String Security Best Practices?

195. How Do You Manage Database Secrets Securely?

196. What is the Principle of Least Privilege for Database Access?

197. What Are the Key SQL Dialect Differences Between MySQL and PostgreSQL?

198. What Are Database Collation and Character Sets?

199. What are Query Plan Hints and When Should You Force Index Usage?

200. What are the Limitations of a Cost-Based Query Optimizer?

201. What is Adaptive Query Execution?

202. How Does Parallel Query Execution Work in a Database?

203. What is Vectorized Query Execution?

204. What is a Bitmap Index and When Should You Use One?

205. What is a Bloom Filter and How Do Databases Use It?

206. What is an LSM-Tree and How Does it Handle Writes?

207. What is a B-Epsilon Tree and How Does it Differ from a B-Tree?

208. What is Write Amplification and Why Does it Matter?

209. What Are Database Compression Techniques and Why Do They Matter?

210. How Does Run-Length Encoding Work in Columnar Compression?

211. What is Data Deduplication in Storage Systems?

212. What is Tiered Storage for Hot and Cold Data?

213. What Are Common Archival Strategies for Old Database Data?

214. What Are Database Failover Mechanisms and How Do They Work?

215. Automatic Failover vs Manual Failover: What Is the Difference?

216. What Is the Split-Brain Problem in Distributed Databases?

217. How Do You Design a Robust Database Health Check?

218. What Is Chaos Engineering for Databases and Why Practice It?

219. How Do You Implement API Rate Limiting With Database-Backed Counters?

220. What Are Idempotency Keys and How Do They Prevent Duplicate Database Writes?

221. How Does Optimistic Concurrency Control Work With Version Columns?

222. Soft Delete vs Hard Delete: What Is the Difference and When Do You Use Each?

223. How Do You Design an Audit Trail Table in a Relational Database?

224. What Are Database Schema Documentation Tools and Why Do They Matter?

225. What is Data Lineage Tracking and Why Does It Matter?

226. How Do You Implement Data Quality Validation in Pipelines?

227. What is a "God Table" Anti-Pattern and Why is It a Problem?

228. What Are Common Database Refactoring Techniques?