Databases & SQL Interview Questions: SQL vs NoSQL

The core distinction is that relational SQL databases require a fixed, predefined schema built from tables, rows, and columns, while NoSQL databases typically offer flexible or schema-less models such as documents, key-value pairs, wide columns, or graphs. The statement about enforcing a fixed schema of tables, rows, and columns is the one that matches this. The statement claiming SQL stores JSON documents and NoSQL uses tables reverses the two storage models. The claim that NoSQL always requires a strict predefined schema is wrong, since NoSQL systems generally do not impose one, and the claim that SQL has no schema is also wrong. The statement that only NoSQL supports ACID is false, because relational databases are the ones traditionally known for ACID transactions and many NoSQL systems offer transactions too.

SQL databases are built on the relational model, introduced by E.F. Codd, which organizes data into tables (relations) of rows and columns. They use SQL (Structured Query Language) — a declarative language — to create schemas (DDL), insert and query data (DML), and manage access (DCL). This is the foundational definition that separates relational from non-relational (NoSQL) databases.

NoSQL databases as a group trade strict schema enforcement and normalized table structures for flexible data models (key-value, document, column-family, graph) and horizontal scalability across commodity hardware. The other choices describe hallmarks of relational SQL databases (normalized tables with foreign keys, a fixed enforced schema) or are false, since many NoSQL systems do not use SQL at all.

Relational databases enforce a predefined schema (tables with fixed columns, data types, and constraints) and are built around ACID (Atomicity, Consistency, Isolation, Durability) guarantees as a core design principle. Document-oriented NoSQL databases like MongoDB are schema-flexible: documents in the same collection can have entirely different fields, making them well-suited for heterogeneous or rapidly evolving data. It is worth noting that MongoDB has supported multi-document ACID transactions since version 4.0 (and across sharded clusters since 4.2), so the primary day-to-day distinction between the two is the rigid relational schema versus the flexible document model — not consistency guarantees. The other options are factually incorrect: SQL databases use tables (not key-value pairs), NoSQL collections can hold heterogeneous documents, and SQL databases are specifically designed to express relationships via foreign keys and joins.

This statement is false. While many early NoSQL databases sacrificed ACID compliance for scalability and availability, several modern NoSQL systems do support ACID transactions. For example, MongoDB (since v4.0) supports multi-document ACID transactions, Google Spanner and CockroachDB offer full ACID guarantees at global scale, and Amazon DynamoDB supports transactions via TransactWriteItems. ACID compliance is therefore not an exclusive property of relational databases.

SQL (relational) databases store data in tables (relations), where every row conforms to the same column schema. The CAP theorem, formulated by Eric Brewer, states a distributed system can guarantee only two of: Consistency, Availability, and Partition Tolerance — understanding this trade-off is central to choosing between SQL and NoSQL. Key-value stores (e.g., Redis), document stores (e.g., MongoDB), and wide-column stores (e.g., Cassandra) are all categories of NoSQL (non-relational) databases, unified by their departure from the strict tabular model of SQL systems.

Document stores such as MongoDB keep each content item as a flexible, nested document (typically JSON/BSON) and do not enforce a fixed schema at the collection level. Articles, videos, and galleries can therefore carry different fields, and developers can add or change fields as the schema evolves without ALTER TABLE migrations, which suits rapid iteration. The distractors are wrong: document stores do not impose one fixed column layout, they do not offer stronger ACID guarantees than relational databases, and they still need indexes for efficient querying.

The statement is true. Relational databases enforce a schema: every table has defined columns and types, and a row that does not conform cannot be inserted. MongoDB collections are schema-less by default, so documents with different fields and structures can be inserted into the same collection without any prior schema definition. Optional schema validation exists in MongoDB but is not required.

The statement that a relational database enforces a fixed schema, which can make it difficult to accommodate events with highly variable fields without significant migration overhead, is correct — relational databases use a fixed schema, and heterogeneous event structures require awkward workarounds (nullable columns, EAV, JSON blobs) or costly migrations. The statement that a relational database with proper indexing and a mature query planner can handle complex ad-hoc aggregation queries more expressively using SQL than most document store query languages is correct — SQL is a mature, expressive standard for aggregations (window functions, CTEs, GROUP BY) that most document store query languages still lag behind. The statement that horizontal sharding of write load is generally simpler to configure in many NoSQL document stores than in traditional single-node relational databases is correct — most NoSQL document databases (e.g., MongoDB, Cassandra) were designed with built-in horizontal scaling, whereas traditional RDBMS require extra tooling (e.g., Vitess, Citus). The claim that document stores like MongoDB automatically outperform column-oriented SQL databases for aggregation queries on hundreds of millions of records is false — column-oriented SQL databases (e.g., ClickHouse, Redshift) are specifically optimized for analytical aggregations and often outperform document stores for such workloads. The claim that document stores sacrifice ACID transaction guarantees entirely and can never offer them, making them unsuitable for any financial event tracking, is false — modern document stores like MongoDB support multi-document ACID transactions since version 4.0, so the blanket statement is inaccurate.

