Topics/Databases

Databases

121 articles

Relational, NoSQL & Distributed Storage Engines

Databases store, manage, and query structured and unstructured data. Mastering storage engines, indexing strategies, and consistency models is critical for reliable backends.

Level: Foundational to Advanced
7 Core Subtopics

Track Curriculum

Subtopics & Modules

7 Modules Available
Module 01
3 Q&A
Beginner

SQL Fundamentals & Joins

SELECT queries, INNER/LEFT/RIGHT/FULL JOINs, GROUP BY aggregations, and subqueries.

18 min readStart Guide
Module 02
3 Q&A
Intermediate

Relational DBs (Postgres & MySQL)

Schema design, normalization (1NF-3NF), foreign keys, storage engines (InnoDB), and JSONB.

24 min readStart Guide
Module 03
3 Q&A
Intermediate

NoSQL & Document Stores

Document stores, MongoDB, aggregation pipelines, key-value stores, and schema-less flexibility.

20 min readStart Guide
Module 04
3 Q&A
Advanced

Indexing & Query Optimization

B-Trees, Hash indexes, composite indexes, EXPLAIN ANALYZE, and covering queries.

28 min readStart Guide
Module 05
3 Q&A
Advanced

Transactions & ACID Properties

Atomicity, Consistency, Isolation, Durability, isolation levels, and deadlock resolution.

26 min readStart Guide
Module 06
3 Q&A
Intermediate

Redis & In-Memory Caching

Redis data structures (Strings, Hashes, Lists, Sets, Sorted Sets), TTL, eviction policies, and cache patterns.

22 min readStart Guide
Module 07
3 Q&A
Advanced

Database Sharding & Replication

Primary-Replica replication, read scaling, hash-based sharding, CAP theorem, and PACELC.

30 min readStart Guide
Track Question Bank
21 Questions Across Modules

Interview Questions by Module Topic

Select a module to view its related questions, or explore all modules organized by seniority level

Filter by Module Topic:
01
Module Topic•3 Questions under this topic

SQL Fundamentals & Joins

Full Guide & Mechanics

What is the declarative execution order of an SQL query, and why can't you reference a column alias created in SELECT inside a WHERE clause?

In-Depth Technical Answer

While developers write SQL starting with `SELECT`, relational database engines (PostgreSQL, MySQL, Oracle) process query clauses in a strict declarative pipeline order: 1. **FROM / JOIN**: Identifies target tables and executes join logic (cartesian product + join filters). 2. **WHERE**: Evaluates filter predicates row-by-row on the base data *before* grouping. 3. **GROUP BY**: Aggregates filtered rows into distinct bucket groups. 4. **HAVING**: Filters the aggregated groups using aggregate expressions (e.g. `COUNT(*) > 5`). 5. **SELECT**: Evaluates column expressions, scalar functions, and assigns column aliases (`AS total`). 6. **DISTINCT**: De-duplicates projected tuples. 7. **ORDER BY**: Sorts the final output rows. 8. **LIMIT / OFFSET**: Paginates and truncates the result set. Because the `WHERE` clause executes at Step 2 while column aliases are assigned in `SELECT` at Step 5, the alias does not exist yet when `WHERE` evaluates! In contrast, `ORDER BY` executes at Step 7, which is why column aliases *can* be referenced in `ORDER BY`.

Practical Code Exampleexecution_order.sql
SQL
-- ILLEGAL: Fails with "column total does not exist"
-- SELECT price * quantity AS total FROM orders WHERE total > 100;

-- CORRECT: Repeat expression or use CTE / Subquery
SELECT price * quantity AS total
FROM orders
WHERE (price * quantity) > 100
ORDER BY total DESC; -- Legal: ORDER BY runs after SELECT!

🔍 Code Mechanics (SQL): WHERE executes at step 2 before SELECT runs at step 5; ORDER BY runs at step 7, allowing it to reference SELECT aliases.

Senior Architectural TakeawaySQL executes clauses in order: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT; aliases assigned in SELECT are unavailable in WHERE.
Explore full SQL Fundamentals & Joins guide & diagrams

What are the mechanical and algorithmic differences between Hash Join, Merge Join, and Nested Loops Join, and how does the query planner select the optimal strategy?

How do you design an immutable audit-log temporal system in PostgreSQL to reconstruct historical database state at any specific Point-in-Time (PITR)?

02
Module Topic•3 Questions under this topic

Relational DBs (Postgres & MySQL)

Full Guide & Mechanics

What is the difference between MySQL InnoDB's Clustered Index and a Secondary Index, and why do secondary index lookups perform a 'Bookmark Lookup'?

How does PostgreSQL Multi-Version Concurrency Control (MVCC) eliminate read-write locks, what causes Table Bloat, and how does Autovacuum clean dead tuples?

Why does PostgreSQL process-based concurrency crash under 10,000 application client connections, and how does PgBouncer Transaction Pooling solve connection starvation?

03
Module Topic•3 Questions under this topic

NoSQL & Document Stores

Full Guide & Mechanics

What are the fundamental architectural differences between SQL Relational Databases and NoSQL Document Stores, and when should you choose MongoDB over PostgreSQL?

When should you Embed documents versus Reference documents in MongoDB, and what 16MB document size and unbound array pitfalls must you avoid?

How do MongoDB Write Concern (w: majority, j: true) and Read Concern (linearizable vs local) prevent data rollback during replica set primary failovers?

04
Module Topic•3 Questions under this topic

Indexing & Query Optimization

Full Guide & Mechanics

How do B+ Tree database indexes work, why are leaf nodes chained together in doubly linked lists, and why is WHERE id = ? logarithmic O(log N) instead of linear O(n)?

What is the Leftmost Prefix Rule in Composite Indexes, and how does a Covering Index (Index-Only Scan) eliminate expensive heap table reads?

How do Partial Indexes and Expression/Functional Indexes in PostgreSQL optimize queries on massive 100-million-row tables while slashing index storage by 90%?

05
Module Topic•3 Questions under this topic

Transactions & ACID Properties

Full Guide & Mechanics

What do the ACID properties guarantee in a relational database, illustrated with a bank funds transfer scenario?

What are the 4 standard SQL Transaction Isolation Levels, and which concurrency phenomena (Dirty Read, Non-Repeatable Read, Phantom Read) does each prevent?

How do you handle High-Contention inventory decrements under high-concurrency load using Optimistic Concurrency Control (version columns) versus Pessimistic Locking (SELECT ... FOR UPDATE)?

06
Module Topic•3 Questions under this topic

Redis & In-Memory Caching

Full Guide & Mechanics

Why is Redis single-threaded for command execution yet capable of processing over 100,000 requests per second with sub-millisecond latency?

What are the operational differences between Redis memory eviction policies (allkeys-lru, volatile-lru, volatile-ttl, allkeys-lfu), and how does Redis implement Approximate LRU without pointer memory overhead?

How do you implement a production-safe Distributed Lock in Redis with unique owner tokens, auto-expiry TTL, and atomic Lua release scripts (Redlock pattern)?

07
Module Topic•3 Questions under this topic

Database Sharding & Replication

Full Guide & Mechanics

What is the operational difference between Database Replication (Read Scaling) and Database Sharding (Write/Storage Scaling), and when is sharding required?

How do you choose an effective Database Shard Key to avoid Hotspots (Hot Shards), and how do you handle cross-shard queries?

How do you execute a live zero-downtime database sharding migration on a production cluster handling 50,000 QPS using Dual-Writing, Shadow Verification, and cutover routing?