KAHIBARO
Discord Login Register ☰
☰

33.7. Database Interview Questions

Table of Contents

Types of Database Interview Questions

Database interview questions usually fall into a few categories:

You will rarely get only one type. Most backend interviews mix them to see how you think about data and trade-offs.

In this chapter we will not re-teach databases or SQL. Instead we will focus on what you might be asked and how to answer with clarity and confidence, plus example answers.

Fundamental Concept Questions

Relational vs NoSQL

Typical questions:

Example answer structure:

  1. Explain the core idea
    • Relational: tables, rows, columns, fixed schema, SQL.
    • NoSQL: document, key–value, wide-column, graph, flexible schema.
  2. Highlight strengths and weaknesses
  3. Give simple use cases

Example concise answer:

A relational database stores data in tables with a fixed schema and supports SQL and ACID transactions. It is ideal when data is structured and relationships are important, for example orders, users, payments.

NoSQL is a group of databases with more flexible schemas, for example document stores like MongoDB, key value stores like Redis, column stores, and graph databases. They trade some relational features for flexibility or scalability. They work well for large, semi-structured data, caching, or when the schema evolves quickly.

Primary key and foreign key

Typical questions:

Example answer:

A primary key uniquely identifies each row in a table. It must be unique and not null.
A foreign key is a column, or set of columns, in one table that refers to the primary key of another table. The database can enforce referential integrity with foreign key constraints, so you cannot have child rows pointing to non-existent parent rows.

Normalization

Typical questions:

You do not need to recite formal definitions of every normal form. Focus on the goal and trade-offs.

Example answer:

Normalization is the process of structuring tables so that each fact is stored only once, which reduces redundancy and update anomalies.

For example, instead of storing a customer address in every order row, you store customer data in a Customers table, and orders reference the customer with a foreign key.

Denormalization is the opposite, where you intentionally duplicate some data, for example storing the customer name in the Orders table, to speed up reads or simplify queries. It is useful in analytics or high-read workloads, but it makes writes and updates more complex.

ACID properties

Typical question:

ACID properties:

  • Atomicity: each transaction is all or nothing
  • Consistency: a transaction brings the database from one valid state to another
  • Isolation: concurrently running transactions do not interfere in a way that breaks correctness
  • Durability: once a transaction is committed, its data is persisted even after crashes

You can give a short example about transferring money between accounts to illustrate atomicity, isolation, and consistency.

SQL and Query Questions

Almost every backend interview includes at least one SQL problem. They test if you can think in sets and joins.

Basic CRUD SQL

Typical questions:

You should be comfortable writing simple and readable SQL quickly.

Example schema:

text 
users(id, email, created_at, status)
orders(id, user_id, amount, created_at)

Example answers:

Select users from last 7 days:

sql 
SELECT id, email
FROM users
WHERE created_at >= NOW() - INTERVAL '7 days';

Insert:

sql 
INSERT INTO users (email, created_at, status)
VALUES ('alice@example.com', NOW(), 'active');

Update:

sql 
UPDATE users
SET email = 'new@example.com'
WHERE id = 123;

Delete:

sql 
DELETE FROM users
WHERE status = 'inactive';

You will not always have these exact columns, but the pattern is the same.

JOIN questions

Very common questions:

Table of basic join types:

Join typeReturns
INNER JOINRows where join condition matches in both
LEFT JOINAll rows from left table, plus matching right
RIGHT JOINAll rows from right table, plus matching left
FULL OUTER JOINAll rows from both, with nulls where missing
CROSS JOINCartesian product of two tables

Example question:

Given users(id, email) and orders(id, user_id, amount), write a query to get each user with their total order amount, including users who have no orders.

Answer:

sql 
SELECT
  u.id,
  u.email,
  COALESCE(SUM(o.amount), 0) AS total_amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.email;

Key points to mention:

Aggregation and GROUP BY

Typical questions:

Example queries:

Orders per user:

sql 
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id;

Average order amount by day:

sql 
SELECT
  DATE(created_at) AS day,
  AVG(amount)      AS avg_amount
FROM orders
GROUP BY DATE(created_at)
ORDER BY day;

Top 5 users by total spend:

