Quick Navigation

Select a section to jump directly to the questions you want to practise.

• ➜ DBA Role and Database Fundamentals
• ➜ Database Design, Transactions and Concurrency
• ➜ Indexing, Performance, and Scalability
• ➜ Availability, Backup and Recovery
• ➜ Security and Production Administration
• ➜ Behavioural and Career Questions
• ➜ A Reliable DBA Answer Framework
• ➜ References and Further Reading

What DBA Interviewers Are Really Testing

• Data safety: whether you protect recovery options before making changes.
• Technical judgement: whether you understand SQL, architecture, transactions, indexing, and operating conditions.
• Evidence-based troubleshooting: whether you use logs, plans, waits, metrics, and recent-change history instead of guessing.
• Operational discipline: whether you follow change control, least privilege, monitoring, and documentation.
• Communication: whether you can translate technical risk into business language during normal work and incidents.

1. Tell me about yourself as a database professional.

Keep your answer focused on databases, systems, and business impact. Mention your education or current role, the database platforms you have used, the kinds of data you supported, and one achievement involving availability, performance, recovery, security, or automation. A strong answer could be: ‘I am an IT professional with experience in SQL, data management, user support, and system administration. I have worked with relational databases through application projects, reporting, access control, backups, and troubleshooting. I enjoy finding the reason behind slow queries or failed jobs, documenting the fix, and making the environment more reliable. I am now looking for a DBA role where I can deepen my production skills while protecting data and supporting users.’

2. What does a database administrator do?

A DBA keeps databases available, secure, recoverable, and performant. Typical responsibilities include installation and configuration, user and privilege management, backup and recovery, monitoring, capacity planning, patching, upgrades, performance tuning, automation, documentation, and support for developers or analysts. In a small organisation, one person may handle all of these duties. In a larger team, responsibilities may be divided among production DBAs, development DBAs, cloud DBAs, database reliability engineers, and security specialists.

3. Why do you want to become a database administrator?

Connect your motivation to responsibility rather than prestige. Explain that databases sit at the centre of payroll, customer service, finance, healthcare, logistics, websites, and public services. A credible answer says you enjoy structured problem-solving, working with SQL and operating systems, protecting data, and preventing failures. Mention that you understand DBA work can involve maintenance windows, careful change control, and responding calmly when systems are under pressure.

4. What qualities make a strong DBA?

A strong DBA combines technical depth with caution, communication, and business awareness. Important qualities include analytical thinking, attention to detail, scripting, SQL knowledge, security awareness, disciplined documentation, and the confidence to stop an unsafe change. A DBA should also be curious enough to investigate evidence, humble enough to escalate, and calm enough to communicate clearly during an outage.

5. What database platforms have you used?

Be precise about your real level. Separate production experience from labs or personal projects. For example: ‘I have administered MySQL and PostgreSQL in development environments, written SQL in SQL Server, and studied Oracle architecture and recovery concepts. My strongest hands-on platform is PostgreSQL, but I understand that core DBA principles—backups, transactions, indexing, permissions, monitoring, and change control—transfer across platforms while commands and architecture differ.’ Never claim production experience you cannot defend.

6. What is a database management system?

A database management system, or DBMS, is software that stores, retrieves, protects, and manages data. It provides mechanisms for defining structures, querying data, controlling concurrency, enforcing integrity, managing users, and recovering from failures. Examples include PostgreSQL, MySQL, Microsoft SQL Server, Oracle Database, MongoDB, and cloud-managed database services. The right DBMS depends on workload, consistency needs, scale, operational skills, cost, and integration requirements.

7. What is the difference between a database and a database instance?

The wording varies by platform. In general, the database is the persistent collection of data files and metadata, while an instance is the running processes and memory structures that access that data. In Oracle, the distinction is explicit: an instance opens and manages a database. SQL Server commonly uses an instance to describe an installed Database Engine service that can host multiple databases. A good candidate acknowledges the platform-specific meaning instead of forcing one definition everywhere.

8. What is a relational database?

A relational database organises data into tables made of rows and columns and uses keys and constraints to define relationships and integrity. SQL is commonly used to query and manipulate data. Relational systems are especially useful when consistency, structured relationships, transactions, and flexible querying matter. They are not automatically better for every workload; document, key-value, graph, and time-series systems may suit other access patterns.

