Practitioner Overview

Go beyond basics — write queries that perform and build polyglot data solutions. Advanced SQL, indexing strategies, ACID transactions, and hands-on NoSQL with MongoDB and Redis.

Technologies: MySQL 8, PostgreSQL 15, MongoDB 7, Redis 7

---

🚀 Start Here → Lab 01: Window Functions

---

📋 Lab Index

#	Lab	DB	Topics
01	Window Functions	PostgreSQL 15	OVER, PARTITION BY, RANK, ROW_NUMBER, LAG/LEAD, NTILE
02	CTEs & Recursive Queries	PostgreSQL 15	WITH, recursive CTEs, tree traversal, hierarchies
03	Stored Procedures & Functions	MySQL 8	CREATE PROCEDURE, FUNCTION, DECLARE, loops, cursors
04	Triggers & Events	MySQL 8	BEFORE/AFTER triggers, event scheduler, audit patterns
05	Advanced Indexing	PostgreSQL 15	Partial, composite, covering, GIN, GiST, BRIN indexes
06	EXPLAIN & Query Optimization	PostgreSQL 15	EXPLAIN ANALYZE, seq scan vs index scan, query plans
07	Transactions & Isolation Levels	PostgreSQL 15	Read Uncommitted/Committed/Repeatable Read/Serializable
08	Deadlocks	MySQL 8	Deadlock detection, prevention, SHOW INNODB STATUS
09	Connection Pooling	PostgreSQL 15	PgBouncer setup, pool modes, connection limits
10	Full-Text Search	MySQL 8, PostgreSQL	MATCH AGAINST, tsvector/tsquery, GIN indexes
11	MongoDB CRUD	MongoDB 7	insertOne/Many, find, updateOne, deleteOne, filters
12	MongoDB Aggregation Pipeline	MongoDB 7	$match, $group, $sort, $lookup, $unwind, $project
13	MongoDB Indexes & Queries	MongoDB 7	createIndex, explain, compound, text, TTL indexes
14	Redis Data Structures	Redis 7	Strings, hashes, lists, sets, sorted sets, HyperLogLog
15	Redis Pub/Sub & Streams	Redis 7	PUBLISH/SUBSCRIBE, Streams, consumer groups, XREAD
16	Redis Caching Patterns	Redis 7	Cache-aside, write-through, write-behind, TTL, eviction
17	PostgreSQL JSONB Advanced	PostgreSQL 15	JSONB operators, path queries, GIN indexes, aggregation
18	Database Security	MySQL 8	Users, GRANT/REVOKE, SSL, password policies
19	Backup & Restore	MySQL 8, PostgreSQL	mysqldump, pg_dump, restore, point-in-time recovery
20	Capstone: Multi-Model App Database	MySQL+MongoDB+Redis	Full app: relational + document + cache integration

---

🗂️ Learning Path

SQL Mastery (Labs 1–10)

Window functions and CTEs are the most-asked SQL interview topics. Master these before NoSQL.

NoSQL (Labs 11–16)

MongoDB for documents, Redis for speed. Learn when to use each.

Advanced Topics (Labs 17–20)

JSONB power user, security hardening, backup strategies, capstone.

---

🛠️ Setup

# PostgreSQL
docker run -d --name pg-lab -e POSTGRES_PASSWORD=rootpass postgres:15

# MongoDB
docker run -d --name mongo-lab mongo:7

# Redis
docker run -d --name redis-lab redis:7-alpine

# MySQL
docker run -d --name mysql-lab -e MYSQL_ROOT_PASSWORD=rootpass mysql:8.0