Contents

1. 1. Introduction
2. 2. Database fundamentals every full-stack candidate should know
3. 3. SQL database interview questions and answers
4. 4. SQL queries interview: practical examples
5. 5. MongoDB questions and answers
6. 6. Data modeling: normalization, denormalization, and schema design
7. 7. Indexing and query performance
8. 8. Transactions, concurrency, and ACID
9. 9. Database security and reliability
10. 10. Database integration in a full-stack application
11. 11. Scenario-based interview questions
12. 12. Quick revision checklist
13. 13. Conclusion
14. References
15. Reports

1. Introduction

A strong full-stack developer is expected to understand more than frontend components and backend APIs. The database is where application state becomes persistent, so interviewers often test whether a candidate can design data correctly, write efficient queries, protect consistency, and connect the database safely to an API.

This guide focuses on database interview questions from a full-stack perspective. It combines relational SQL concepts, MongoDB questions, practical SQL queries, data modeling, indexes, transactions, and web-dev data storage decisions. The goal is not only to memorize definitions but to explain why a particular approach is appropriate.

A useful interview pattern is: define the concept → give a small example → explain the trade-off → connect it to a real application.

2. Database fundamentals every full-stack candidate should know

Relational vs. document databases

Relational databases store structured data in tables with rows and columns and commonly use SQL. Relationships are represented through keys and joins. Document databases such as MongoDB store records as flexible documents, which can contain nested objects and arrays. The right choice depends on access patterns, consistency requirements, schema flexibility, scale, and team expertise—not on the idea that one model is always better.

Area	SQL / Relational	MongoDB / Document	Interview point
Structure	Tables, rows, columns	Documents, collections	Choose based on data shape and access patterns
Relationships	Foreign keys + JOINs	Embedding or references	Explain why you embed or reference
Schema	Usually strongly defined	Flexible document model	Flexibility still needs deliberate design
Queries	SQL	MongoDB query language / aggregation	Know filtering, sorting, grouping and aggregation
Transactions	Widely used	Supported for multi-document work	Discuss consistency vs. performance trade-offs

Figure 2. Conceptual comparison of relational and document data models.

Core terms to explain clearly

• Primary key: a column or set of columns that uniquely identifies a row.
• Foreign key: a column or set of columns that references a key in another table and helps enforce relationships.
• Candidate key: a minimal set of attributes that can uniquely identify a record.
• Composite key: a key made from more than one column.
• Constraint: a rule such as PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, or CHECK that protects data quality.
• Index: an auxiliary data structure that can speed up reads, but generally adds storage and write-maintenance cost.
• Transaction: a logical unit of work that should preserve required consistency rules.

3. SQL database interview questions and answers

Q1. What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping. HAVING filters groups after GROUP BY and is commonly used with aggregate conditions. A simple way to remember it is: WHERE filters rows; HAVING filters grouped results.

SELECT department, COUNT(*) AS employees
FROM employees
WHERE status = ‘active’
GROUP BY department
HAVING COUNT(*) >= 5;

Q2. What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns rows where the join condition matches on both sides. LEFT JOIN keeps every row from the left table and adds matching data from the right table; unmatched right-side columns become NULL. Use LEFT JOIN when the absence of a related record is still meaningful.

SELECT u.id, u.name, o.order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

Q3. What is normalization?

Normalization is the process of organizing relational data to reduce unnecessary duplication and update anomalies. In interviews, explain the practical goal: keep facts in an appropriate place and use relationships instead of repeatedly storing the same fact.

Q4. What is denormalization?

Denormalization intentionally duplicates or combines data to make common reads faster or simpler. It can reduce joins, but it increases the risk of inconsistent copies and makes updates more complex. The decision should be driven by measured access patterns.

Q5. What is an index and when should you create one?

An index helps the database find matching rows more efficiently. Good candidates are columns frequently used for filtering, joins, ordering, or uniqueness. Avoid creating indexes blindly because every index consumes resources and can increase write cost.

Q6. What is a primary key vs. UNIQUE constraint?