9. What is a primary key?

A primary key uniquely identifies each row in a table and normally cannot contain NULL values. It may be a natural value, such as a stable registration number, or a surrogate value generated solely for identification. A good primary key should be unique, stable, minimal, and suitable for relationships. Changing a primary key frequently can create cascading complexity and larger indexes.

10. What is a foreign key?

A foreign key enforces a relationship between a child table and a candidate key, usually a primary or unique key, in a parent table. It prevents orphaned references and protects referential integrity. The DBA should understand delete and update actions such as RESTRICT, NO ACTION, CASCADE, and SET NULL. Cascades can be useful, but careless use may cause unexpectedly large changes.

11. What is database normalisation?

Normalisation is the process of structuring data to reduce unnecessary duplication and modification anomalies. First normal form removes repeating groups and requires atomic values. Second normal form removes partial dependency on part of a composite key. Third normal form removes transitive dependencies on non-key attributes. The goal is maintainable integrity, not blindly splitting every table. Reporting and analytical workloads may intentionally use denormalised models.

12. What is denormalisation, and when is it appropriate?

Denormalisation deliberately duplicates or combines data to reduce joins, simplify reads, or improve performance for a known workload. It may be appropriate in data warehouses, reporting tables, caches, and carefully measured high-read systems. The trade-off is more storage and harder consistency. Before denormalising, confirm the bottleneck with evidence and define how duplicated values will remain correct.

13. What is the difference between DELETE, TRUNCATE, and DROP?

DELETE removes selected rows and can normally use a WHERE clause. TRUNCATE removes all rows more directly and often has different logging, locking, identity, and rollback behaviour depending on the platform. DROP removes the database object definition itself. Because vendor behaviour differs, especially around transactions and foreign keys, a DBA should never repeat the slogan ‘TRUNCATE cannot be rolled back’ without naming the platform and context.

14. What are ACID properties?

ACID describes four transaction properties. Atomicity means all operations succeed or the transaction is undone. Consistency means valid rules are preserved from one committed state to another. Isolation controls how concurrent transactions affect one another. Durability means committed changes survive expected failures. ACID does not remove the need for correct application logic, backups, or disaster recovery.

15. What is a database transaction?

A transaction is a logical unit of work containing one or more operations that should be treated together. Transferring money, for example, requires both a debit and a credit. The transaction should commit only when all required checks succeed; otherwise,e it should roll back. Transactions should be short enough to reduce blocking and resource retention but complete enough to preserve business consistency.

16. What are transaction isolation levels?

Common isolation levels are Read Uncommitted, Read Committed, Repeatable Read, and Serializable, with snapshot or multiversion options in many platforms. Higher isolation reduces anomalies but may increase blocking, retries, or resource use. The exact behaviour differs across PostgreSQL, MySQL, SQL Server, and Oracle. A DBA should choose based on correctness requirements and measured concurrency rather than automatically selecting the highest level.

17. What are dirty reads, non-repeatable reads, and phantom reads?

A dirty read sees another transaction’s uncommitted data. A non-repeatable read occurs when the same row returns different committed values within one transaction. A phantom occurs when repeating a range query returns a different set of matching rows. Isolation mechanisms prevent or manage these anomalies. Interviewers may also ask about lost updates and write skew, which require careful application and isolation design.

18. What is a deadlock?

A deadlock occurs when transactions wait on one another in a cycle, and none can continue. The database normally detects the cycle and terminates one transaction as the victim. DBAs investigate deadlock graphs or logs, identify access order, query plans, indexes, and transaction duration, then reduce risk by accessing objects consistently, shortening transactions, improving indexes, and adding safe application retries.

19. What is an index?

An index is a data structure that helps the database locate rows without scanning every row. Common forms include B-tree, hash, bitmap, full-text, spatial, and specialised indexes. Indexes can dramatically improve reads but consume storage and add work to inserts, updates, deletes, backups, and maintenance. The right index follows the actual filter, join, sort, and selectivity patterns of important queries.

20. What is the difference between clustered and nonclustered indexes?

