A Database Administrator installs, configures, secures and monitors databases, manages backups and recovery, handles user access, applies patches and keeps performance healthy. DBAs troubleshoot locks, deadlocks and slow queries, plan capacity and replication, and support high availability. Experience with one major database such as PostgreSQL, MySQL or SQL Server is expected.
Backup and recovery Performance tuning Security and access Replication Monitoring
Solved for this roleStart practicing
3 QUESTIONS FREESign up to unlock the full question bank and track your progress.Unlock every question and answer, then continue practising company-wise and package-wise.
A natural key is a real-world identifier like PAN number, email or product code. A surrogate key is a system-generated value, usually an integer or UUID, with no business meaning. Surrogate keys are stable, compact and good for joins, while natural keys can change or be long. A common design uses a surrogate primary key plus a unique constraint on the natural key.
02
A query uses SELECT * on a table with 80 columns. Why is that a problem?
SELECT * fetches every column, increasing disk reads, memory, network transfer and application processing. It prevents covering indexes from being used and can break applications when columns are added or reordered. Select only required columns, which lets the optimiser use narrower indexes and makes the intent clear. SELECT * is acceptable only for quick exploration, not production code.
A star schema has a central fact table with measures such as sales amount and foreign keys to denormalised dimension tables like date, product, store and customer. A snowflake schema normalises dimensions further, such as product to category to department. Star schemas are simpler and faster for BI queries, while snowflake schemas save space and reduce redundancy.
A materialised view stores the result of a query physically, unlike a normal view. Reads are fast because data is precomputed, which suits heavy aggregations for dashboards. The trade-off is staleness, so it must be refreshed on schedule, on commit or incrementally depending on the database. PostgreSQL uses REFRESH MATERIALIZED VIEW, and SQL Server offers indexed views with similar benefits.
05
What is table partitioning and how does it help performance?
Partitioning splits a large table into smaller physical pieces by a key, such as range partitions by month, list partitions by region, or hash partitions. Queries filtering on the partition key read only relevant partitions, called partition pruning. It also makes maintenance easier, such as dropping old months instantly. It does not help queries that do not filter on the partition key.
A recursive CTE has an anchor member that returns the starting rows and a recursive member that references the CTE itself, joined with UNION ALL. The database repeats the recursive part using the previous iteration's rows until no new rows appear. It is used for hierarchies like org charts, bill of materials and generating sequences. Add a depth limit to prevent infinite loops on cyclic data.
07
What are the performance considerations of window functions?
Each distinct PARTITION BY and ORDER BY combination usually needs a sort, which is expensive on large data and may spill to disk. Reuse the same window definition where possible, using a WINDOW clause, and create indexes that match the partition and order columns so sorts can be avoided. Filter rows before applying windows and avoid unnecessary large frames.
08
Compare this month's and last month's sales per product using a full outer join.
SELECT COALESCE(c.product_id, p.product_id) AS product_id,
COALESCE(c.sales, 0) AS this_month,
COALESCE(p.sales, 0) AS last_month
FROM cur_month c
FULL OUTER JOIN prev_month p
ON c.product_id = p.product_id;
Products sold in only one of the months still appear, with zero for the other month.
09
Create a simple stored procedure that returns orders for a customer.
MySQL:
DELIMITER //
CREATE PROCEDURE get_orders(IN p_customer INT)
BEGIN
SELECT order_id, order_date, amount
FROM orders
WHERE customer_id = p_customer
ORDER BY order_date DESC;
END //
DELIMITER ;
CALL get_orders(101);
Parameters prevent SQL injection compared with building query strings in code.
10
How would you design a database for an online food delivery app at a high level?
Core tables are users, addresses, restaurants, menu_items, orders, order_items, payments, delivery_partners and deliveries. Orders reference users, restaurants and addresses, order_items reference orders and menu_items with price at order time, and payments and deliveries reference orders. Add status history tables for tracking and indexes on user_id, restaurant_id and created_at for common queries.
11
A query that ran in 1 second now takes 40 seconds after a data load. What do you check?
Compare the current execution plan with the old one. A big data load often makes statistics stale, causing poor row estimates and a different join or scan choice, so update statistics first. Check for index fragmentation, missing indexes after the load, blocking from other sessions, and whether the data volume crossed a threshold. Test again after each change.
12
Write an upsert that inserts a row or updates it if it exists.
PostgreSQL:
INSERT INTO inventory (sku, qty)
VALUES ('A100', 10)
ON CONFLICT (sku)
DO UPDATE SET qty = inventory.qty + EXCLUDED.qty;
MySQL:
INSERT INTO inventory (sku, qty) VALUES ('A100', 10)
ON DUPLICATE KEY UPDATE qty = qty + VALUES(qty);
SQL Server and others use MERGE. A unique key on sku is required.
13
How do you balance performance tuning with delivery deadlines?
Situation: a release was due in two days, but a key query was slow under load testing. Task: fix the risk without delaying the release. Action: I identified the top two bottlenecks from the plan, applied an index and a query rewrite that were low risk, and logged deeper redesign work as a follow-up ticket with data. Result: the release went live on time and the redesign was completed in the next sprint.
14
The dashboard queries hit the production database and slow down the app at month end. What do you propose?
Separate workloads: route reporting to a read replica or data warehouse, pre-aggregate month-end metrics into summary tables refreshed after close, and cache results for dashboards. Tune the heaviest queries with indexes and better predicates. Schedule batch reports outside business hours and set resource limits or query timeouts for reporting users so customer transactions are protected.
PostgreSQL:
CREATE FUNCTION log_salary() RETURNS trigger AS $$
BEGIN
IF NEW.salary <> OLD.salary THEN
INSERT INTO salary_audit(emp_id, old_sal, new_sal, changed_at)
VALUES (OLD.emp_id, OLD.salary, NEW.salary, NOW());
END IF;
RETURN NEW;
END; $$ LANGUAGE plpgsql;
CREATE TRIGGER trg_salary AFTER UPDATE ON employees
FOR EACH ROW EXECUTE FUNCTION log_salary();
16
Tell me about a production incident involving a database and how you handled it.
Situation: during a sale, order placement started failing with lock timeouts. Task: restore service quickly. Action: I identified a long-running reporting query holding locks on the orders table, coordinated with the team to kill it, and moved that report to the read replica. Result: orders recovered within ten minutes, and we added query timeouts and a rule that reports never run on the primary.
Boyce-Codd Normal Form requires that for every functional dependency, the determinant is a candidate key. 3NF allows a dependency where the dependent attribute is part of a candidate key, while BCNF does not. For example, if a teacher teaches only one subject but a student-subject pair determines the teacher, the table is in 3NF but not BCNF and should be split.
18
Add a foreign key and a check constraint to an existing table.
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id);
ALTER TABLE orders
ADD CONSTRAINT chk_amount_positive
CHECK (amount > 0);
First clean existing rows that violate the rule, otherwise the ALTER fails. Naming constraints makes errors easier to read.
19
Your team debates using UUIDs or auto-increment integers as primary keys. What do you advise?
Auto-increment integers are compact, fast to index and naturally ordered, but expose counts and are hard to generate across distributed systems. UUIDs are globally unique and good for distributed inserts and merging data, but larger and random, causing index fragmentation. Time-ordered UUIDs such as UUIDv7 reduce fragmentation. Choose based on scale, distribution and security needs.
20
What are database statistics and why do they matter?
Statistics describe data distribution in columns, such as row counts, distinct values and histograms. The optimiser uses them to estimate how many rows each step returns and pick join types and indexes. Stale statistics after large data loads lead to bad plans, such as nested loops over millions of rows. Update them with ANALYZE in PostgreSQL or UPDATE STATISTICS in SQL Server.
A covering index contains all columns a query needs, either as key columns or included columns, so the database answers the query from the index alone without visiting the table. For SELECT order_date, amount FROM orders WHERE customer_id = 5, an index on (customer_id) INCLUDE (order_date, amount) avoids lookups. It speeds reads but increases index size and write cost.
22
Inserts into a heavily used table have become slow. What could be causing it?
Too many indexes increase write cost, so audit and remove unused ones. Random UUID primary keys cause page splits and fragmentation. Triggers, foreign key checks without indexes on child columns, lock contention from long transactions, and log disk bottlenecks also slow inserts. Use batch inserts, sequential keys, proper indexing of foreign keys and monitor waits to find the actual bottleneck.
23
Implement keyset pagination instead of OFFSET for large tables.
Slow for deep pages:
SELECT * FROM orders ORDER BY id LIMIT 50 OFFSET 500000;
Keyset pagination:
SELECT * FROM orders
WHERE id > :last_seen_id
ORDER BY id
LIMIT 50;
OFFSET reads and discards all skipped rows, while keyset seeks directly using the index on id, keeping each page fast.
Avoid indexes on small tables where scans are cheap, on columns with very low selectivity such as a boolean flag used alone, on tables with heavy writes and few reads, and duplicates of existing indexes. Every index slows inserts and updates and uses memory and storage. Review unused indexes periodically with database statistics and drop them after checking dependencies.
A SARGable (Search ARGument able) predicate lets the optimiser use an index seek. Wrapping an indexed column in a function, such as WHERE YEAR(order_date) = 2025 or WHERE LOWER(email) = 'x', usually prevents index use. Rewrite as WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01'. Leading wildcards and implicit type conversions also make predicates non-SARGable.
26
Finance asks for the count of customers whose total spend crossed 1 lakh in each city. How do you write it?
Aggregate in two levels. First compute total spend per customer and city in a CTE: SELECT city, customer_id, SUM(amount) AS spend FROM orders GROUP BY city, customer_id. Then in the outer query: SELECT city, COUNT(*) FROM cte WHERE spend > 100000 GROUP BY city. Trying to do it in one GROUP BY by city fails because the threshold applies to each customer, not each city.
Multi-Version Concurrency Control keeps multiple versions of rows, so readers see a consistent snapshot without blocking writers, and writers do not block readers. PostgreSQL stores old row versions in the table and cleans them with VACUUM, while MySQL InnoDB uses undo logs. MVCC enables high concurrency in read-heavy systems, but long-running transactions keep old versions alive and cause bloat.
28
An e-commerce app needs products with very different attributes, such as phones and shirts. How do you model them?
Options are a common products table plus category-specific tables (table per type), an entity-attribute-value table, or a JSON or JSONB column for flexible attributes. A practical design keeps shared fields like name, price and category as columns, stores variable attributes in JSONB with indexes on frequently filtered keys, and validates required attributes in the application or with check constraints.
29
What are slowly changing dimensions in data warehousing?
Slowly changing dimensions track changes in dimension attributes over time. Type 1 overwrites old values with no history. Type 2 adds a new row with effective dates and a current flag, preserving full history, such as a customer moving city. Type 3 keeps a previous value column for limited history. Type 2 is most common when reports must reflect attributes as they were at transaction time.
30
A reporting team wants to query the production OLTP database directly. What are the risks and alternatives?
Heavy analytical queries can lock rows, use CPU and slow customer transactions, and normalised schemas make reporting complex. Alternatives are a read replica for reporting, a data warehouse loaded by ETL or CDC with a star schema, or materialised summary tables. Grant read-only access with limits if direct access is unavoidable, and schedule heavy jobs off-peak.
31
What is the difference between optimistic and pessimistic locking?
Pessimistic locking locks rows when read, using SELECT ... FOR UPDATE, so other transactions wait; it suits high-conflict cases such as seat booking. Optimistic locking does not lock but checks at update time that the row has not changed, usually with a version column: UPDATE ... SET version = version + 1 WHERE id = 1 AND version = 5. If zero rows update, the application retries. It suits low-conflict workloads.
32
Product prices change often, but old orders must keep the price at the time of purchase. How do you design it?
Store the unit price, discount and tax in order_items when the order is created instead of reading the current product price, so history is not affected by updates. Maintain a product_price_history table with effective_from and effective_to dates for analysis. Reports on past orders use order_items values, while catalogue pages use the current price.
33
How would you design data archiving for a table growing by 10 million rows a month?
Partition the table by month so old partitions can be detached or moved to an archive table or cheaper storage without heavy deletes. Define a retention policy with business and compliance teams, for example 24 months online. Keep archived data queryable through a separate schema or warehouse, update indexes and statistics, and automate the process with monitoring and alerts.
34
What is the difference between a CTE, a subquery, a temp table and a view?
A subquery and a CTE exist only for one statement; a CTE is more readable and can be recursive. A temporary table stores data physically for the session, can be indexed and reused across statements, useful for large intermediate results. A view is a saved query stored in the database that anyone with access can reuse. Choose by reuse, size and performance needs.
35
You added an index but the query still does a full table scan. Why might that be?
Possible reasons: the predicate is not SARGable because of a function or type conversion, the query returns a large share of rows so a scan is cheaper, the composite index column order does not match the filter, statistics are stale, or OR conditions prevent index use. Check the plan, rewrite the predicate, update statistics, or adjust the index to match the query.
36
Tell me about a time you set SQL coding standards for a team.
Situation: our analytics team of eight wrote queries in very different styles, making reviews slow. Task: create common standards. Action: I drafted guidelines on naming, CTE usage, formatting, comments and anti-patterns, added a linter to the repository, and held a short workshop. Result: review time dropped noticeably, new joiners onboarded faster, and production errors from ambiguous queries reduced.
37
A query with many OR conditions across different columns is slow. How can you optimise it?
OR across different columns often prevents a single index seek. Rewrite it as UNION ALL of separate queries, each using its own index, removing duplicates carefully if needed, or use IN for multiple values of the same column. Some optimisers can combine indexes with bitmap or index merge plans. Compare plans and timings to choose the best approach.
38
Your application logs frequent deadlock errors at peak hours. How do you investigate and reduce them?
Capture deadlock graphs or logs to see which statements and resources are involved. Common fixes are accessing tables and rows in the same order in all code paths, keeping transactions short, adding indexes so updates lock fewer rows, avoiding user interaction inside transactions, and using lower isolation where safe. Add retry logic for deadlock victims since some deadlocks cannot be fully eliminated.
39
A long-running report transaction is causing table bloat and blocking. What do you do?
Long transactions hold snapshots and locks, preventing cleanup of old row versions and blocking DDL or writers. Move the report to a read replica, break it into smaller batches, use read committed snapshot where supported, and avoid leaving transactions open in client tools. Set statement or idle-in-transaction timeouts, and monitor sessions with long transaction durations.
40
What is parameter sniffing and how do you handle it?
In databases that cache plans, such as SQL Server, the plan for a parameterised query is compiled using the first parameter values. If those values are unusual, the cached plan may be poor for typical values, causing sudden slowness. Fixes include OPTION (RECOMPILE) for that query, OPTIMIZE FOR hints, plan guides, splitting procedures for different data shapes, or updating statistics.
Frequently asked questions
What does a Database Administrator do?
A Database Administrator installs, configures, secures and monitors databases, manages backups and recovery, handles user access, applies patches and keeps performance healthy. DBAs troubleshoot locks, deadlocks and slow queries, plan capacity and replication, and support high availability. Experience with one major database such as PostgreSQL, MySQL or SQL Server is expected.
Which skills are tested in a Database Administrator interview?
Expect questions on Backup and recovery, Performance tuning, Security and access, Replication, Monitoring. Most rounds also ask you to explain a project and solve one practical problem live.
How do I prepare for a Database Administrator interview?
Revise Indexes and Performance, Transactions and Advanced SQL, Database Design first using the topic sheets, practise the questions on this page aloud, then do one full mock interview to practise timing.