Sql & No Sql

Question 1

Sql dB

Answer 1

Structured Query Language – used to interact with Relational Databases (RDBMS).

Data is stored in tables (rows and columns).

Fixed schema — strict structure.

Popular relational databases are MySQL, Oracle, MS SQL Server, SQLite, Postgres, and MariaDB.

Storage: SQL stores data in tables where each row represents an entity and each column represents a data point about that entity;

Schema: In SQL, each record conforms to a fixed schema, meaning the columns must be decided and chosen before data entry and each row must have data for each column. All rows must have the same columns

Querying: SQL databases use SQL (structured query language) for defining and manipulating the data.

Scalability: In most common situations, SQL databases are vertically scalable, i.e., by increasing the horsepower (higher Memory, CPU, etc.) of the hardware, which can get very expensive. It is possible to scale a relational database across multiple servers, but this is a challenging and time-consuming process.

Reliability or ACID Compliancy (Atomicity, Consistency, Isolation, Durability): The vast majority of relational databases are ACID compliant.

Question 2

No Sql dB

Answer 2

Designed to store and retrieve unstructured, semi-structured, or flexible schema data.

Supports horizontal scaling better than SQL.

Four major types:

Document (e.g. MongoDB)

Key-Value (e.g. Redis)

Column-Family (e.g. Cassandra)

Graph (e.g. Neo4j)

Examples: MongoDB, Redis, Cassandra, DynamoDB

A) A Key-Value Store is a type of NoSQL database where:

Each data item is stored as a pair:

Key: A unique identifier (like a name)

Value: The actual data or object associated with that key

Eg: “user:101” { “name”: “Alice”, “age”: 30 }
“session:abc” “xyz123token”

You use the key to look up the value instantly.

🛠️ Common Use Cases:
a)Caching (e.g., session data, frequently accessed records)
b)Shopping cart data
cUser preferences
d)Token/session management

B) Document databases are a type of NoSQL database that store data as documents, usually in JSON,

Documents are stored in collections (similar to tables in SQL).

Each document is a self-contained unit of data.

Documents can have flexible and dynamic schemas — i.e., each one can look different.

eg: {
“user_id”: 101,
“name”: “Alice”,
“email”: “alice@example.com”,
“orders”: [
{ “item”: “Laptop”, “qty”: 1 },
{ “item”: “Mouse”, “qty”: 2 }
]
}

Structure Comparison:
SQL (Relational) Document DB Equivalent
Table Collection
Row Document
Column Field

🛠️ Use Cases:
a)Content management systems (CMS)
b)User profiles and preferences
c)Product catalogs (e.g., e-commerce)
d)Real-time analytics

Why user profiles etc uses document Db because:
a) Hierarchical data: Can nest prices, images, reviews inside each document.
b) No joins: All info for a product is in one place — fast reads

c) Wide-Column Databases (a.k.a. Column-Family Stores)
✅ Key Concepts:
Column Families:
Think of these like “tables” in RDBMS — but much more flexible. A column family groups related rows that can each have a different set of columns.

Rows with dynamic columns:
In contrast to relational databases, where all rows must have the same columns, here each row can have a different number of columns, or even different column names.

eg:
Column Family: UserActivity

Row Key: user_123
- last_login: 2024-06-10
- page_views: 45

Row Key: user_456
- last_login: 2024-06-08
- video_plays: 12
- likes: 5
Each row can have different columns, and it’s perfectly fine.

Column Family → Like a Table
Example: UserActivity

Row Key → Like the Primary Key in a Table
Example: user_123, user_456

Columns → Flexible Key-Value Pairs under that Row
Each row can have its own set of columns, and they don’t need to be the same across rows.

Example:
For user_123: last_login, page_viwes are columns.

last_login: 2024-06-10

page_views: 45

For user_456: last_login,video_plays and likes are columns.

last_login: 2024-06-08

video_plays: 12

likes: 5

Best Use Cases:
a)Analytics on massive data volumes
b)Time-series data (e.g., sensor logs)
c)Recommendation engines
d)IoT event storage
e)Clickstream or telemetry data

D) Graph databases are purpose-built to store and navigate relationships. Instead of tables or documents, they use:

These databases are used to store data whose relations are best represented in a graph. Data is saved in graph structures with nodes (entities), properties (information about the entities), and lines (connections between the entities).