The logical order is: (1) analyze existing query/access patterns first, because NoSQL schema design is query-driven — you must know how data will be read before deciding what to embed or reference; (2) design the document schema based on those patterns; (3) run a transformation/migration script to populate the NoSQL store with the existing data; (4) dual-write and verify correctness by comparing reads between the two stores, which catches data integrity issues safely before full cut-over; (5) finally, cut over and decommission the legacy SQL database only after a stabilization window. Skipping or reordering these steps risks data loss, mismatched schemas, or unrecoverable rollback scenarios.

Multi-entity ACID transactions and ad-hoc joins are exactly what the relational model is built for, so a SQL database is the natural fit here. Document stores shine for self-contained aggregates read/written as a unit, and key-value stores for simple lookups by key — neither gives you cross-entity transactions and rich joins for free. The right answer is workload-driven, not a blanket 'NoSQL is faster'.

The scenario asks for an enforced predefined schema, multi-table joins with aggregation, ACID transactions across related tables, and predictable read patterns. A relational SQL database provides all of these natively: a schema enforced at write time, foreign keys, a cost-based query planner for joins and aggregation, and multi-table transactions. Wide-column stores are tuned for high-throughput writes and partition-keyed access, not multi-table joins or cross-table transactions. Key-value stores offer point lookups with no native join capability. Document stores are built around flexible, self-contained documents, so joins across collections are not their native model and the flexible schema works against the stated need for a fixed one. Only the relational category matches every stated requirement.

The two strategies are named by when schema checking happens. In schema-on-write, data is validated against the schema when it is written, so a relational database that enforces a predefined schema on insert or update fits the blank 'write'. In schema-on-read, data is stored without enforcing a fixed structure and is interpreted when it is read, which is the approach typical of document stores such as MongoDB and fits the blank 'read'. The terms are the standard database-community names for this contrast.

In schema-on-write, the schema is enforced when data is written, so nonconforming data is rejected before it is stored; this is the SQL approach described. In schema-on-read, data is stored without enforcing a schema and structure is interpreted when it is read, which matches the document-store approach described. The blanks therefore take the words write and read.

This statement is true. Relational databases enforce foreign key constraints inside the engine, so an insert or update that would create a reference to a missing parent row is rejected at write time. MongoDB has no built-in foreign key constraint across collections, so the application must check references itself. The $lookup aggregation stage performs a join-like read at query time, but it does not constrain writes, so it provides no write-time integrity guarantee.

Document stores like MongoDB allow schema-less or schema-flexible storage, making evolving JSON documents a natural fit (schema flexibility for evolving JSON is correct). Horizontal multi-region write scaling (via sharding, replica sets, or multi-master modes) is a core architectural advantage over traditional single-primary RDBMSes (easier horizontal write scaling across regions is correct). While modern document stores support multi-document ACID transactions, they add locking and coordination overhead relative to single-document ops — so the claim that multi-document ACID transactions carry no additional performance overhead is false. Document stores lack the rich join optimizer SQL databases have; $lookup is far more limited than SQL JOINs on indexed tables (the claim that ad-hoc multi-collection joins are equally performant and expressive is false). The debit/credit requirement can be met with session transactions, but it is not the document store's natural strength — the relational model with ACID is better suited (multi-document transactions are possible but require careful management and are not the natural strength of document stores is correct).

The CAP theorem is only meaningful in the context of distributed systems experiencing network partitions. A single-node database cannot have a network partition between nodes (there are no other nodes), so saying it is 'CA' conflates 'not distributed' with 'both consistent and available'. In reality, single-node systems simply don't face the partition scenario CAP describes. CP systems (HBase, Zookeeper) refuse requests or block during partitions to preserve consistency. AP systems (Cassandra) continue serving requests during partitions at the cost of returning possibly stale data.