In SQL Server terminology, a clustered index determines how table rows are organised at the leaf level, so a table can have only one. Nonclustered indexes have separate structures containing keys and row locators. Other platforms use different concepts: PostgreSQL’s CLUSTER is not continuously maintained, and InnoDB organises table data by the primary key. Always anchor the explanation to a specific product.

21. What is a composite index?

A composite index contains more than one column. Column order matters because many B-tree indexes are most useful from the leftmost leading columns. Design should reflect frequent filters, joins, and ordering, while considering equality predicates before range predicates in many cases. Do not create every possible column combination; redundant indexes increase write cost and administrative burden.

22. What is index selectivity?

Selectivity describes how well a column or expression distinguishes rows. A highly selective predicate returns a small percentage of the table and often benefits from an index. A low-selectivity column such as a two-value status may not justify a standalone index, although combined, filtered, partial, bitmap, or covering designs may still help. The optimiser also considers table size, statistics, correlation, and retrieval cost.

23. How do you identify a slow query?

Start with monitoring evidence rather than guessing. Use the platform’s query store, performance schema, statistics views, slow-query log, activity monitor, AWR-like reports, or observability tools. Examine duration, CPU, logical and physical reads, wait time, execution count, blocking, and plan changes. Capture parameters and workload context. A query that is slow once may be less important than a moderately slow query executed thousands of times.

24. What is an execution plan?

An execution plan shows how the optimiser intends to access data: scans, seeks, joins, sorts, aggregations, estimated rows, and costs. An actual plan may include runtime row counts and warnings. Compare estimated and actual cardinality, inspect expensive operators, spills, lookups, and join choices, then test changes. Cost percentages are estimates, not proof; plans must be interpreted with runtime metrics and business context.

25. What are database statistics?

Statistics summarise data distribution and help the optimiser estimate how many rows a predicate will return. Poor or stale statistics can lead to unsuitable join methods, memory grants, and access paths. DBAs monitor automatic statistics behaviour, update strategies, sampling, and plan regressions. Updating statistics is not a universal fix; it can also cause recompilation and different plans, so test and measure.

You May Also Like to Read About

Use or replace these internal links with the most relevant articles available on your website.

• 50 ICT Interview Questions and Answers
• Top 50 Help Desk Interview Questions and Answers: Complete Technical Support Preparation Guide
• 50 CCNA Questions and Answers
• Top 50 cloud computing interview questions and answers
• 50 cybersecurity questions and answers, beginner-friendly cyber safety guide

26. What causes database performance problems?

Common causes include missing or excessive indexes, inefficient SQL, stale statistics, blocking, deadlocks, poor schema design, parameter-sensitive plans, insufficient memory, slow storage, CPU pressure, network latency, misconfiguration, large maintenance operations, and application connection problems. A good DBA separates symptoms from causes by analysing waits, workload, plans, host resources, and recent changes before tuning.

27. How do you tune a poorly performing database?

Establish a baseline, identify the dominant waits and expensive workload, and fix the highest-impact cause first. Optimise SQL and indexes, reduce unnecessary data movement, correct cardinality problems, tune configuration carefully, and verify storage or host constraints. Test in a representative environment, change one factor at a time, define rollback, and compare before-and-after metrics. Avoid random configuration changes copied from the internet.

28. What is connection pooling?

Connection pooling reuses established database connections instead of opening a new connection for every request. It reduces connection overhead and protects the server when configured correctly. A pool that is too large can overwhelm the database; one that is too small can create application waits. DBAs coordinate timeouts, maximum pool size, transaction handling, credential rotation, and monitoring with application teams.

29. What is database partitioning?

Partitioning divides a large logical table or index into smaller physical units based on a key such as date, range, list, or hash. It can improve manageability, retention, loading, and some queries through partition pruning. Partitioning is not automatically faster. Poor partition keys, too many partitions, or queries that cannot prune may add complexity without benefit.

30. What is replication?

Replication copies data or changes from one database server to another. It may be physical or logical, synchronous or asynchronous, and used for availability, read scaling, migration, or geographic distribution. Replication is not a replacement for backups because corruption or accidental deletion may replicate too. DBAs monitor lag, conflicts, slots or logs, topology, failover behaviour, and recovery procedures.

