Database and SQL Developer interview questions and answers.

Questions

Technical questions to practise out loud

1. 01

   How do composite indexes work?

   A composite index orders entries by its column sequence, so it efficiently supports predicates using the leftmost columns and may also satisfy ordering or cover selected fields. Column order must match real access paths.

   Read the full breakdown
2. 02

   How do you read a query execution plan?

   Read the plan from actual row flow: compare estimated and actual cardinalities, identify scans, join algorithms, sorts, spills and repeated loops, then find the first bad estimate or expensive operator rather than only the highest cost.

   Read the full breakdown
3. 03

   How do locks and transactions affect database concurrency?

   Transactions hold locks or versions to preserve isolation; larger scope and longer duration increase blocking and deadlock risk. Keep transactions short, access resources consistently, index predicates, and choose isolation from the invariant.

   Read the full breakdown
4. 04

   When do you normalise or denormalise data?

   Normalize transactional data to remove update anomalies and enforce one source of truth; denormalize deliberately for measured read performance or analytics, with clear ownership and a reliable synchronization strategy.

   Read the full breakdown
5. 05

   How does database replication affect design?

   Replication improves read scale and recovery but introduces lag, failover and consistency choices. Decide which reads may be stale, how writes find the primary, how clients recover, and how replication health is monitored.

   Read the full breakdown
6. 06

   How do you investigate a database deadlock?

   Use the database deadlock graph to identify transactions, locks and access order; reproduce the competing paths, then shorten transactions, add useful indexes, or acquire resources consistently. Retry only the chosen victim safely.

   Read the full breakdown
7. 07

   How do you validate database backups?

   A backup is valid only after a restore test. Automate encrypted backups, retention and off-site copies, restore into an isolated environment, run integrity and application checks, and measure achieved RPO and RTO.

   Read the full breakdown
8. 08

   When do you consider database sharding?

   Consider sharding only after a single database and partitioning cannot meet measured scale or isolation needs. Choose a stable shard key, avoid cross-shard transactions, plan rebalancing, routing, hot keys and global queries.

   Read the full breakdown
9. 09

   How do you secure database access?

   Use separate least-privilege identities, short-lived credentials, network restrictions, TLS, encryption at rest, parameterized queries, audited administrative actions, protected backups, patching, and tested access revocation.

   Read the full breakdown
10. 10

    How do you paginate large SQL result sets?

    Prefer keyset pagination for large changing datasets: order by a unique stable tuple and fetch rows after the last seen key. OFFSET scans and discards growing prefixes and can duplicate or skip rows under changes.

    Read the full breakdown