The correct order reflects industry best practice for zero-downtime migrations: First, you must redesign access patterns and key schemas because NoSQL data modelling is fundamentally different from relational normalisation — doing this wrong invalidates every later step. Next, backfill existing historical data into the NoSQL store so it is not lost. Then enable dual-writes so the system remains the source of truth in both stores for new mutations. Reconcile and validate data parity, then promote NoSQL to primary. Finally, after a stabilisation window with monitoring, decommission the legacy SQL database.

The polyglot persistence approach recognizes that MongoDB's document model naturally handles variable schemas, eliminating the EAV anti-pattern and its associated multi-way joins for catalog reads. PostgreSQL retains advantages for ad-hoc analytical queries and cross-entity ACID transactions. Neither 'strictly better' nor 'always preferred' claims are defensible—each technology has domains where it excels. The 50% storage reduction claim is unsubstantiated and implementation-specific.

Key-value stores excel at single-digit millisecond point lookups at massive scale with simple access patterns, horizontal write scalability with key-based partitioning, and flexible or rapidly evolving schemas. Complex many-to-many joins with ACID guarantees and cross-entity ad-hoc analytical aggregations are core strengths of relational databases, not key-value stores—forcing these workloads into a KV model sacrifices the query expressiveness and transactional integrity that relational engines provide.

The quorum intersection property is the reason. When R+W>N, any read set of size R and any write set of size W must share at least one of the N replicas, so the read includes a replica holding the latest acknowledged write and the coordinator can return the newest version. With N=3, R=2, W=2, R+W=4>3, so this holds. With R=1 and W=1, R+W=2≤3, the sets can be disjoint, so a read may hit only replicas that missed the write and return stale data, leaving only eventual consistency. The answer should also note that the overlap guarantee is not full linearizability under concurrent writes or sloppy quorums.

PostgreSQL is the better fit because requirements (1) and (2) demand multi-entity atomic updates and strong consistency, which PostgreSQL's ACID transactions and MVCC provide natively. Requirement (3) involves aggregation across 5+ related entities, where PostgreSQL's relational model and SQL JOINs are far more natural than MongoDB's $lookup pipelines or denormalization. Requirement (4)—custom attributes per category—is addressable in PostgreSQL via JSONB columns, giving schema flexibility without abandoning the relational backbone. MongoDB can handle flexible schemas but trades away the transactional and join-heavy capabilities this workload relies on.

This question tests understanding of how Cassandra's distributed write path differs fundamentally from a relational commit log. Cassandra uses a Log-Structured Merge-tree (LSM-tree) for writes: a write first goes to the in-memory Memtable (and a commit log for durability), and is eventually flushed to an immutable SSTable on disk. SSTables are never updated in-place; compaction merges them. There is no traditional B-tree page-level locking, no row-level mutex in the RDBMS sense, and no two-phase commit by default. The correct order is: (1) Write to commit log (durability), (2) Write to Memtable (in-memory), (3) Memtable flush → SSTable on disk (on threshold), (4) Compaction merges SSTables. This is the canonical Cassandra write path. Options that place the SSTable write before Memtable or skip the commit log are wrong.

This question tests expert-level understanding of data modeling trade-offs between relational and document databases. In a relational (SQL) system, you normalize data to eliminate redundancy and maintain referential integrity via foreign keys, enforced at the engine level. Joins reconstruct denormalized views at query time. In a document store (NoSQL, e.g., MongoDB), the dominant pattern is to embed related data inside a document when the access pattern retrieves it together, avoiding joins entirely—this is 'data locality.' However, embedding causes write amplification when the embedded sub-document is shared across many parents (the 'one-to-squillions' problem), because every parent must be updated. The correct fill-ins are: (1) 'normalization' / 'referential integrity', (2) 'joins' / 'query time', (3) 'embedding' / 'data locality', and (4) 'write amplification' / 'update' to reflect the cost of embedding widely shared sub-documents.

The keyed statement is correct because the two requirements conflict physically. Immediate visibility from every region after acknowledgment is a linearizability requirement, so the write must be replicated to other regions before the client is told it succeeded. Inter-region round trips take tens of milliseconds because of the speed of light and network distance, so an acknowledgment under one millisecond cannot have waited for that propagation. The other statements fail for reasons that are each specific: a single global leader with synchronous cross-region replication must wait for remote regions before acknowledging, so it misses the latency bound; a grow-only counter CRDT acknowledges locally but only converges through anti-entropy, so it misses immediate visibility; and Cassandra's quorum overlap guarantees freshness only when the quorum replicas are reached, which spans regions and incurs the same round-trip delay.