Week 3 Master Guide
Week 3: Relational Databases & SQL From Zero to Pro AI Data Engineering
Week 3 Daily Breakdown (7 Days)
Day 1
Week 3 — Day 1: Relational Database Fundamentals & CRUD SQL From Zero
1 code file(s) • 3 Q&ADay 2
Week 3 — Day 2: Aggregations (`GROUP BY`, `HAVING`) & Multi-Table `JOIN`s
1 code file(s) • 3 Q&ADay 3
Week 3 — Day 3: Common Table Expressions
1 code file(s) • 3 Q&ADay 4
Week 3 — Day 4: Database Internals — B-Tree Indexes (O(log N)), EXPLAIN QUERY PLAN & ACID Transactions
1 code file(s) • 2 Q&ADay 5
Week 3 — Day 5: SQL Injection Defense & The Repository Pattern in Python
1 code file(s) • 2 Q&ADay 6
Week 3 — Day 6: Building an AI Conversation Memory & Token Cost Audit Database
1 code file(s) • 1 Q&ADay 7
Week 3 — Day 7: Mini-Project (Safe Read-Only Text-to-SQL Engine) + Week 3 Final Interview Grill
1 code file(s) • 0 Q&AWeek 3: Relational Databases & SQL From Zero to Pro AI Data Engineering (week3.md)
Mentor's Note (10+ YOE Staff AI Engineer POV): Why does every serious AI Engineer need deep SQL & Relational Database mastery?
- Every AI App Has a Database Backbone: Where do ChatGPT/Claude store users, multi-turn conversation histories, system prompt versions, thumbs-up/down evaluation feedback, and per-tenant token costs? In a Relational Database (SQLite / PostgreSQL)!
- Text-to-SQL & Data Agents (Month 4): One of the highest-paying enterprise AI use cases is building Text-to-SQL Agents where executives ask "What was our p95 latency and token spend per customer tier last week?" and the LLM writes a SQL query with
JOINs,CTEs (WITH), andWindow Functions(ROW_NUMBER() OVER). If you don't know advanced SQL, you cannot evaluate or debug your AI agent's SQL!- Hybrid Vector + Relational Search (Month 3): In production RAG (
pgvector), you combine SQLWHERE tenant_id = 42 AND created_at > '2026-01-01'with vector cosine similarity!
Week 3 Architecture & Daily Folder Map
text
week03_sql_databases_from_zero_to_pro/
├── week3.md # Master Week 3 Syllabus & Architecture
├── day01_rdbms_tables_keys_and_crud_sql/ # Day 1: Tables, Primary/Foreign Keys & CRUD SQL
│ ├── README.md
│ ├── src/sql_crud_basics.py
│ ├── tests/test_sql_crud_basics.py
│ └── interview_questions.md
├── day02_aggregations_groupby_having_and_joins/ # Day 2: GROUP BY, HAVING & INNER/LEFT JOINs
│ ├── README.md
│ ├── src/sql_joins_analytics.py
│ ├── tests/test_sql_joins_analytics.py
│ └── interview_questions.md
├── day03_subqueries_ctes_and_window_functions/ # Day 3: WITH CTEs & Window Functions (ROW_NUMBER, LAG)
│ ├── README.md
│ ├── src/sql_window_functions.py
│ ├── tests/test_sql_window_functions.py
│ └── interview_questions.md
├── day04_btree_indexes_query_plans_and_acid/ # Day 4: B-Tree Indexes (O(log N)), EXPLAIN & ACID
│ ├── README.md
│ ├── src/btree_index_benchmark.py
│ ├── tests/test_btree_index_benchmark.py
│ └── interview_questions.md
├── day05_python_db_security_and_sql_injection/ # Day 5: SQL Injection Defense & Repository Pattern
│ ├── README.md
│ ├── src/safe_repository.py
│ ├── tests/test_safe_repository.py
│ └── interview_questions.md
├── day06_ai_conversation_memory_and_cost_audit_db/ # Day 6: Multi-Turn Chat Memory & Token Billing DB
│ ├── README.md
│ ├── src/chat_memory_store.py
│ ├── tests/test_chat_memory_store.py
│ └── interview_questions.md
└── day07_week3_mini_project_text_to_sql_analytics/ # Day 7: Read-Only Text-to-SQL Guardrail Engine
├── README.md
├── src/text_to_sql_engine.py
├── tests/test_text_to_sql_engine.py
└── interview_questions.md
Daily Schedule & Learning Objectives
| Day | Topic | Core SQL & Database Theory | Runnable Deliverable |
|---|---|---|---|
| Day 1 | Database Fundamentals & CRUD SQL | Tables, Rows, Columns, Data Types (INTEGER, TEXT, REAL, TIMESTAMP), Primary Keys, Foreign Keys, CREATE, INSERT, SELECT, WHERE, ORDER BY, LIMIT, UPDATE, DELETE | sql_crud_basics.py — Creates an in-memory/file SQLite runbook metadata store and executes full CRUD operations |
| Day 2 | Aggregations (GROUP BY, HAVING) & JOINs | COUNT, SUM, AVG, MIN, MAX; why WHERE filters rows before grouping while HAVING filters groups after aggregation; INNER JOIN vs LEFT JOIN; 3NF Normalization | sql_joins_analytics.py — Multi-table schema (tenants, users, llm_requests) computing spend per tenant |
| Day 3 | CTEs (WITH) & Window Functions (OVER) | Common Table Expressions (WITH); ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), SUM() OVER (PARTITION BY ... ORDER BY ...) running totals | sql_window_functions.py — Finds top-K highest latency queries per model and token spend running totals |
| Day 4 | B-Tree Indexes (), EXPLAIN & ACID | Full Table Scan () vs Balanced B-Tree Index (); EXPLAIN QUERY PLAN; Composite Index Leftmost-Prefix Rule; ACID transactions (BEGIN, COMMIT, ROLLBACK) | btree_index_benchmark.py — Benchmarks 50,000 rows Full Table Scan vs B-Tree Index + proves atomic rollback |
| Day 5 | SQL Injection Defense & Repository Pattern | How string-formatted SQL (f"SELECT ... WHERE user = '{u}'") allows ' OR '1'='1 attacks; Parameterized Queries (? / $1); Context-managed transactions | safe_repository.py — Demonstrates a live SQL Injection exploit vs Parameterized Defense |
| Day 6 | AI Conversation Memory & Token Audit Database | Designing production schemas for Chatbots (sessions, messages, token_ledger); context window retrieval (LIMIT N); JSON metadata columns | chat_memory_store.py — Complete persistent Chatbot Memory & Token Cost Ledger engine |
| Day 7 | Week 3 Mini-Project: Safe Text-to-SQL Analytics Engine | Schema introspection (PRAGMA table_info), Read-Only SQL AST/Regex validation (blocking DROP, DELETE, UPDATE), and executing natural-language analytical queries | text_to_sql_engine.py — End-to-end Text-to-SQL Guardrail & Execution Engine + 12 SQL Interview Questions |