A primary key identifies the main row identity and is unique; it also cannot be NULL. A UNIQUE constraint enforces uniqueness for another candidate value or business identifier. A table has one primary-key definition, but it can have multiple UNIQUE constraints.

Q7. What are ACID properties?

Atomicity means a transaction is all-or-nothing. Consistency means database rules remain valid. Isolation controls how concurrent transactions interact. Durability means committed changes survive the failures covered by the database’s durability guarantees.

Q8. What is a subquery?

A subquery is a query nested inside another SQL statement. It can appear in places such as WHERE, FROM, or SELECT depending on the database. For complex work, a Common Table Expression (CTE) can make multi-step logic easier to read.

Q9. What is a database view?

A view is a stored query presented like a virtual table. Views can simplify repeated logic and limit which columns or rows users see. A view does not automatically mean that its underlying data is physically stored separately.

Q10. What is the N+1 query problem?

N+1 occurs when an application runs one query to fetch a list and then performs another query for each item. This can create hundreds of round trips. Typical fixes include JOINs, batching, eager loading, aggregation, or carefully designed API queries.

Figure 3. A simplified SQL query lifecycle for interview discussion.

4. SQL queries interview: practical examples

Interviewers often give a small schema and ask you to write or improve a query. Practice explaining both the result and why the query is correct.

1. Find all active customers

SELECT id, name
FROM customers
WHERE status = ‘active’;

2. Find the top five products by sales

SELECT product_id, SUM(amount) AS total_sales
FROM order_items
GROUP BY product_id
ORDER BY total_sales DESC
LIMIT 5;

3. Find customers with no orders

SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

4. Find duplicate email addresses

SELECT email, COUNT(*) AS occurrences
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

5. Get the second-highest salary

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

6. Calculate average order value

SELECT AVG(order_total) AS average_order_value
FROM orders;

7. Use a CTE to calculate department totals

WITH dept_totals AS (
  SELECT department_id, SUM(salary) AS total_salary
  FROM employees
  GROUP BY department_id
)
SELECT * FROM dept_totals
ORDER BY total_salary DESC;

Interview tip: do not rush into writing SQL. First restate the desired output, identify the tables and relationships, then decide whether you need filtering, joining, grouping, ordering, a window function, or a subquery.

5. MongoDB questions and answers

Q1. What is MongoDB?

MongoDB is a document-oriented database that stores records as BSON documents inside collections. Its flexible model supports nested objects and arrays, making it useful when application data naturally maps to documents.

Q2. What is a collection?

A collection is a grouping of MongoDB documents. It is broadly comparable to a table in a relational database, but the document model allows records in a collection to have flexible shapes.

Q3. Embedding vs. referencing—what is the difference?

Embedding places related data inside the same document and can make reads simple and atomic for that document. Referencing stores related data separately and can be preferable when related data is large, shared, or independently updated. Choose based on read/write patterns and data lifecycle.

Q4. What is the aggregation pipeline?

Aggregation processes documents through a sequence of stages such as $match, $group, $sort, $project, and $lookup. It is useful for reporting, calculations, filtering, and reshaping data close to the database.

Q5. How do indexes help MongoDB?

Indexes allow MongoDB to avoid scanning every document for supported query patterns. They can improve read performance, but indexes also consume resources and can slow writes because index entries must be maintained.

Q6. What is a compound index?

A compound index covers multiple fields in a defined order. It is useful when common queries filter or sort on a combination of fields. The order of fields matters, so design the index around actual query patterns.

Q7. Does MongoDB support transactions?

Yes. MongoDB supports multi-document transactions, including across multiple collections and, in supported deployments, across shards. Transactions are useful when multiple changes must succeed together, but good schema design can often reduce the need for them.

Q8. How would you optimize a slow MongoDB query?

Start with the actual query pattern, inspect the execution plan, check whether useful indexes exist, reduce unnecessary fields, filter early in aggregation pipelines, and review schema design. Measure before and after changes rather than adding indexes blindly.

6. Data modeling: normalization, denormalization, and schema design

Data modeling is one of the most valuable areas in database interview questions because it tests whether you can translate business requirements into a maintainable structure.

