Database Interview Questions

One identifies rows in its own table; the other points to rows in another table.

• A primary key uniquely identifies each row in a table. It must be unique and cannot be NULL. A table has at most one primary key (which may span several columns).
• A foreign key is a column whose values must match a primary (or unique) key in another table. It creates the relationship between tables and prevents "orphan" rows, such as an order for a customer that does not exist.

Think about storing the same fact in many places.

Normalization is organising tables so that each fact is stored in exactly one place. It reduces duplicated data and prevents update anomalies (changing a customer's city in one row but forgetting another).

The first three normal forms, in simple terms:

• 1NF: every column holds a single value (no lists inside a cell) and rows are uniquely identifiable.
• 2NF: 1NF, and every non-key column depends on the whole primary key, not just part of it.
• 3NF: 2NF, and non-key columns depend only on the key, not on other non-key columns.

In practice, analytical databases sometimes denormalize on purpose to make reads faster.

Four guarantees that make transactions reliable.

• Atomicity: a transaction happens completely or not at all.
• Consistency: a transaction moves the database from one valid state to another, respecting all constraints.
• Isolation: concurrent transactions do not see each other's unfinished work (to a degree set by the isolation level).
• Durability: once committed, changes survive crashes and restarts.

Classic example: transferring money between two accounts must debit one and credit the other atomically.

Compare it with the index at the back of a book.

An index is a separate data structure (usually a B-tree) that lets the database find rows by a column's value without scanning the whole table, just as a book's index lets you jump to a page.

Trade-offs:

• Reads that filter, join, or sort on the indexed column become much faster.
• Every INSERT, UPDATE and DELETE must also update the index, so writes become slower.
• Indexes use extra storage.

Index the columns you frequently search or join on, not every column.

One serves the app; the other serves the analysts.

• OLTP (Online Transaction Processing) databases run applications: many small, fast reads and writes, such as placing an order. They are usually normalized and row-oriented (PostgreSQL, MySQL).
• OLAP (Online Analytical Processing) systems answer large analytical queries across millions of rows, such as revenue by region per month. They are often column-oriented and denormalized (data warehouses such as BigQuery, Snowflake, Redshift).

They differ in how many columns they use and whether they carry real-world meaning.

• A candidate key is any column (or set of columns) that uniquely identifies a row. A table can have several; one is chosen as the primary key, and the others are often enforced with UNIQUE.
• A composite key uses more than one column together, for example (student_id, course_id) in an enrollments table, where neither column is unique on its own.
• A natural key has real-world meaning (an email address, a passport number). A surrogate key is an artificial identifier with no meaning, usually an auto-incrementing integer or a UUID.

Surrogate keys are popular because they never change, while natural values like email addresses can change and would then need updating everywhere they are referenced.

Compare the data model, the schema, and how they usually scale.

Choose relational databases for structured data with relationships and transactions (orders, payments, inventory). NoSQL suits very large or rapidly changing data, simple access patterns at huge scale, caching (Redis), or highly connected data (graph databases). Many systems use both.

It deliberately undoes part of normalization. What do you gain, and what do you risk?

Denormalization means deliberately storing redundant data, for example keeping a customer_name column in the orders table, or a precomputed order_total, so that common reads need fewer joins or calculations.

• Benefit: faster, simpler reads, which matters for reporting and read-heavy applications.
• Cost: the same fact is stored in several places, so every write must keep the copies in sync, or the data becomes inconsistent.

A good approach is to start normalized and denormalize specific tables only when measurements show that joins are a real bottleneck. Data warehouses commonly use denormalized "star schemas" because they are read far more than written.

Two transactions each hold something the other one needs.

A deadlock happens when two (or more) transactions each hold a lock that the other is waiting for, so neither can continue:

1. Transaction A locks account 1, then tries to lock account 2.
2. Transaction B locks account 2, then tries to lock account 1.

The database detects the cycle and aborts one of the transactions with an error, so the application must be ready to retry it.

To reduce deadlocks:

• Access tables and rows in a consistent order (for example, always lock the lower account id first).
• Keep transactions short, and never wait for user input inside one.
• Add indexes so updates lock fewer rows.
• Retry transactions that fail with a deadlock error.

Think about what one transaction can see of another transaction's unfinished or newly committed changes.

Isolation levels control how much concurrent transactions can see of each other's work. The SQL standard defines four, from weakest to strongest:

• Dirty read: reading another transaction's changes before it commits (they might be rolled back).
• Non-repeatable read: reading the same row twice and getting different values, because another transaction committed a change in between.
• Phantom read: running the same query twice and getting new rows, because another transaction inserted matching rows.

Stronger levels are safer but allow less concurrency. Defaults differ: PostgreSQL, Oracle and SQL Server use Read Committed; MySQL (InnoDB) uses Repeatable Read. Real databases also differ in details; for example, PostgreSQL's Repeatable Read also prevents phantom reads.

Measure first. What does the database say it is doing?

A structured answer:

1. Reproduce and measure the query with realistic data, and note its time.
2. Read the execution plan with EXPLAIN (or EXPLAIN ANALYZE in PostgreSQL, the actual execution plan in SQL Server). Look for full table scans on large tables, row estimates far from the actual counts, and expensive sorts or nested loops.
3. Add or adjust indexes for the columns used in WHERE, JOIN and ORDER BY. A composite index should list the equality columns first.
4. Make conditions index-friendly: WHERE YEAR(order_date) = 2026 cannot use an index on order_date, but WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01' can.
5. Reduce the work: select only the needed columns instead of SELECT *, filter early, and paginate.
6. Check the statistics are up to date (ANALYZE in PostgreSQL, UPDATE STATISTICS in SQL Server), so the optimiser makes good choices.
7. If reads still dominate, consider caching, a materialized view or denormalization.

Then measure again to confirm the improvement.

One copies the same data to several servers; the other splits the data between them.

• Replication keeps copies of the same data on several servers. A common setup has one primary that accepts writes and replicas that serve reads. It improves read capacity and availability (a replica can take over if the primary fails), but every server still holds all the data, and asynchronous replicas can lag slightly behind.
• Sharding (horizontal partitioning) splits the data across servers, for example customers A–M on one server and N–Z on another, or by a hash of the customer id. Each shard holds only part of the data, so it scales writes and storage, but queries that span shards become harder, and choosing a good shard key is critical.

Large systems often use both: the data is split into shards, and each shard is replicated.

It is about what a distributed system must give up when the network between its nodes fails.

The CAP theorem says that a distributed data store cannot guarantee all three of these at the same time:

• Consistency: every read sees the most recent write.
• Availability: every request to a working node gets a response.
• Partition tolerance: the system keeps working even when network messages between nodes are lost.

Network partitions cannot be ruled out in a real distributed system, so the practical choice is what to do during a partition: stay consistent by refusing some requests (CP), or stay available by answering with possibly stale data (AP). When there is no partition, a system can provide both consistency and availability.

Many databases let you tune this trade-off per operation, for example with quorum reads and writes.

Which one can be used inside a SELECT statement?

Both are named blocks of code stored in the database, but they are used differently:

Use a procedure for a multi-step operation such as "place an order", and a function for a reusable calculation inside queries. The exact rules vary between databases; for example, PostgreSQL functions can modify data.