sql 
SELECT
  user_id,
  SUM(amount) AS total_amount
FROM orders
GROUP BY user_id
ORDER BY total_amount DESC
LIMIT 5;

Common filter patterns

You may be asked things like:

Users with no orders:

sql 
SELECT u.*
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

Users with recent orders:

sql 
SELECT DISTINCT u.*
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.created_at >= NOW() - INTERVAL '30 days';

Be prepared to use:

Schema Design and Relationships

Here you are tested on how you model data.

One-to-many and many-to-many

Typical questions:

Example explanation:

Schema example:

text 
users(id, email, ...)
products(id, name, ...)
orders(id, user_id, created_at, ...)
order_items(
  order_id  REFERENCES orders(id),
  product_id REFERENCES products(id),
  quantity,
  PRIMARY KEY (order_id, product_id)
)

You can mention that the primary key can be (order_id, product_id) if each product appears at most once per order. If not, use a separate id.

Example design question: blog system

Question:

Design the database tables for a simple blog with users, posts, and comments.

Good minimal design:

text 
users(
  id SERIAL PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL
)
posts(
  id SERIAL PRIMARY KEY,
  author_id INTEGER NOT NULL REFERENCES users(id),
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW()
)
comments(
  id SERIAL PRIMARY KEY,
  post_id INTEGER NOT NULL REFERENCES posts(id),
  author_id INTEGER NOT NULL REFERENCES users(id),
  content TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW()
)

Explain:

Example design question: e-commerce cart

Question:

How would you design the schema for a shopping cart?

Simple design:

text 
users(
  id SERIAL PRIMARY KEY,
  ...
)
products(
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  price NUMERIC(10,2) NOT NULL
)
carts(
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  status TEXT NOT NULL  -- 'active', 'converted', etc.
)
cart_items(
  id SERIAL PRIMARY KEY,
  cart_id INTEGER NOT NULL REFERENCES carts(id),
  product_id INTEGER NOT NULL REFERENCES products(id),
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  UNIQUE(cart_id, product_id)
)

Here you can mention:

Indexing and Performance Questions

You will often get high level or practical questions about indexes, query performance, and trade-offs.

What is an index?

Typical question:

Example answer:

An index is a data structure that a database uses to speed up queries on certain columns. You can think of it like the index in a book. Instead of scanning every row, the database looks up the index to quickly find matching rows.

Indexes make WHERE, JOIN, and ORDER BY operations faster on the indexed columns, but they add overhead on writes, since the index must be updated when data changes.

You can mention that a B-tree index is the default in many relational databases, and that there are other types, for example hash, GIN, GiST in PostgreSQL.

When to add an index

Typical question:

Example query:

sql 
SELECT *
FROM orders
WHERE user_id = 123
  AND created_at >= '2024-01-01'
ORDER BY created_at DESC
LIMIT 20;

Good index:

sql 
CREATE INDEX idx_orders_user_created_at
  ON orders(user_id, created_at DESC);

Reasoning you can explain:

Index trade-offs

Typical questions:

Points you can mention:

A good line to use:

Index read-heavy columns that appear often in WHERE, JOIN, or ORDER BY clauses, but be careful not to index everything, because each index slows down writes.

Transactions, Isolation, and Concurrency

Backend roles often involve concurrent access to the database. Interviewers may ask about race conditions and how to avoid them.

Basic transaction concept

Typical question:

Example answer:

A transaction is a group of database operations that are treated as a single unit. Either all of them succeed and are committed, or if something fails they are rolled back so that none of them take effect. This helps keep data consistent, for example when transferring money between accounts.

Isolation levels

You may be asked:

At a junior to mid level, you should know:

You do not need a deep internal explanation. Focus on practical understanding.

Example answer:

Isolation levels control how much concurrent transactions can see each other intermediate changes.

At read committed, which is a common default, each query sees only committed data, so there are no dirty reads, but repeated reads in the same transaction can return different results.

At repeatable read, once you read a row, you keep seeing the same version within the transaction, which prevents non-repeatable reads.

At serializable, the database behaves as if transactions ran one after another, which is the safest but can cause more conflicts and rollbacks.