A practical modeling process

16. Identify entities: users, products, orders, payments, courses, etc.
17. Identify attributes and business rules for each entity.
18. Identify relationships and cardinality: one-to-one, one-to-many, or many-to-many.
19. List the most common read and write operations before finalizing the schema.
20. Choose normalization or embedding based on consistency, query patterns, data size, and update frequency.
21. Add constraints and indexes that support the real workload.
22. Test the model with realistic queries and expected growth.

Example: e-commerce order system

A relational design might separate customers, products, orders, and order_items. This prevents product details from being duplicated in every order line. A document design could embed order items inside an order document when the items are primarily read together and the document remains within operational limits.

The interview-winning answer is not simply ‘SQL is normalized and MongoDB is denormalized.’ Explain the workload and trade-offs.

7. Indexing and query performance

Indexes are a common interview topic because performance problems often appear when data grows. An index can reduce the amount of data the database must examine for a supported query, but indexes are not free.

• Index columns that frequently participate in selective filters, joins, sorting, or uniqueness checks.
• Review composite-index column order against the application’s query patterns.
• Avoid duplicate or unused indexes.
• Remember that indexes can increase storage use and write overhead.
• Use execution plans or database-specific performance tools to validate whether an index is actually helping.
• For MongoDB aggregation, early filtering and suitable indexes can reduce the amount of data later stages process.

A simple performance checklist

Check	Question to ask	Typical action
Query shape	What filters, joins, and sorts are common?	Design indexes around real queries.
Result size	Do we really need every column/row?	Select only required fields and paginate.
Execution plan	Is the database scanning too much data?	Inspect EXPLAIN / execution statistics.
Write cost	Will the new index slow inserts/updates?	Remove low-value indexes and measure.
Application layer	Are we making too many round trips?	Batch, join, aggregate, or redesign API access.

Figure 4. Suggested study focus for database interview preparation.

8. Transactions, concurrency, and ACID

A transaction groups related database operations so that the required business rules are preserved. A strong candidate should be able to explain what happens when two users update the same data or when the application fails midway through a workflow.

• Atomicity: either the transaction’s required operations commit or the transaction is rolled back.
• Consistency: constraints and business invariants should remain valid after the transaction.
• Isolation: concurrent work should not create unacceptable intermediate or conflicting views.
• Durability: committed data should survive the failure scenarios covered by the database’s durability design.

Example interview scenario: payment + order

Suppose an application creates an order and records a payment. If the order is created but the payment record fails, the system may become inconsistent. A transaction can be appropriate when both changes must succeed together. In a distributed architecture, however, payment providers may be external systems, so the design may require idempotency keys, retries, outbox/event patterns, reconciliation, and careful failure handling rather than one database transaction alone.

9. Database security and reliability

• Use parameterized queries or prepared statements instead of building SQL with untrusted string concatenation.
• Apply least privilege: application accounts should receive only the permissions they need.
• Store secrets outside source code and use appropriate secret-management mechanisms.
• Encrypt data in transit and use encryption-at-rest features where required by the environment.
• Back up important data and regularly test restoration; a backup that cannot be restored is not a dependable recovery plan.
• Log important database/application events without exposing passwords, tokens, or sensitive personal information.
• Validate inputs at the API boundary, but do not rely on validation alone for database security.

10. Database integration in a full-stack application

The database should normally sit behind the backend/API layer rather than being exposed directly to the browser. The frontend sends an HTTP request, the backend authenticates and validates it, performs authorized database operations, and returns a controlled response.

Figure 4. Database access should be controlled through the application/backend layer.

Where caching fits

Caching can reduce repeated work and improve response time. Browser and HTTP caches can reuse responses when the caching rules allow it. For database-heavy applications, an application cache can also reduce repeated database reads. The key interview point is freshness: a cache is useful only when the system can safely tolerate its data being reused for the defined period.

11. Scenario-based interview questions

Your API is slow after the database grows from 10,000 to 10 million rows. What do you do?

Measure first. Inspect slow queries and execution plans, verify indexes, reduce unnecessary result sets, paginate, review joins/aggregations, and check whether the application is generating N+1 queries. Then benchmark the proposed change.