Nodes → entities (like users, products, locations)

Edges (or Relationships) → connections between nodes (like friend of, bought, lives in)

Properties → key-value pairs that describe nodes and edges

Example (Social Network)
Let’s say we model users and friendships:

Entities:
🧍 Node: User {name: “Alice”}

🧍 Node: User {name: “Bob”}

Relationship:
➡️ Edge: FRIENDS_WITH (from Alice to Bob)

✅ When to Use Graph Databases
Ideal for:

a)Social networks
b)Recommendation engines
c)Fraud detection
d)Network/IT asset management
e)Knowledge graphs

Question 3

Differences between SQL & No SQL

Answer 3

Feature Sql No Sql
Schema Fixed, predefined, Dynamic, flexible
Data Model Tables JSON, key-value,
(rows/columns) Document, Graph
Joins Supported Not typically used
Scalability Vertical scaling Horizontal scaling
Querying Sql Unstructured QueryLan
Best For Structured data, Unstructured/rapidly
complex queries changing data
Examples. MySQL, PostgreSQL MongoDB, Redis,

Feature | SQL (Relational) | NoSQL (Non-Relational) |

Question 4

ACID

Answer 4

ACID (Atomicity, Consistency, Isolation and Durability)

Atomicity: All steps in a transaction succeed or none do.

Why it’s important:
a)Prevents partial updates that can corrupt your data.
b)Ensures data integrity, even in the event of crashes, power failures, or network issues.

eg: The transaction involves two steps:
Withdraw $100 from Account A
Deposit $100 into Account B
If only the first step happens, and the second fails (e.g., due to a crash), money disappears 💸 — that’s bad.

With atomicity:

Both steps succeed → ✅ Transfer complete
Or both steps are rolled back → ❌ No money moves, no corruption

Consistency: Data must always be valid according to rules and constraints.
The database must remain in a valid state after every transaction.
Any transaction should take the database from one consistent state to another.

🔧 Think of it like this:
A consistent state obeys all the rules, constraints, and relationships defined in the database.
If any transaction violates those rules, it must be rejected or rolled back.
eg: “The balance in any account cannot be negative.”

Isolation: Transactions don’t interfere with each other.

This ensures that concurrent transactions execute as if they were happening one at a time, even if they’re happening simultaneously.

Example: Two people transferring money
Let’s say:
Suppose Account X has $100.

User A is transferring $100 from Account X to Y.
User B is transferring $50 from Account X to Z — at the same time.

Without isolation:

Both transactions might read the same balance (e.g., $100 in Account X),
Each might assume there’s enough balance and proceed.
End result: Overdrawn account — data corruption.

With proper isolation:

In this even though concurrent transactions happens, with isolation the database ensures that only one transaction can successfully modify the same row at a time by locking.

Even if requests arrive at the same millisecond:

The DB engine processes instructions one CPU operation at a time

Lock acquisition is atomic

The lock manager guarantees only one winner.

Each transaction sees a consistent snapshot of data.

What actually happens in the real world?

Transactions start concurrently

Reads may overlap

Writes to the same row never overlap

DB enforces a single logical order

Durability: Once committed, data is permanently saved—even after crashes.

Why does it matter?
Imagine:
You transfer $100 from Account A to B,
The database shows “Success”,
But seconds later, the server crashes or loses power…
If Durability isn’t guaranteed, that data could be lost forever.

How databases ensure durability:
🔁 Write-Ahead Logs (WAL): Before making changes to the DB, database logs the action on disk. Disk is a persistent storage—usually the hard drive (HDD).

Important:
A and D are the most commonly supported in NoSQL systems.

C and I are often relaxed in favor of scalability and availability, especially in distributed systems.

Question 5

SQL vs No SQL , which one to use where

Answer 5

Few reasons to choose a SQL database:

a)ACID Compliance for Data Integrity:
SQL databases (like MySQL, PostgreSQL, etc.) ensure strict ACID compliance (Atomicity, Consistency, Isolation, Durability).

This is critical for applications where data accuracy, transactional integrity, and consistency are non-negotiable — such as:
Financial systems (banking, payment processing)
E-commerce (order processing, inventory updates)
Healthcare (patient records)

