Databases & SQL Interview Questions¶

Conceptual questions on database design, SQL queries, transactions, optimization, and best practices.

1. What's the difference between INNER JOIN, LEFT JOIN, and UNION?¶

View Answer

INNER JOIN

Returns rows that exist in BOTH tables (intersection).

SELECT o.id, c.name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

Result: Only shows customers WITH orders

LEFT JOIN

Returns ALL rows from left table + matching rows from right table.

SELECT o.id, c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

Result: All customers shown. Non-matching orders have NULL values.

UNION

Combines result sets from multiple queries, removes duplicates.

SELECT name FROM customers
UNION
SELECT name FROM vendors;
-- Result: All unique names from both tables

UNION ALL: Like UNION but keeps duplicates (faster).

Comparison Table

Operation	Result	Duplicates	Use Case
INNER JOIN	Matching rows only	N/A (join)	Data in both tables
LEFT JOIN	All left + matching right	N/A (join)	All left + optional right
UNION	Combined result	Removed	Combining similar datasets
UNION ALL	Combined result	Kept	Combining, performance matters

📚 Learn more: SQL & Relational Databases

2. What's the difference between WHERE and HAVING?¶

View Answer

Quick Comparison

Clause	Filters	When Applied	Can Use Aggregates
WHERE	Individual rows	BEFORE grouping	❌ No
HAVING	Groups	AFTER grouping/aggregation	✅ Yes

Example

SELECT customer_id, COUNT(*) as order_count
FROM orders
WHERE order_date > '2024-01-01'      -- Filters ROWS before grouping
GROUP BY customer_id
HAVING COUNT(*) > 5;                 -- Filters GROUPS after aggregation
-- Result: Customers with >5 orders since 2024-01-01

Key Point

Cannot use aggregate functions in WHERE:

-- ❌ INVALID
SELECT customer_id FROM orders
WHERE COUNT(*) > 5
GROUP BY customer_id;

-- ✅ VALID
SELECT customer_id FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;

Performance Tip

Use WHERE whenever possible (filters before grouping, faster). Use HAVING only for aggregate filtering.

📚 Learn more: SQL & Relational Databases

3. What are common database caching patterns?¶

View Answer

1. Cache-Aside (Lazy Loading)

Flow: App checks cache → if miss, fetch DB → populate cache

Request → Check Cache → Miss? → Query DB → Store in Cache → Return

Pros: Simple, read-heavy workloads, only needed data cached

Cons: Cache misses cause DB hits, stale data possible

2. Read-Through

Flow: Cache library handles DB fetching automatically

Request → Cache (handles DB fetch internally) → Return

Pros: Transparent to app, consistent logic

Cons: More complex, tight coupling with cache library

3. Write-Through

Flow: Write to cache AND DB simultaneously

Write Request → Cache → DB (synchronous) → Return

Pros: Data consistency guaranteed

Cons: Slower writes, must wait for DB

4. Write-Behind (Write-Back)

Flow: Write to cache immediately, write to DB asynchronously

Write Request → Cache → Return (async write to DB)

Pros: Fast writes, good for write-heavy workloads

Cons: Data loss risk if cache crashes before DB write

Interview Favorite

Cache-Aside with Redis is the most common pattern for interviews. It's simple, proven, and widely used.

📚 Learn more: Caching

4. Explain the CAP theorem and its implications¶

View Answer

The CAP Theorem

In a distributed system, you can guarantee only 2 of 3 properties:

Consistency (C): All nodes see the same data at the same time
Availability (A): System always responds to requests
Partition Tolerance (P): System works despite network partitions

Trade-offs in Practice

Type	Consistency	Availability	Partition Tolerance	Examples
CA	✅	✅	❌	Traditional relational DBs
CP	✅	❌	✅	HBase, Zookeeper, MongoDB
AP	❌	✅	✅	Cassandra, DynamoDB, Riak

Key Insight

Network partitions ALWAYS happen. Modern distributed systems must choose between:

CP: Reject writes during partition (consistent but unavailable)
AP: Accept writes, reconcile later (available but inconsistent)

Real-World Implications

Banking (CP): Cannot afford inconsistency; accept downtime
Social Media (AP): Can tolerate eventual consistency; prioritize availability
E-commerce (AP with backup): Availability first, eventual consistency

📚 Learn more: System Design Fundamentals

5. What's the difference between ACID and BASE?¶

View Answer

ACID (Traditional Databases)

Property	Meaning	Example
Atomicity	Transaction all-or-nothing (no partial updates)	Transfer $100: debit one, credit other (both or neither)
Consistency	Database always in valid state	Foreign key constraints always satisfied
Isolation	Concurrent transactions don't interfere	Two simultaneous transfers don't corrupt data
Durability	Committed data persists (crash-safe)	Committed transaction survives power failure