A user submits the same payment request twice. How do you prevent duplicate orders?

Use an idempotency key or another unique business identifier, enforce uniqueness at the database layer where appropriate, and make the application safe to retry.

A MongoDB collection is growing quickly. What would you review?

Review document size and shape, access patterns, indexes, shard/replication strategy where applicable, archival/lifecycle rules, and the most expensive queries. Do not assume that adding indexes alone will solve growth problems.

When would you choose SQL over MongoDB?

Choose SQL when strong relational integrity, complex joins, mature transaction semantics, structured reporting, or well-understood relational workflows are central. The answer should always reference the application’s actual requirements.

When would you choose MongoDB?

MongoDB can be a strong choice when document-shaped data, evolving schemas, nested structures, and access patterns that map naturally to documents are important. Again, explain the workload rather than presenting it as universally better.

12. Quick revision checklist

☐ Explain primary key, foreign key, UNIQUE, NULL, and constraints.

☐ Know INNER, LEFT, RIGHT, and FULL JOIN concepts.

☐ Practice WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, subqueries, and CTEs.

☐ Understand normalization and denormalization with a real example.

☐ Explain indexes and their read/write trade-off.

☐ Know ACID and why transactions matter.

☐ Understand MongoDB collections, documents, embedding, referencing, aggregation, and indexes.

☐ Be able to discuss the N+1 problem and pagination.

☐ Know how a backend/API safely accesses a database.

☐ Be ready to explain a performance-debugging process rather than naming one magic fix.

13. Conclusion

Database interview questions are really testing engineering judgment. A strong full-stack candidate can write SQL or MongoDB queries, but also understands data modeling, performance, consistency, security, and the application context in which those queries run.

For interview preparation, focus on explaining trade-offs with small examples. If you can take a business requirement, design a sensible schema, write a correct query, explain how you would index it, and describe how the API handles failures and security, you are demonstrating full-stack database maturity.

References

[1] MySQL, “SELECT Statement,” MySQL 8.4 Reference Manual. [Online]. Available:
https://dev.mysql.com/doc/refman/8.4/en/select.html

[2] PostgreSQL, “SELECT,” PostgreSQL Documentation. [Online]. Available:
https://www.postgresql.org/docs/current/sql-select.html

[3] MongoDB, “Best Practices for Data Modeling in MongoDB,” MongoDB Documentation. [Online]. Available:
https://www.mongodb.com/docs/manual/data-modeling/best-practices/

[4] MongoDB, “Indexes,” MongoDB Database Manual. [Online]. Available:
https://www.mongodb.com/docs/manual/indexes/

[5] MongoDB, “db.collection.aggregate(),” MongoDB Database Manual. [Online]. Available:
https://www.mongodb.com/docs/manual/reference/method/db.collection.aggregate/

[6] MongoDB, “Enforce Data Consistency with Transactions,” MongoDB Documentation. [Online]. Available:
https://www.mongodb.com/docs/v8.0/data-modeling/enforce-consistency/transactions/

[7] Mozilla Developer Network, “HTTP caching,” MDN Web Docs. [Online]. Available:
https://developer.mozilla.org/en-US/docs/Web/HTTP/Guides/Caching

[8] Mozilla Developer Network, “Cache-Control header,” MDN Web Docs. [Online]. Available:
https://developer.mozilla.org/en-US/docs/Web/HTTP/Reference/Headers/Cache-Control

Frequently Asked Questions

1. What are the most common database interview questions?

Common database interview questions cover primary keys, foreign keys, SQL queries, joins, normalization, indexing, transactions, and database security. Interviewers may also ask about MongoDB, query optimization, and practical database design scenarios.

2. How should freshers prepare for database interview questions?

Freshers should learn database fundamentals, practice SQL queries, understand relational and document databases, and study indexing and transactions. Building a small application that connects a database to a backend API can also provide valuable practical experience.

3. What is the difference between SQL and NoSQL databases?

SQL databases organize data into relational tables and support structured queries and relationships. NoSQL databases use models such as documents, key-value pairs, and graphs. The best choice depends on data structure, consistency requirements, application needs, and query patterns.