b) Structured and Stable Data Model:

When your data is highly structured (e.g., rows and columns with well-defined relationships) and schema rarely changes, SQL is ideal.

Examples:
Employee databases
Inventory systems
Student records

c)Complex Queries and Joins:
SQL excels at handling complex queries, multi-table joins, aggregations, and reporting.If you need real-time analytics or dashboards powered by relational queries, SQL fits well.

d) Single-Server or Vertical Scaling is Sufficient:
If your application doesn’t need to scale to massive user bases or distributed data centers, a single-node SQL database can be more cost-effective and easier to manage.

Reasons to use No SQL Database:

a) Flexible Schema for Unstructured or Semi-Structured Data:
NoSQL databases (like MongoDB, CouchDB, Cassandra, HBase) are ideal when you need to:

Store data without a fixed schema.
Handle diverse data types (e.g., text, JSON, metadata, images).
Easily add new fields without schema migrations.

Perfect for: User profiles, logs, IoT data, product catalogs, social feeds.

b) Low latency and high performance Simple ops: For use cases like caching, session storage, or real-time analytics, where you mostly do simple operations but at a very high volume, a specialized NoSQL store (like Redis for caching, or Cassandra for time-series inserts) is ideal.

c) Massive Scale or High Throughput Required: If you expect web-scale traffic or data volume that a single server cannot handle, NoSQL is more adept at scaling out. For instance, a social network’s feed, log aggregation system, or any service expecting millions of users and constant growth might lean NoSQL for the scalability.

c) Geographically Distributed Data: If you need multi-region deployments with local reads/writes in each region (to serve users around the world with low latency), many NoSQL databases handle replication and partitioning across data centers gracefully. Some SQL solutions do this too, but it’s often easier to accomplish eventual consistency models in NoSQL for multi-region.

d) Specific Data Models Fitting NoSQL: If your data naturally fits a document, graph, or other NoSQL model better than a relational one. For example, representing a social network (graph) is more straightforward in a graph database than in SQL tables with join tables for relationships.

e) Big Data and High Throughput Use Cases
NoSQL thrives in high-write, high-volume environments.
Use cases: Real-time analytics, event logging, clickstream data, sensor streams.

Important:

For complex querying and reporting, SQL databases offer more power out-of-the-box.

For fast reads/writes on simple access patterns at huge scale, NoSQL databases often provide better performance.

Write performance: NoSQL systems are optimized for fast write.

Read Performance: Simple key-value reads – NoSQL is great. Complex reads that aggregate data – SQL shines. Caching strategies often come into play too: e.g., using Redis to cache results of SQL queries.

Indexing: Both SQL and NoSQL databases use indexes to speed up lookups. In SQL, you index columns; in NoSQL, you index fields in documents (e.g., MongoDB has indexes too). Performance in both depends on good indexing for query patterns. NoSQL often encourages using the primary key (or partition key) as the main query mechanism.

Question 6

ACID vs BSAE

Answer 6

SQL databases are often categorized as CP (Consistent and Partition-tolerant) in the CAP theorem context – they choose consistency over availability when partitioned. SQL support ACID

BASE: (Basically Available, Soft state, Eventual consistency.). NoSQL databases, especially those designed to distribute across many nodes, favor availability and partition tolerance over strong consistency. This means they allow eventual consistency.

For example, if you update a user’s profile picture in a globally distributed NoSQL store, it might update in the US data center immediately but take a second to update in the Europe replica.

If someone in Europe reads in that second, they get the old picture (stale data). However, after a short time, consistency is achieved (eventually consistent).

Important:
ACID (SQL) is about absolute correctness and consistency – critical for ordered, reliable transactions like financial or legal data. BASE (NoSQL) is about being basically available and allowing inconsistency as a trade for performance and partition tolerance, often acceptable for large-scale web systems (like showing slightly out-of-date info that soon syncs).

Bottom line: If your application cannot tolerate inconsistent or out-of-date data, and you need transactions, a SQL database is usually the safer bet. If you can tolerate eventual consistency and need to prioritize uptime and distribution (like a globally distributed app that should always accept writes even if nodes are partitioned), a NoSQL approach might serve you better.

NOte: discuss ACID vs eventual consistency when deciding on SQL vs NoSQL in interview.