Week 3 — Day 1: Relational Database Fundamentals & CRUD SQL From Zero (`SQLite`)

Mentor's Note (10+ YOE Staff AI Engineer POV): Whether you are a complete non-tech beginner seeing SQL for the first time or a developer building an AI startup, SQL (Structured Query Language) is the universal language of data. Python comes with a full industrial-grade relational database built right into its standard library: sqlite3 (used on billions of devices, and by LangGraph/LlamaIndex for local state persistence!). And in Month 3 & 5, the exact same SQL syntax runs on PostgreSQL!

Part 1: What is a Relational Database? (Tables, Rows, Columns & Keys)

Think of a Relational Database as a collection of strict, typed spreadsheets (Tables) that can link to each other:

Table (runbooks): A collection of related data entities.
Column (Field / Attribute): A specific property with a strict data type (id INTEGER, title TEXT, latency_ms REAL).
Row (Record / Tuple): A single complete entry in the table.
Primary Key (PRIMARY KEY): A unique identifier for each row (like id = 1). Can never be NULL or duplicated!
Foreign Key (FOREIGN KEY): A column in Table B that points to the PRIMARY KEY of Table A (linking a message to its conversation_id).

Part 2: Complete CRUD SQL Cheat Sheet (`CREATE`, `INSERT`, `SELECT`, `UPDATE`, `DELETE`)

sql

-- 1. CREATE TABLE (Define Schema & Constraints)
CREATE TABLE IF NOT EXISTS runbooks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    filename TEXT NOT NULL UNIQUE,
    service_name TEXT NOT NULL,
    token_count INTEGER NOT NULL CHECK (token_count >= 0),
    is_active INTEGER NOT NULL DEFAULT 1
);

-- 2. INSERT INTO (Create New Rows)
INSERT INTO runbooks (filename, service_name, token_count)
VALUES ('payments_service.md', 'payments', 450);

-- 3. SELECT (Read, Filter, Sort & Paginate)
SELECT id, filename, service_name, token_count
FROM runbooks
WHERE is_active = 1 AND token_count >= 100
ORDER BY token_count DESC
LIMIT 10;

-- 4. UPDATE (Modify Existing Rows — ALWAYS include a WHERE clause!)
UPDATE runbooks
SET token_count = 520
WHERE filename = 'payments_service.md';

-- 5. DELETE (Remove Rows — ALWAYS include a WHERE clause!)
DELETE FROM runbooks
WHERE id = 1;

[!CAUTION] The Classic Production Disaster: Running UPDATE runbooks SET is_active = 0; or DELETE FROM runbooks; without a WHERE clause updates or deletes EVERY SINGLE ROW IN THE TABLE! Always write and test your WHERE clause with a SELECT first!

Part 3: Hands-On Lab — SQL CRUD Runbook Registry (`sql_crud_basics.py`)

Check out src/sql_crud_basics.py (in Tab 2: Codes — click Run in Browser to execute) and run the script and tests:

powershell

python week03_sql_databases_from_zero_to_pro/day01_rdbms_tables_keys_and_crud_sql/src/sql_crud_basics.py
python -m unittest discover -s week03_sql_databases_from_zero_to_pro/day01_rdbms_tables_keys_and_crud_sql/tests -v

Day 1:Relational Database Fundamentals & CRUD SQL From Zero

Week 3 — Day 1: Relational Database Fundamentals & CRUD SQL From Zero (SQLite)

Part 1: What is a Relational Database? (Tables, Rows, Columns & Keys)

Part 2: Complete CRUD SQL Cheat Sheet (CREATE, INSERT, SELECT, UPDATE, DELETE)

Part 3: Hands-On Lab — SQL CRUD Runbook Registry (sql_crud_basics.py)

Week 3 — Day 1: Relational Database Fundamentals & CRUD SQL From Zero (`SQLite`)

Part 2: Complete CRUD SQL Cheat Sheet (`CREATE`, `INSERT`, `SELECT`, `UPDATE`, `DELETE`)

Part 3: Hands-On Lab — SQL CRUD Runbook Registry (`sql_crud_basics.py`)