4. What is a primary key in a database?

A primary key uniquely identifies each row in a database table. It cannot contain NULL values, and each table has one primary-key definition. A primary key can consist of one column or multiple columns.

5. What is the difference between a primary key and a foreign key?

A primary key uniquely identifies a record within its table. A foreign key references a key in another table, or sometimes the same table, to represent a relationship and help enforce referential integrity.

6. What is database normalization?

Database normalization organizes relational data to reduce unnecessary duplication and prevent insertion, update, and deletion anomalies. It divides data into related tables and establishes relationships using appropriate keys.

7. What is the difference between WHERE and HAVING in SQL?

WHERE filters individual rows before grouping, while HAVING filters groups after GROUP BY. WHERE is commonly used for conditions on individual records, whereas HAVING is useful for conditions involving aggregate functions such as COUNT() and SUM().

8. What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns rows with matching values in both tables. LEFT JOIN returns every row from the left table and includes matching rows from the right table. When no match exists, the right-side columns contain NULL values.

9. What is a database index, and why is it important?

A database index helps the database locate records more efficiently without scanning the entire table or collection. Indexes can improve query performance, but they consume storage and may slow down insert, update, and delete operations.

10. What are ACID properties in database management?

ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties describe important transaction guarantees that help maintain data integrity, particularly when multiple operations must succeed together or when concurrent transactions interact.

11. What is MongoDB, and when should it be used?

MongoDB is a document-oriented database that stores data as BSON documents in collections. It can be suitable for applications with nested data structures, evolving document schemas, and access patterns that naturally fit the document model.

12. What is the difference between embedding and referencing in MongoDB?

Embedding stores related information within the same document, making certain reads simpler. Referencing stores related data separately and connects documents through identifiers. The choice depends on data size, update frequency, relationships, and application query patterns.

13. How can you optimize a slow database query?

Start by measuring query performance and inspecting the execution plan. Check indexes, reduce unnecessary columns, optimize joins and filters, paginate large result sets, and investigate excessive database calls. Validate improvements with performance measurements.

14. What is a database transaction, and why is it important?

A database transaction groups related operations into a logical unit of work. Transactions help maintain consistency when multiple changes must succeed or fail together, such as updating account balances or recording related order information.

15. How are databases used in full-stack web development?

In full-stack applications, the frontend sends requests to a backend API, which validates inputs, checks permissions, and performs authorized database operations. The backend then returns an appropriate response to the frontend. This architecture helps protect database credentials and control access to application data.

Citations & References

[1] MySQL, “SELECT Statement,” MySQL 8.4 Reference Manual. [Online]. Available:
https://dev.mysql.com/doc/refman/8.4/en/select.html
[2] PostgreSQL, “SELECT,” PostgreSQL Documentation. [Online]. Available:
https://www.postgresql.org/docs/current/sql-select.html
[3] MongoDB, “Best Practices for Data Modeling in MongoDB,” MongoDB Documentation. [Online]. Available:
https://www.mongodb.com/docs/manual/data-modeling/best-practices/
[4] MongoDB, “Indexes,” MongoDB Database Manual. [Online]. Available:
https://www.mongodb.com/docs/manual/indexes/
[5] MongoDB, “db.collection.aggregate(),” MongoDB Database Manual. [Online]. Available:
https://www.mongodb.com/docs/manual/reference/method/db.collection.aggregate/
[6] MongoDB, “Enforce Data Consistency with Transactions,” MongoDB Documentation. [Online]. Available:
https://www.mongodb.com/docs/v8.0/data-modeling/enforce-consistency/transactions/
[7] Mozilla Developer Network, “HTTP caching,” MDN Web Docs. [Online]. Available:
https://developer.mozilla.org/en-US/docs/Web/HTTP/Guides/Caching
[8] Mozilla Developer Network, “Cache-Control header,” MDN Web Docs. [Online]. Available:
https://developer.mozilla.org/en-US/docs/Web/HTTP/Reference/Headers/Cache-Control

Know more..