SQL vs NoSQL — Common Interview Questions and Answers

1. What is the main difference between SQL and NoSQL databases?

SQL (relational): Data in tables with rows and columns; fixed or enforced schema; relationships via foreign keys; query language SQL; strong ACID transactions; vertical scaling (bigger machine) or read replicas.
NoSQL: Various models (document, key-value, column-family, graph); flexible or schema-less; often horizontal scaling (sharding); BASE or eventual consistency in many systems; no single standard query language.

2. What are the main types of NoSQL databases?

Document: Data as documents (e.g. JSON/BSON). Examples: MongoDB, CouchDB.
Key-value: Key → value. Examples: Redis, DynamoDB (simple mode), Memcached.
Column-family (wide-column): Rows with flexible columns; good for analytics. Examples: Cassandra, HBase.
Graph: Nodes and edges for relationships. Examples: Neo4j, Amazon Neptune.

3. When would you choose SQL over NoSQL?

Choose SQL when:

You need complex queries, joins, and aggregations
Data integrity and ACID transactions are critical (e.g. payments, inventory)
Schema is stable and relationships are clear
Reporting and analytics rely on relational structure
Team and tooling are SQL-oriented

4. When would you choose NoSQL over SQL?

Choose NoSQL when:

Schema changes often or structure varies by record
You need horizontal scaling and high write throughput
Data is document-shaped (e.g. configs, profiles) and fits one “document”
You need caching, sessions, or simple key-value access (Redis)
You model graphs (social, recommendations) and use a graph DB
Availability and partition tolerance matter more than strong consistency (CAP)

5. What does ACID mean?

Atomicity: Transaction either fully commits or fully rolls back.
Consistency: Database stays in valid state; constraints hold.
Isolation: Concurrent transactions don’t see each other’s partial state (at some isolation level).
Durability: Committed data survives crashes.

SQL databases are designed for ACID; many NoSQL systems relax consistency (e.g. eventual consistency) for availability and scale.

6. What does BASE mean (in the context of NoSQL)?

Basically Available: System is available most of the time.
Soft state: State can change without new input (e.g. replication lag).
Eventually consistent: Replicas will agree after some time if writes stop.

BASE is often contrasted with ACID: prioritizes availability and scale over strong consistency.

7. What is CAP theorem and how does it affect SQL vs NoSQL?

CAP: of Consistency, Availability, and Partition tolerance, you can only fully guarantee two when there is a network partition.

Partition tolerance is usually required in distributed systems.
So the choice is often CP (consistent but may be unavailable) vs AP (available but may be inconsistent temporarily).

Many SQL systems lean CP; many NoSQL systems (e.g. DynamoDB, Cassandra) tune for AP and eventual consistency.

8. How does scaling differ between SQL and NoSQL?

SQL: Traditionally vertical scaling (bigger server); read replicas for read scaling; sharding is possible but complex (joins, transactions across shards).
NoSQL: Designed for horizontal scaling; sharding and distribution are built in; no joins across shards, so data is often denormalized or duplicated.

9. What are advantages of the document model (e.g. MongoDB)?

Flexible schema: Add or omit fields per document; good for evolving or heterogeneous data.
Natural mapping to objects (e.g. JSON); less ORM mapping.
Embedding: Related data in one document reduces joins for read-heavy, access-by-document workloads.
Horizontal scaling via sharding.

Drawbacks: Duplication if same data is embedded in many documents; no joins; need to manage schema in application.

10. What are advantages of the key-value model (e.g. Redis)?

Very fast reads/writes; in-memory option.
Simple model: get/set by key; good for cache, sessions, queues, feature flags.
Rich structures in Redis: hashes, lists, sets, sorted sets, streams.

Limitation: No query by value or relations; use for access by key or simple patterns.

11. When would you use a graph database?

Use a graph DB (e.g. Neo4j) when:

Data is about relationships (social graph, recommendations, fraud rings, knowledge graphs).
You run path or pattern queries (e.g. “friends of friends”, “shortest path”, “all nodes connected to X”).
Relational DBs need many joins and become slow or complex for these queries.

12. Can you use SQL and NoSQL together?

Yes. Common patterns:

SQL for core transactional data (users, orders, payments); NoSQL for cache (Redis), search (Elasticsearch), or documents (MongoDB).
CQRS: Write to one store (e.g. SQL), read from another (e.g. denormalized in NoSQL or cache).
Polyglot persistence: Use the best store per use case (e.g. PostgreSQL + Redis + Elasticsearch).

13. What is denormalization and why is it common in NoSQL?

Denormalization means storing redundant or duplicated data to avoid joins and to keep related data in one place (e.g. one document or one partition). In NoSQL, joins across shards are expensive or not supported, so data is often denormalized. Trade-off: faster reads and simpler scaling vs more storage and harder updates (multiple places to change).

14. What are pros and cons of a flexible schema (NoSQL)?

Pros:

Schema changes without migrations; different “shapes” in same collection.
Good for rapid iteration and semi-structured data.

Cons:

No DB-level enforcement; invalid data possible.
Application must handle multiple shapes; reporting and queries get harder.
Need discipline (e.g. application-level schema or validation) to avoid chaos.

15. How do you decide between PostgreSQL and MongoDB for a new project?

PostgreSQL if: You have clear entities and relations, need transactions and integrity, complex queries and reporting, or team prefers SQL and fixed schema.
MongoDB if: Data is document-like and variable, you want flexible schema and horizontal scaling, and access is mostly by document or simple filters rather than complex joins.

Hybrid is common: PostgreSQL for core data, MongoDB (or another store) for specific use cases (e.g. logs, content, search).

Interview angle

“SQL or NoSQL?” - the wrong framing. Ask about the access pattern, the consistency requirement and the shape of the data. Relational is the safe default because ad-hoc queries and joins stay possible; NoSQL wins when you know the access pattern up front and need its specific scaling model.
“When is a document store genuinely better?” - when the aggregate is read and written whole, the schema varies per document, and you don’t need cross-document joins. Storing a relational model in documents and then joining in application code is the common mistake.
“Does NoSQL scale better?” - it scales writes horizontally more easily, at the cost of joins, transactions and query flexibility. Modern Postgres with read replicas and partitioning handles far more than people assume, so exhaust that before switching.
“Can you mix them?” - yes, and it’s common: Postgres as the system of record, Redis for caching, Elasticsearch for search, an object store for blobs. The trade is operational surface and keeping them consistent.