←

# sql & databases

// [27] real interview questions. Answers and sources live in the practice app.

practice this topic
  1. Explain the ACID properties of database transactions with concrete examples.(junior)
  2. What is the N+1 query problem, how do you detect it, and how do you solve it?(mid)
  3. What are database indexes, how do they improve query performance, and why might the planner not use your index?(mid)
  4. How would you find the most expensive queries in a running application?(mid)
  5. What is database normalization, and is it always needed?(mid)
  6. What is denormalization, and when would you use it?(mid)
  7. Why do databases treat NULL as such a special case?(mid)
  8. How would you manage database schema migrations in a team and in production?(mid)
  9. How would you migrate an application from one database to another, for example from MySQL to PostgreSQL, with minimal downtime?(senior)
  10. How is lazy loading achieved in ORMs? When is it useful, and what are its pitfalls?(mid)
  11. Explain the CAP theorem. What does it actually force you to choose, and what do CP and AP look like in real systems?(senior)
  12. What is eventual consistency, and how do you design an application to live with it?(mid)
  13. When would you use a document database like MongoDB instead of a relational database like PostgreSQL?(mid)
  14. What are the pros and cons of holding domain logic in stored procedures?(senior)
  15. The system you're working on doesn't support transactions. How would you implement transaction-like behavior from scratch?(senior)
  16. Why are long-lived distributed transactions discouraged in service architectures, and how do sagas with compensations replace them?(senior)
  17. What are window functions, and what's the difference between ROW_NUMBER, RANK, and DENSE_RANK?(mid)
  18. Write the classic 'top N per group' query — the 3 most recent orders per customer. Why is it awkward without window functions?(mid)
  19. In what order does SQL logically evaluate a query, and why can't you use a SELECT alias in WHERE?(junior)
  20. Subquery vs JOIN — when do you use each, and what's the danger with correlated subqueries?(mid)
  21. How does MVCC let readers avoid blocking writers, and what does it cost in PostgreSQL?(senior)
  22. Walk through the transaction isolation levels: which anomalies does each allow, and why doesn't everyone just run SERIALIZABLE?(senior)
  23. Two of your transactions deadlock every night. How do deadlocks happen, and how do you fix and prevent them?(mid)
  24. Why does every serious backend use database connection pooling, and how do you size the pool?(mid)
  25. You add read replicas and users start complaining their just-created post disappears on refresh. What's happening and what are the fixes?(mid)
  26. Sharding a database: when do you reach for it, how do you pick a shard key, and what breaks?(senior)
  27. You scan a QR code at a cafe to pay; it works once and can't be replayed, and if your connection hiccups and the app resends, you're still charged only once. Design the QR-payment backend so a scan is single-use and safe to retry.(mid)