SQL vs NoSQL Interview Questions

Relational databases keep tables with an enforced schema, joins in SQL and ACID transactions across tables. NoSQL stores use documents, key-value pairs, wide columns or graphs, usually with a flexible schema, few joins and per-document transactions, and are often built to scale out. The line is blurry: PostgreSQL has jsonb, and MongoDB has had multi-document transactions since 4.0.

Four families: key-value (Redis, DynamoDB) for caches and sessions, document (MongoDB, Firestore) for nested records, wide-column (Cassandra, HBase) for huge write volumes read by key and time range, and graph (Neo4j) for relationships of variable depth. Pick from the queries: a profile by id is key-value or document, friends of friends is a graph, and joins across many entities with strong consistency point back to SQL.

During a network partition, a distributed store must give up either consistency (every read sees the latest write; linearizability, not ACID's C) or availability (every live node answers). Partitions always happen, so the real choice is CP, which refuses requests to stay consistent (ZooKeeper, etcd, HBase), or AP, which answers from any replica and reconciles later (Cassandra, DynamoDB by default). Follow-up: what is PACELC? Without a partition you still trade latency against consistency.

BASE (Basically Available, Soft state, Eventually consistent) systems keep answering and let replicas disagree for a while; ACID transactions are all or nothing, and reads see committed data. Under BASE a read after your write may return the old value. Many stores let you choose per request: with N replicas, R + W > N makes every read overlap the latest write.

Choose NoSQL for a simple, known access pattern at a scale or shape that suits it: writes beyond one node (Cassandra), access by key only (sessions, caches), nested data read whole, or deep relationship queries (a graph). Default to relational when data is related, queries will change, or correctness across entities matters. "No schema" only moves the schema into your code. Many systems use both.

Yes. PostgreSQL (jsonb), MySQL (JSON), SQL Server and SQLite store JSON and query inside it:

CREATE TABLE products (id INTEGER PRIMARY KEY, doc TEXT);
INSERT INTO products VALUES
  (1, '{"name": "Laptop", "price": 55000, "tags": ["electronics", "work"]}'),
  (2, '{"name": "Desk",   "price": 8000,  "tags": ["furniture", "work"]}');

SELECT p.id, json_extract(p.doc, '$.name') AS name, t.value AS tag
FROM products p, json_each(p.doc, '$.tags') t
WHERE json_extract(p.doc, '$.price') > 10000;

json_extract reads a field and json_each turns an array into rows. Use JSON for variable attributes and normal columns for what you filter, join or constrain on; core data in JSON loses types, foreign keys and simple indexes. See JSON support.

Replication copies all the data to several servers for availability and read scaling; sharding splits the data across servers for write scaling and size. The shard key should spread load and keep most queries on one shard (tenant_id); a bad one makes hot shards. Range sharding keeps range scans but heats recent keys; hash sharding spreads evenly but loses them. Cross-shard joins and transactions become app work, and uniqueness holds only within a shard.

Vertical scaling (scaling up) moves the database to a bigger machine; horizontal scaling (scaling out) adds machines and spreads the work. Scaling up needs no code change but has a ceiling and one point of failure. Scaling out uses read replicas for reads and sharding for writes, at the cost of replication lag and queries that span nodes. Relational databases usually scale up first; Cassandra and DynamoDB were built to scale out.

Embed data that is read with its parent and belongs to it, such as the lines of an order. Reference data that is shared, updated on its own, or grows without limit, such as products or a post's comments.

{
  "_id": 501,
  "customer": "Asha",
  "lines": [{ "productId": 42, "name": "Pen", "qty": 2, "price": 20 }]
}

The order references the product by productId but copies the name and price as they were at purchase. Embedding gives one read with no join, but a document has a size limit (16 MB in MongoDB) and copied data must be kept in sync.

Consistent hashing places servers and keys on one hash ring, and each key belongs to the first server clockwise from its hash. Adding or removing a server moves only the keys in its arc, about 1/N of them, while hash(key) % N remaps nearly every key when N changes. Each server gets many virtual nodes on the ring to even out the load. Cassandra and DynamoDB partition data this way, and cache clients use it to spread keys across servers.