SQL vs NoSQL interview questions, with answers

The questions, with answers

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

   A relational (SQL) database stores data in tables with a fixed schema — declared columns, types and constraints — related through keys and queried with SQL, and it enforces integrity and ACID transactions at the database level. NoSQL is not one thing but a family of non-relational stores — document, key-value, wide-column, graph — that trade some of that structure for a flexible or absent schema, a data model shaped for one access pattern, and easier horizontal scaling. The one-line contrast: SQL organises data by its structure and lets you query it any way later; NoSQL organises data by how it will be read and asks you to know that in advance.

2. 2.What are the main types of NoSQL database, and what is each for?

   Four families. Document stores (MongoDB, CouchDB) keep JSON-like documents with fields that can vary per record — good for content, catalogues and user profiles. Key-value stores (Redis, DynamoDB in its simplest use) map a key to an opaque value, giving the fastest possible lookup for caching, sessions and counters. Wide-column stores (Cassandra, HBase) hold rows with dynamic columns partitioned across many nodes, built for very high write volumes such as time series and event logs. Graph databases (Neo4j) store nodes and edges and answer relationship queries — friends of friends, shortest path — that would need many self-joins in SQL. Name the family and one example when asked; that is usually the whole marking scheme.

3. 3.What does "schema-less" really mean, and is it an advantage?

   It means the database does not enforce a structure: two documents in the same collection can have different fields, and adding a field needs no migration. The schema has not disappeared — it has moved into the application code that reads the documents, which now has to cope with every shape it might meet, including the ones written by last year's version of the code. That is an advantage during rapid iteration and for genuinely irregular data, and a liability when many services share the data or when a field's meaning drifts. Modern practice narrows the gap from both sides: MongoDB offers schema validation, and PostgreSQL stores JSON columns with indexes, so "schema-less" is a dial, not a category.

4. 4.How do SQL and NoSQL databases scale differently?

   Relational databases scale up first — a bigger machine — and scale reads out with replicas; scaling writes out means sharding, which most relational systems leave to you and which makes cross-shard joins and transactions painful. Most NoSQL stores were designed to scale out from the start: data is partitioned by key across nodes, writes go to the node that owns the key, and the query model is restricted to what a single partition can answer cheaply, which is why they discourage joins. The honest caveat: a single PostgreSQL server handles more than most applications will ever see, and distributed SQL systems (CockroachDB, Spanner, Vitess) now shard relational data too, so "NoSQL because we need scale" is a claim to back with numbers.

5. 5.Do NoSQL databases support transactions?

   Increasingly, but not as a default assumption. Relational databases give ACID transactions across any rows and tables. Early NoSQL systems offered atomicity only for a single document or row, on the grounds that a well-designed document holds everything one operation needs. That is still the sweet spot — a document store is at its best when one write updates one document — but MongoDB has supported multi-document transactions since 4.0, and DynamoDB and Cassandra offer limited transactional or lightweight-transaction features. The interview answer: if your business operations regularly need atomic changes across several entities, that is a signal toward a relational database, or toward modelling the entities as one document.

6. 6.When would you embed related data in a document, and when would you reference it?

   Embed when the related data is always read with its parent, belongs to it, and is bounded in size: an order's line items live inside the order document, so one read returns the whole order and one write updates it atomically. Reference — store an id and look the other document up — when the related data is shared by many parents (a product referenced by thousands of orders), changes independently, or grows without bound (a user's comments), because embedding would duplicate updates or produce documents that keep growing. This is the document-model version of normalisation: embedding is a deliberate denormalisation for read speed, and the question is testing whether you know what it costs.

7. 7.When is NoSQL the wrong choice?

   When the data is relational and the queries are not known in advance. Reporting and analytics that join, group and filter across many entities, ad hoc questions from the business, financial records that need constraints and multi-row transactions, and small applications that would run comfortably on one relational server all fit SQL better. Choosing a document store and then discovering you need joins leads to either duplicating data everywhere or doing the joins in application code, both of which are worse than the relational database you avoided. The mature answer is that most systems use both: a relational database as the system of record, with a cache or a specialised store beside it for the workload that needs it.

8. 8.How does the CAP theorem come into a SQL vs NoSQL decision?

   Only for distributed deployments, and mostly as vocabulary. A single-node relational database is not a distributed system, so CAP does not apply to it — it is consistent and available until the machine fails. Once data is replicated across nodes, every system must decide what to do during a network partition: relational and CP-style stores refuse some operations to stay consistent, while AP-style NoSQL stores such as Cassandra and DynamoDB keep serving and reconcile later, offering eventual consistency. Many NoSQL systems make that choice tunable per request. The point to make: CAP is a property of a deployment's configuration, not of "SQL" or "NoSQL" as categories, and the more useful question is what consistency each operation in your application actually needs.

How the diagnostic asks it

One question from the DBMS bank, exactly as a sitting would show it. The bank has 4 on sql vs nosql and 60 across DBMS.