🗄️ Database Interview Prep

A production-grade, interview-focused course covering SQL, NoSQL, system design, and real-world database engineering. Built to make you both interview-ready and portfolio-strong.

---

📋 Table of Contents

• Why This Course
• Repository Structure
• Learning Paths
• SQL Mastery
• Database Design
• NoSQL & Distributed Systems
• Performance Optimization
• Transactions & Concurrency
• Problem Bank
• Projects
• Cheatsheets
• Setup Guide
• How to Use This Repo
• Contributing

---

🎯 Why This Course

Most interview prep resources teach you what SQL is. This course teaches you how interviewers think:

• Pattern recognition — every hard SQL problem reduces to 3–5 repeatable patterns
• Trade-off fluency — explain why you chose a schema, index, or system design decision
• Production habits — write queries and schemas the way senior engineers do
• Portfolio output — every module produces code you can push to GitHub and show recruiters

Target roles: Data Engineer, Backend Engineer, Analytics Engineer, Data Analyst, ML Engineer, Database Administrator

---

📁 Repository Structure

database-interview-prep/
│
├── sql/                          # SQL mastery modules
│   ├── 01_basics/
│   ├── 02_joins/
│   ├── 03_aggregations/
│   ├── 04_window_functions/
│   ├── 05_ctes_and_subqueries/
│   ├── 06_query_optimization/
│   └── 07_advanced_patterns/
│
├── nosql/                        # NoSQL systems
│   ├── 01_mongodb/
│   ├── 02_redis/
│   ├── 03_sql_vs_nosql/
│   └── 04_vector_databases/          ← embeddings, HNSW, pgvector, Qdrant, RAG
│
├── system-design/                # Schema design & architecture
│   ├── 01_er_diagrams_and_schema/
│   ├── 02_normalization/
│   ├── 03_cap_theorem/
│   └── 04_scaling_strategies/
│
├── notes/                        # Deep-dive reference notes
│   ├── transactions_and_concurrency.md
│   ├── indexing_strategies.md
│   └── performance_optimization.md
│
├── cheatsheets/                  # Quick-revision reference cards
│   ├── sql_cheatsheet.md
│   ├── nosql_cheatsheet.md
│   └── top_50_sql_interview_questions.md
│
├── problems/                     # Categorized problem bank
│   ├── easy/
│   ├── medium/
│   └── hard/
│
├── projects/                     # Portfolio-grade projects
│   ├── 01_library_db/
│   ├── 02_ecommerce_db/
│   ├── 03_analytics_dashboard/
│   ├── 04_social_media_db/
│   └── 05_capstone_system/
│
├── datasets/                     # Seed data for all projects
│
├── SETUP.md                      # Local + cloud DB setup guide
├── CONTRIBUTING.md               # How to contribute
└── README.md                     # This file

---

🛤️ Learning Paths

Role	Duration	Focus
Data Analyst	2 weeks	SQL Modules 1–5 → Problems (easy+medium) → Projects 1–2
Backend Engineer	3 weeks	SQL Modules 1–7 → System Design → NoSQL → Projects 1–3
Data Engineer	4 weeks	All SQL → NoSQL → Performance → System Design → All Projects
Quick Revision	3 days	Cheatsheets → Top 50 Questions → Hard Problems

---

🔷 SQL Mastery

Module	Topics	Interview Weight
01 — Basics	SELECT, WHERE, ORDER BY, GROUP BY, HAVING	⭐⭐
02 — Joins	INNER, LEFT, RIGHT, FULL, SELF, CROSS joins	⭐⭐⭐⭐⭐
03 — Aggregations	COUNT, SUM, AVG, GROUP BY, HAVING, ROLLUP	⭐⭐⭐⭐
04 — Window Functions	ROW_NUMBER, RANK, LAG/LEAD, PARTITION BY	⭐⭐⭐⭐⭐
05 — CTEs & Subqueries	WITH, recursive CTEs, correlated subqueries	⭐⭐⭐⭐
06 — Query Optimization	EXPLAIN, indexes, execution plans	⭐⭐⭐⭐
07 — Advanced Patterns	Gaps & islands, pivoting, top-N per group	⭐⭐⭐⭐⭐

---

🏗️ Database Design

Module	Topics
ER Diagrams & Schema	Entities, relationships, cardinality, schema SQL
Normalization	1NF → 3NF → BCNF, denormalization trade-offs
CAP Theorem	Consistency, availability, partition tolerance
Scaling Strategies	Replication, sharding, partitioning, read replicas

---

🍃 NoSQL & Distributed Systems

Module	Topics	Interview Weight
MongoDB	Document model, aggregation pipeline, indexing	⭐⭐⭐
Redis	Data structures, caching patterns, pub/sub	⭐⭐⭐⭐
SQL vs NoSQL	When to use what, trade-off frameworks	⭐⭐⭐⭐
Vector Databases	Embeddings, HNSW/IVF, pgvector, Qdrant, Pinecone, RAG schema	⭐⭐⭐⭐⭐

---

⚡ Performance Optimization

→ See notes/indexing_strategies.md and notes/performance_optimization.md

• B-Tree vs Hash indexes
• Covering indexes and index pushdown
• Query execution plans (EXPLAIN ANALYZE)
• N+1 problem and batch loading
• Partitioning for performance

---

🔐 Transactions & Concurrency

→ See notes/transactions_and_concurrency.md

• ACID deep dive with real examples
• Isolation levels: READ UNCOMMITTED → SERIALIZABLE
• Deadlock detection and prevention
• Optimistic vs pessimistic locking

---

🧪 Problem Bank

Problems are tagged and categorized:

problems/
├── easy/     # Warmup + filtering + basic joins
├── medium/   # Window functions + subqueries + design
└── hard/     # Recursive CTEs + optimization + system design

Tags: joins aggregation window-functions cte recursion optimization design gaps-islands top-n ranking

---

💼 Projects

#	Project	Level	Tech Stack	Key Skills
01	Library Management System	Beginner	PostgreSQL	Schema design, CRUD, basic queries
02	E-Commerce Platform	Beginner–Mid	PostgreSQL	Complex joins, inventory, orders
03	Analytics Dashboard Backend	Intermediate	PostgreSQL + Redis	Aggregations, caching, reporting
04	Social Media Platform	Advanced	PostgreSQL + MongoDB	Graph-like queries, feeds, scaling
05	HR & Payroll System (Capstone)	Advanced	PostgreSQL	Full design cycle, audit trail, analytics

---

📖 Cheatsheets

Sheet	Contents
SQL Cheatsheet	All syntax, functions, patterns in one page
NoSQL Cheatsheet	MongoDB + Redis command reference
Top 50 SQL Questions	50 most asked questions with model answers

---

⚙️ Setup Guide

→ Full instructions in SETUP.md

Quick start (Docker):

docker run --name pg-interview -e POSTGRES_PASSWORD=postgres -p 5432:5432 -d postgres:16
docker run --name mongo-interview -p 27017:27017 -d mongo:7
docker run --name redis-interview -p 6379:6379 -d redis:7

---

📌 How to Use This Repo

1. Start with a learning path — pick the one matching your role above
2. Read the module README — understand the concept + interviewer lens
3. Run the SQL files — every module has runnable .sql scripts
4. Solve the problems — attempt before looking at solutions
5. Build the projects — fork the project template, implement from scratch
6. Review cheatsheets — last-minute revision before interviews

---

🤝 Contributing

See CONTRIBUTING.md for guidelines on adding problems, fixing errors, or improving explanations.

---

📄 License

MIT © 2024 — Free to use, fork, and build on.