31. What is the difference between high availability and disaster recovery?

High availability reduces downtime from local failures, often through clustering, replicas, automatic failover, and redundant infrastructure. Disaster recovery restores service after a major site, region, security, or data-loss event. HA may fail over bad data instantly, so DR also requires protected backups, recovery locations, documented procedures, and testing. Both should be driven by RTO and RPO.

32. What are RPO and RTO?

Recovery Point Objective is the maximum acceptable amount of data loss measured in time. Recovery Time Objective is the maximum acceptable time to restore service. A five-minute RPO may require frequent log shipping or replication; a four-hour RTO shapes automation, staffing, and infrastructure. These are business decisions translated into technical design, cost, and testing—not numbers the DBA should invent alone.

33. What is the difference between full, differential, and transaction-log backups?

A full backup captures the database at a point in time. A differential backup captures changes since the relevant full backup in systems that support it. A transaction-log or archived-log backup captures log records needed for point-in-time recovery. Names and chains vary by platform. The DBA must understand the recovery model, retention, encryption, off-site copies, and exact restore sequence.

34. What is a logical backup compared with a physical backup?

A logical backup exports database objects and data in a portable representation such as SQL or an archive. Examples include PostgreSQL pg_dump and MySQL mysqldump. A physical backup copies database files or storage blocks in a format tied more closely to the engine. Logical backups offer selective portability but may restore slowly at scale; physical backups are often faster for large-system recovery.

35. How do you verify that a backup is usable?

A successful backup job only proves that a process wrote something. Verify checksums or tool validation where available, monitor logs, confirm retention and encryption keys, and regularly restore into an isolated environment. Test application access, row counts, integrity checks, permissions, and point-in-time steps. Record actual restore duration against RTO. The most trusted backup is one that has passed a realistic restore test.

36. What is point-in-time recovery?

Point-in-time recovery restores a database to a chosen moment before an error, such as an accidental deletion. It typically requires a base backup plus an unbroken sequence of transactions or write-ahead logs. The DBA identifies the safe stopping point, protects the current environment, restores to an alternate location when possible, validates consistency, and coordinates the final cutover with stakeholders.

37. How would you respond to an accidental DELETE without a WHERE clause? First,t stop further damaging activity without destroying recovery evidence. Record the time, session, database, and transaction state. If the transaction is still open, a rollback may be possible. Otherwise,se assess point-in-time recovery, temporal or flashback features, audit history, replicas, or application logs. Restore to an alternate database, validate the required rows, and recover them carefully. Then investigate controls, permissions, and change procedures.

38. How do you secure a database?

Use layered controls: strong identity, MFA for administration where supported, least privilege, role-based access, network segmentation, encryption in transit and at rest, secret management, patching, secure configuration, auditing, backups, monitoring, and tested incident response. Remove default or unused accounts, restrict direct production access, separate administrative duties, and review privileges regularly. Security must also include the application and host, not only the DBMS.

39. What is the principle of least privilege in database administration?

Least privilege means each user, service, and administrator receives only the permissions necessary for approved work and only for the required duration. Applications should not connect as database owners. DBAs should use separate everyday and privileged accounts, controlled elevation, and audited emergency access. Grant permissions to roles rather than individuals where practical and remove access promptly when responsibilities change.

40. How do you manage database users and roles?

Begin with an approved access request and a clear job function. Create or use roles that map to responsibilities, grant the minimum object and system privileges, enforce authentication and password or federated identity policy, set expiry where appropriate, and document ownership. Review inactive accounts, role membership, shared credentials, orphaned users, and excessive privileges. Access changes should be auditable and reversible.

41. What is SQL injection, and what can a DBA do about it?

SQL injection occurs when untrusted input changes the intended meaning of a query. The primary defence is parameterised queries or prepared statements in the application. DBAs reduce impact through least-privilege service accounts, restricted network paths, safe stored interfaces, auditing, patching, and monitoring unusual queries. Escaping alone is fragile, and a web application should never connect using a highly privileged DBA account.

42. How do you monitor a production database?

