SQL — Database Query Fundamentals

Status: Active Last Updated: 2026-08-29 Category: Databases & Storage Prerequisites: kb/basics/ — Linux fundamentals, command-line basics Tags: sql, sqlite, postgres, mysql, database, queries, joins, indexes

Summary

SQL fundamentals for anyone who will touch a database — from writing basic queries to understanding indexing, joins, and schema design. This stack uses SQLite in development (via Prisma) and PostgreSQL in production. Both are covered.

🎯 Learning Philosophy

SQL is Universal:

Every backend stores data
Every data layer queries with SQL (or an abstraction over it)
Learn SQL once → use it in SQLite, Postgres, MySQL, beyond

📚 Learning Path

Prerequisites: kb/basics/
         ↓
┌────────────────────────────────────────┐
│  PHASE 1: SQL Fundamentals             │
│  ├─ SELECT, WHERE, ORDER BY             │
│  ├─ Filtering, comparison operators     │
│  ├─ DISTINCT, LIMIT, OFFSET            │
│  └─ NULL handling (IS NULL, COALESCE)  │
└────────────────────────────────────────┘
         ↓
┌────────────────────────────────────────┐
│  PHASE 2: Joins & Relations            │
│  ├─ PRIMARY KEY, FOREIGN KEY           │
│  ├─ INNER JOIN, LEFT JOIN               │
│  ├─ Self joins, multiple joins          │
│  └─ UNION, INTERSECT, EXCEPT           │
└────────────────────────────────────────┘
         ↓
┌────────────────────────────────────────┐
│  PHASE 3: Aggregation & Grouping        │
│  ├─ COUNT, SUM, AVG, MIN, MAX          │
│  ├─ GROUP BY, HAVING                   │
│  ├─ Subqueries                         │
│  └─ Window functions                   │
└────────────────────────────────────────┘
         ↓
┌────────────────────────────────────────┐
│  PHASE 4: Schema & Indexing             │
│  ├─ CREATE TABLE, ALTER TABLE           │
│  ├─ Data types and constraints          │
│  ├─ Indexes (B-tree, GIN, partial)     │
│  └─ EXPLAIN and query plans            │
└────────────────────────────────────────┘
         ↓
┌────────────────────────────────────────┐
│  PHASE 5: Transactions & Advanced       │
│  ├─ ACID properties                    │
│  ├─ BEGIN, COMMIT, ROLLBACK             │
│  ├─ Stored procedures and functions     │
│  └─ Migrations and schema versioning    │
└────────────────────────────────────────┘

📖 Articles in This Directory

🟢 Phase 1: SQL Fundamentals

1. sql-fundamentals — Writing your first queries
   • SELECT, FROM, WHERE
   • ORDER BY, LIMIT, OFFSET
   • Comparison and logical operators
   • NULL handling and COALESCE
   • Prerequisites: None
   • Time: 2-3 hours

🟡 Phase 2: Joins & Relations

1. sql-joins — Relating tables together
   • PRIMARY KEY, FOREIGN KEY concepts
   • INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN
   • Multiple joins, self-joins
   • UNION, INTERSECT, EXCEPT
   • Prerequisites: SQL fundamentals
   • Time: 2-3 hours

🟡 Phase 3: Aggregation & Grouping

1. sql-aggregation — Summarizing data
   • COUNT, SUM, AVG, MIN, MAX
   • GROUP BY, HAVING
   • Subqueries in SELECT, WHERE, FROM
   • Window functions (ROW_NUMBER, RANK, LAG, LEAD)
   • Prerequisites: Joins and relations
   • Time: 2-3 hours

🟠 Phase 4: Schema & Indexing

1. sql-schema — Designing tables and indexes
   • CREATE TABLE, ALTER TABLE, DROP TABLE
   • Data types: INTEGER, TEXT, REAL, BLOB, SERIAL
   • Constraints: NOT NULL, UNIQUE, CHECK, DEFAULT
   • Indexes: single-column, composite, B-tree, GIN
   • EXPLAIN and reading query plans
   • Prerequisites: SQL fundamentals
   • Time: 3-4 hours

🔴 Phase 5: Transactions & Advanced

1. sql-transactions — Multi-statement operations
   • ACID: Atomicity, Consistency, Isolation, Durability
   • BEGIN, COMMIT, ROLLBACK
   • Isolation levels (READ COMMITTED, SERIALIZABLE)
   • Migrations: Prisma migrations, raw SQL migrations
   • Prerequisites: Schema design
   • Time: 2-3 hours

🔗 What Comes Next?

• kb/databases/ — How this project uses databases (Prisma + SQLite/Postgres)
• kb/backend/ — Connecting databases to backend services
• kb/infrastructure/ — Database deployment and backup

📝 Change Log

2026-08-29

• Created sql/ directory scaffold
• Defined 5-phase learning path
• Targets SQLite (dev) and PostgreSQL (production)