You can add a one sentence example of a race condition, for example two users trying to update the same inventory count at the same time.

Handling race conditions

Typical scenario question:

You have a table products(id, stock). How do you safely decrement stock when a user makes a purchase, so that you never oversell?

Possible answers:

  1. Use a transaction and row-level lock:
sql 
BEGIN;
SELECT stock
FROM products
WHERE id = 42
FOR UPDATE;
-- Check stock in application code. If enough:
UPDATE products
SET stock = stock - 1
WHERE id = 42;
COMMIT;
  1. Use a single atomic update:
sql 
UPDATE products
SET stock = stock - 1
WHERE id = 42
  AND stock > 0;

Then check the affected rows count in the application:

Explain why this is safe:

Applied and Scenario Questions

These questions test if you can use concepts to solve real problems.

Example: logging and analytics

Question:

You collect user events (page views, clicks) at very high volume for analytics. Would you use a relational database or something else?

A reasonable junior to mid answer:

For very high volume event logging, a traditional relational database can become expensive and hard to scale, especially for write-heavy workloads and large historical data.

A more common approach is to use a log or columnar / analytics store, for example Kafka plus a data warehouse or a time series / columnar database.

For a small system or MVP, it might still be acceptable to use PostgreSQL with partitioned tables, then later move to a more specialized solution as volume grows.

The exact tool is less important than the reasoning: separation between OLTP (transactional) and OLAP (analytics) workloads.

Example: soft delete vs hard delete

Question:

How would you implement soft deletes? When would you use them?

Answer outline:

Example schema:

text 
users(
  id SERIAL PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  deleted_at TIMESTAMP NULL
)

Default query pattern:

sql 
SELECT *
FROM users
WHERE deleted_at IS NULL;

Example: handling large tables

Question:

What would you do if a table becomes very large and queries slow down?

Things you can mention:

You are not expected to be a performance expert, but you should show that you know where to start.

How to Approach Database Questions in Interviews

Beyond knowing content, you should practice how to think aloud. Here are practical strategies.

Clarify requirements

When given a schema or design question, ask:

This shows that you care about trade-offs.

Example clarification:

For this e-commerce database, roughly how many orders per day do we expect? Are we focusing more on operational queries or analytics?

Think from queries back to schema

A helpful mindset:

“Design the schema so that the most common queries are simple and fast.”

So ask yourself:

Then choose:

Use simple and consistent naming

When you write SQL or schemas during the interview:

Clear code is easier for the interviewer to follow.

Validate your SQL

Even in an interview, quickly check:

Say your checks aloud:

I am doing a LEFT JOIN because I still want users without orders. I will group by u.id, u.email. To ensure users with no orders get 0 instead of null, I will use COALESCE(SUM(o.amount), 0).

This reassures the interviewer that you understand the result shape.

Practice Ideas Before Interviews

To feel comfortable with database questions, you can practice in a focused way.

Practice SQL

Pick any SQL-friendly environment, for example:

Then:

  1. Create 3–4 tables, for example users, orders, order_items, products.
  2. Write queries for:
    • Users with no orders
    • Top N spending users
    • Average order size per month
    • Products that were never ordered
    • Last order per user
  3. Try to solve each in more than one way, for example with:
    • JOIN + GROUP BY
    • Subqueries
    • NOT EXISTS

Practice schema design

Pick small domains:

For each:

  1. List the main entities and relationships.
  2. Identify one-to-many and many-to-many.
  3. Draw tables with primary and foreign keys.
  4. List 3–5 most important queries.
  5. Decide which columns to index.

Practice explaining trade-offs

Pick questions like:

Practice answering in 3 parts:

  1. Define both options.
  2. Compare advantages and disadvantages.
  3. Give a simple example decision.

For example:

For an inventory system where consistency is critical and data is structured, I would pick a relational database like PostgreSQL. It gives strong ACID guarantees, transactions, and joins.

For a logging system with very high write throughput and less strict schemas, I might use a NoSQL or analytics store to handle volume and schema evolution more easily.

By focusing on these patterns and practicing both content and communication, you will be ready for most database-related questions in backend interviews.

Views: 7

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 3491 / 24h
Impressum / Imprint
Datenschutz / Data Policy