BASE (NoSQL, Distributed)

Property	Meaning	Example
Basically Available	System responds even during failures	Social media post succeeds even if replica lags
Soft State	State changes without input (eventual consistency)	Like and count doesn't match immediately
Eventually Consistent	Will reach consistency over time	All replicas eventually have same data

When to Use

ACID: Financial systems, inventory (strong guarantees critical)
BASE: Social media, analytics, real-time feeds (eventual consistency OK)

📚 Learn more: System Design

6. What are idempotency and statelessness?¶

View Answer

Idempotency

Definition: Calling an operation multiple times = calling it once. Safe to retry.

Examples

Operation	Idempotent?	Why?
`PUT /users/123 {"name": "John"}`	✅ Yes	Called 3x = same result
`GET /users/123`	✅ Yes	Always returns same data
`POST /transfers {"from": "A", "to": "B", "amount": 100}`	❌ No	Called 3x = 3 transfers
`DELETE /users/123`	✅ Yes	Deleting twice = same result

Ensuring Idempotency for POST

Use idempotent keys (client-generated unique ID):

POST /transfers
Headers: 
  Idempotent-Key: abc-123-def

If same key sent again, server skips if already processed

Statelessness

Definition: Server stores no session data. Each request contains all info needed.

Benefits

Horizontal scaling: Any server can handle any request
No sticky sessions: Load balancer can route anywhere
Fault tolerance: Server crash doesn't lose session data

Implementation

Use tokens (JWT) instead of session storage:

Client sends: Authorization: Bearer jwt-token
Server validates token (no session needed)

📚 Learn more: Security & Authentication

7. How do indexes improve query performance?¶

View Answer

What Indexes Do

Indexes create a data structure (usually B-tree) that allows faster lookup than scanning every row.

Without Index (Table Scan)

Query: SELECT * FROM users WHERE id = 5
Result: Scan ALL rows → O(n) time

With Index

Query: SELECT * FROM users WHERE id = 5
Result: Look up in B-tree index → O(log n) time

Index Types

Type	Use Case	Trade-off
Primary Key Index	Unique lookups (id=5)	Unique, fast
Composite Index	Multi-column queries (name, age)	Must match column order
Full-Text Index	Text search queries	Slower writes, fast searches

Index Trade-offs

Pros: Faster reads, faster WHERE/JOIN/ORDER BY
Cons: Slower writes (must update index), extra disk space, must maintain

When to Index

DO index: Columns in WHERE, JOIN, ORDER BY, with high selectivity (many unique values)
DON'T index: Columns with low selectivity (few unique values), columns rarely queried

Example: Composite Index

-- Query: Find users by last_name, then first_name
SELECT * FROM users WHERE last_name = 'Smith' AND first_name = 'John';

-- Create composite index (order matters!)
CREATE INDEX idx_name ON users(last_name, first_name);

-- This index helps the above query
-- But NOT: SELECT * FROM users WHERE first_name = 'John'

📚 Learn more: SQL & Databases

8. When would you choose NoSQL over a relational database?¶

View Answer

Choose NoSQL When

Scenario	NoSQL Advantage	Example
Massive scale needed	Designed for horizontal scaling	Billions of events/documents
Flexible schema	Schema-less; evolve without migrations	User profiles with varying fields
High write throughput	Optimized for fast writes	Real-time logs, metrics
Non-relational data	Stores documents, graphs, key-values naturally	JSON documents, social graphs
Eventual consistency OK	Prioritizes availability over consistency	Social media, caches

Choose Relational When

Complex relationships: JOINs across many tables
ACID guarantees needed: Financial, inventory
Schema stability: Fixed structure, validated constraints
Complex queries: Aggregations, reports

NoSQL Types & Use Cases

Type	Example	Best For
Document	MongoDB, Firebase	JSON-like data, flexible schema
Key-Value	Redis, DynamoDB	Caching, sessions, fast lookups
Graph	Neo4j, ArangoDB	Social networks, recommendations
Time-Series	InfluxDB, TimescaleDB	Metrics, logs, sensor data

Real-World Example

Social Media Platform:

User profiles: MongoDB (flexible schema)
Real-time feed: Cassandra (high write throughput, AP)
Cache layer: Redis (key-value)
Relationships: Neo4j (graph for recommendations)

📚 Learn more: NoSQL & Distributed Databases

Additional Topics¶

See Sample Topics for more areas to explore: - Explain normalization and why it matters - When would you denormalize a database? - What's the difference between clustering and non-clustering indexes? - How do you handle concurrent writes to the same record? - How do you optimize a slow query? - What's the difference between replication and sharding?