Monitor availability, failed jobs, backups, replication lag, storage growth, CPU, memory, I/O latency, connections, waits, blocking, deadlocks, error logs, long-running queries, and security events. Use baselines and service-level thresholds to reduce noisy alerts. Each alert needs an owner, runbook, severity, and escalation path. Monitoring should detect both immediate outages and slow trends such as capacity exhaustion.

43. Describe how you would handle a production database outage.

Acknowledge the incident, assess scope and business impact, open the incident process, preserve evidence, and communicate clearly. Check recent changes, infrastructure, service status, logs, storage, connectivity, and dependencies. Restore service using the safest approved runbook, including failover if appropriate. Avoid untested changes under pressure. After recovery, validate data and applications, monitor stability, document the timeline, and lead or support a blameless root-cause review.

44. How do you perform a safe database change or upgrade?

Use a reviewed change request with purpose, risk, dependencies, test evidence, backup or recovery plan, maintenance window, communication, validation, and rollback criteria. Rehearin on a representative environment and check vendor compatibility, deprecated features, drivers, extensions, storage, and replication. During implementation, capture timestamps and results. Afterward, validate application transactions, jobs, performance, backups, and monitoring before closing the change.

45. Tell me about a difficult database problem you solved.

Answer with STAR: Situation, Task, Action, Result. A useful example could involve a report that became slow after data growth. Explain how you captured the execution plan and runtime metrics, found a scan caused by a missing selective index or non-sargable predicate, tested a safer query and index, measured improvement, monitored write overhead, and documented the change. Quantify the result without exaggeration.

46. Tell me about a database mistake you made.

Choose a real but recoverable mistake and focus on accountability. For example, you may have run a maintenance task during a busy period, underestimated lock duration, or tested with unrealistic data. Explain how you recognised the impact, communicated, rolled back or corrected it, and improved the checklist, peer review, monitoring, or staging test. Never claim you have never made a mistake.

47. How do you prioritise several DBA tasks at once?

Prioritise by data-loss risk, security impact, service outage, number of users affected, business criticality, and time-sensitive recovery objectives. A failed backup or a rapidly growing transaction log may outrank a routine index request. Communicate trade-offs, delegate where possible, follow incident and change procedures, and avoid allowing the loudest requester to override an objectively higher risk.

48. How do you keep your database knowledge current?

Use official documentation, release notes, security advisories, vendor labs, test environments, professional communities, and post-incident learning. Build small reproducible labs for backup, restore, replication, permissions, and query tuning. Be cautious with blog commands that lack version context. A strong DBA knows how to verify a feature against the exact engine version before applying it to production.

49. Where do you see yourself in three to five years?

Show ambition while respecting the role. You might say you want to become a dependable production DBA with greater skills in automation, cloud database services, performance engineering, high availability, and security. Mention a relevant certification or platform you plan to master. Emphasise that your immediate priority is learning the employer’s environment, improving reliability, and earning trust through safe work.

50. What questions should you ask the interview panel?

Ask questions that reveal operational maturity: What databases and versions are business-critical? What are the current RPO and RTO targets? How often are restores tested? How are changes reviewed? Which monitoring and automation tools are used? What is the on-call model? What would success look like in the first 90 days? These questions show that you think about reliability, risk, teamwork, and measurable outcomes.

1. Protect: Stop further damage and preserve logs, backups, and recovery paths.
2. Clarify: Establish scope, timeline, business impact, RPO, RTO, and recent changes.
3. Measure: Use monitoring, waits, execution plans, logs, and host metrics.
4. Act safely: Follow the runbook, test assumptions, and define rollback.
5. Validate: Confirm database integrity and complete application transactions.
6. Communicate: Give factual updates without promising an unsupported recovery time.
7. Learn: Document root cause, preventive actions, and knowledge gained.

References and Further Reading

• Microsoft Learn — Back Up and Restore SQL Server Databases
• Microsoft Learn — SQL Server Business Continuity and Disaster Recovery
• Microsoft Learn — Administering Microsoft Azure SQL Solutions Study Guide
• PostgreSQL — Current Documentation
• PostgreSQL — pg_dump Documentation
• PostgreSQL — pg_restore Documentation
• MySQL — Reference Manual
• MySQL — mysqldump Backup Program
• Oracle — Concepts for Database Administrators
• Oracle — Configuring Privileges and Role Authorisation