5 exercises — choose the best-structured answer to common DBA interview questions. Focus on precise database terminology, trade-off reasoning, and production-aware thinking.
Structure for DBA interview answers
Define precisely: use the exact technical term, not just a synonym
Mechanism: explain how it works at the storage or transaction level
Trade-off: name what you gain and what you sacrifice
Production context: show awareness of real-world constraints (locks, downtime, replication)
0 / 5 completed
1 / 5
The interviewer asks: "Can you explain ACID properties?" Which answer demonstrates the deepest understanding?
Option A is the strongest: it defines each property with a precise, single-sentence definition that captures the essential mechanism. Key signals of depth: "brings the database from one valid state to another, respecting all constraints" (not just "keeps data valid"), and "concurrent transactions execute as if they were sequential" (explains the isolation model). Option C mentions write-ahead logging — an excellent implementation detail — but defines isolation as "prevents dirty reads" which is incomplete (isolation covers dirty reads, non-repeatable reads, and phantom reads at various levels). Option D is accurate but uses informal phrasing ("writes survive a restart"). Option B is too brief to show depth. For ACID: give the full noun phrase for each property and the exact mechanism, not just a synonym.
2 / 5
The interviewer asks: "What is the difference between a clustered and a non-clustered index?" Choose the most technically precise answer.
Option A is the best: it explains why only one clustered index is possible (data rows are physically ordered and stored according to the clustered key), explains what a non-clustered index contains (indexed columns plus a pointer back to the actual row), and gives practical guidance on when to use each. Option C is also strong — mentioning SQL Server's default PK behaviour and the difference between a clustered key pointer vs file/row pointer shows implementation awareness. Option D's "clustered index = the table" is a memorable shorthand but too informal. Option B is accurate but lacks the "why" behind the one-clustered-index limit. Key tip: explain the "why only one clustered" constraint — it signals you understand the physical storage model.
3 / 5
The interviewer asks: "How do you approach query optimisation when a slow query is reported?" Which answer shows the best professional process?
Option A is the strongest process description: it starts with the right tool (EXPLAIN/EXPLAIN ANALYZE), lists multiple specific signals to look for in the plan (full scans, row estimate errors, nested loops, sort operations), covers indexes on all relevant clause types (WHERE, JOIN, ORDER BY), mentions statistics staleness, gives a specific rewrite example (correlated subquery → CTE/join), and adds the implicit type conversion trap — a detail that only experienced DBAs usually mention. Option C is good and mentions partitioning — showing awareness of large-scale solutions. Option D's approach of asking the developer for context first is actually very professional and often overlooked by purely technical candidates. Option B is too basic for a DBA interview. Structure: explain → identify bottlenecks → fix indexes → refactor query → check statistics.
4 / 5
The interviewer asks: "What is database normalisation and when would you denormalise?" Choose the most complete and balanced answer.
Option A is the strongest: it defines normalisation precisely (reduce data redundancy and improve data integrity), names the mechanism (normal forms, 1NF–BCNF), explains what the forms eliminate (insertion, update, or deletion anomalies), and gives the exact condition for denormalisation (read performance outweighs write integrity concerns) with a specific real-world context (analytical/reporting) and a concrete trade-off statement. Option C is also excellent — distinguishing OLTP (3NF) from data warehouse (star/snowflake) shows architectural maturity and real-world experience. Option D is accurate and mentions the anomalies, which is a strong signal. Option B is too brief. Tip: the best DBA answer always names the trade-off — normalisation favours write integrity; denormalisation favours read performance.
5 / 5
The interviewer asks: "How do you handle database migration in a high-availability production environment?" Choose the most professionally structured answer.
Option A is the best: it names the key principle (backward-compatible migrations — the old app version must work with new schema), explains the phased approach for destructive changes (add → deploy → drop), names specific industry tools (pt-online-schema-change, gh-ost), mentions the rollback plan requirement, and notes low-traffic windows. Option C names the "expand-contract pattern" — expert-level vocabulary that shows architectural awareness. Option B is sensible but lacks the backward-compatibility principle and doesn't mention specific tools. Option D's approach (backup → run → rollback from backup) describes a naive strategy with high risk — restoring from backup in a HA environment causes significant downtime. Key insight: the critical principle is backward compatibility between schema and application code during rolling deploys.