Database Administrator exam: what is tested and how to prepare

What the questions cover

• SQL: selecting, filtering, grouping, joining, subqueries and the order in which a query is evaluated.
• Schema design: keys, relationships, normalisation to third normal form, and when denormalising is the right call.
• Indexes: what they speed up, what they slow down, and why a query can ignore the index you created.
• Transactions: atomicity and consistency, isolation levels, locking, blocking and deadlocks.
• Backup and recovery: full, differential and log backups, point-in-time recovery, and recovery objectives.
• Security: users, roles and privileges, least privilege, encryption in transit and at rest, and auditing.
• Availability: replication, failover, maintenance windows and what each protects against.
• Monitoring: finding slow queries, watching space and connections, and knowing what normal looks like.

Read SQL the way the engine does

Questions that show a query and ask what it returns are testing evaluation order, not typing speed. The engine filters rows, then groups, then filters groups, then sorts. Candidates who apply a condition at the wrong stage pick the wrong answer confidently. Read the clauses in the order the engine uses them and the answer usually falls out.

Watch joins in particular: an inner join drops rows without a match, a left join keeps them with nulls, and a condition placed in the where clause rather than the join can quietly turn one into the other.

Indexes are a trade, not a gift

An index makes reads faster and writes slower, and it costs storage. Questions describe a table with many indexes and slow inserts, or a query that still scans despite an index, and expect you to explain why. Know the common reasons an index is ignored: a function wrapped around the indexed column, a leading wildcard in a pattern match, a data type mismatch, or statistics so stale that the planner chooses badly.

Recovery is the real subject

A backup strategy is judged by two numbers: how much data the organisation can afford to lose and how long it can afford to be down. Full backups restore simply but take space and time; differential and log backups reduce both at the cost of a longer restore chain. Point-in-time recovery needs the log chain intact. The recurring test answer is that a backup nobody has restored is not yet a backup.

Preparing

Install a database on your own machine and use it. Load a few thousand rows, write queries, add an index and watch the plan change, open two sessions and create a deadlock on purpose, then break something and restore it from backup. A week of that teaches more than a month of reading, and it gives you answers for the interview that follows. Keep the commands you used in a single file as you go. Reading your own notes on the morning of the test works better than rereading a manual you only half remember.