A DBA's job isn't to write clever queries — it's to keep the database itself healthy: fast, consistent, secure, and recoverable. This path moves past confident querying into the operational SQL DBAs run every day — integrity, indexes, transactions, permissions, and diagnostics.
Where an analyst mostly reads data, a DBA is accountable for the database itself — that queries stay fast as
tables grow, that a bad write can't corrupt the data, that only the right people can touch the right things,
and that a failure can always be recovered from. This path assumes you can already write a
SELECT; it's built around the decisions and diagnostics that come after — constraints, indexes,
transactions, privileges, and the operational habits that keep a production database trustworthy.
A DBA's day often starts with a vague complaint — "the app feels slow" or "this number looks wrong" —
and ends with a precise SELECT, WHERE and ORDER BY that pins down
exactly what's happening. Fast, targeted diagnostic queries are the tool you'll reach for before touching
anything structural.
SELECT name, department_id, salary
FROM employees
WHERE salary > 60000
ORDER BY salary DESC;SELECT * on large diagnostic queries — pulling only the columns you actually need keeps investigation queries fast and readable.Constraints are the database enforcing your data model instead of trusting every application, script
and ad-hoc query to get it right. PRIMARY KEY, FOREIGN KEY, UNIQUE
and CHECK are a DBA's first line of defense against bad data — decided once, enforced on
every write forever after.
CREATE TABLE accounts (
id INT PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
balance DECIMAL(12,2) NOT NULL CHECK (balance >= 0)
);An index turns a full table scan into a targeted lookup — but only if it matches how the table is
actually queried, and only if you can prove it. Reading EXPLAIN output is how a DBA stops
guessing and starts confirming whether a query is using an index or silently scanning millions of rows.
CREATE INDEX idx_employees_department
ON employees (department_id);
EXPLAIN SELECT * FROM employees WHERE department_id = 1;Production databases take concurrent writes from dozens of connections at once, and things fail mid-write. Transactions and ACID guarantees are what keep a partially-failed operation from leaving data in a half-updated state. Isolation levels are the DBA's dial for trading consistency against concurrency — a decision with real performance consequences.
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;GRANT and REVOKE are how a DBA controls exactly who — and what application
service account — can read, write, or alter each piece of the database. The guiding principle is least
privilege: every account gets only the access it actually needs, nothing "just in case."
GRANT SELECT, INSERT ON orders TO 'app_service'@'%';
REVOKE DROP, ALTER ON orders FROM 'app_service'@'%';Stored procedures let a DBA encapsulate maintenance and business logic inside the database itself; triggers fire automatically on inserts, updates or deletes — invaluable for audit trails and enforcing invariants no application layer can be trusted to remember every time.
CREATE TRIGGER trg_salary_audit
AFTER UPDATE ON employees
FOR EACH ROW
INSERT INTO salary_audit (employee_id, old_salary, new_salary)
VALUES (OLD.id, OLD.salary, NEW.salary);"It was fast last month" is one of the most common tickets a DBA gets. Understanding how the query
optimizer picks a plan — and the common anti-patterns that quietly defeat an index, like wrapping a
column in a function inside WHERE — turns that ticket from a mystery into a checklist.
WHERE YEAR(order_date) = 2026 can silently prevent an index on order_date from being used — wrapping a column in a function is one of the most common accidental performance killers.A backup you've never tested restoring isn't a backup, it's a hope. Beyond backups, routine data-quality queries — orphaned foreign keys, unexpected duplicates, row-count drift — catch integrity problems while they're small, before they surface the hard way in a report or a failed migration.
TRUNCATE resets a table fast and is typically minimally logged; DELETE removes rows individually and can be filtered or rolled back — knowing which one you actually want matters before you run either.These projects use the exact integrity and modeling discipline a DBA relies on, on realistic datasets.
DBA interviews lean on performance tuning, transactions, and troubleshooting scenarios — exactly what this path covered.
25 questions covering everything above. Score 80% or higher to earn your Database Administrator badge on your dashboard.
25 questions · pass at 80%+ to earn your Database Administrator badge on your dashboard. One-time payment, lifetime access.