Overview

Want to quickly master SQL through working examples? This by-example guide teaches 95% of standard SQL through 85 annotated examples organized by complexity level.

What Is By-Example Learning?

By-example learning is an example-first approach where you learn through annotated, runnable SQL rather than narrative explanations. Each example is self-contained, immediately executable in a SQLite container, and heavily commented to show:

• What each statement does - Inline comments explain the purpose and mechanism
• Expected outputs - Using -- => notation to show query results
• Intermediate states - Table contents and data transformations made visible
• Key takeaways - 1-2 sentence summaries of core concepts

This approach is ideal for experienced developers (seasonal programmers or software engineers) who are familiar with programming but want to quickly understand SQL syntax, database concepts, and query patterns through working code.

Unlike narrative tutorials that build understanding through explanation and storytelling, by-example learning lets you see the SQL first, run it second, and understand it through direct interaction. You learn by doing, not by reading about doing.

Learning Path

The SQL by-example tutorial guides you through 85 examples organized into three progressive levels, from fundamental concepts to advanced query optimization.

%% Color Palette: Blue #0173B2, Orange #DE8F05, Teal #029E73, Purple #CC78BC, Brown #CA9161
graph TD
    A["Beginner (Examples 1-30)<br/>0-40% Coverage<br/>Fundamentals"]
    B["Intermediate (Examples 31-60)<br/>40-75% Coverage<br/>Production Ready"]
    C["Advanced (Examples 61-85)<br/>75-95% Coverage<br/>Expert Mastery"]
 
    A -->|Master foundations| B
    B -->|Advanced patterns| C
 
    style A fill:#0173B2,stroke:#000,color:#fff
    style B fill:#DE8F05,stroke:#000,color:#fff
    style C fill:#029E73,stroke:#000,color:#fff

Coverage Philosophy

This by-example guide provides 95% coverage of standard SQL through practical, annotated examples. The 95% figure represents the depth and breadth of concepts covered, not a time estimate—focus is on outcomes and understanding, not duration.

What's Covered

• Core SQL syntax - SELECT, INSERT, UPDATE, DELETE, WHERE clauses, ORDER BY, LIMIT, OFFSET, DISTINCT
• Data types - INTEGER, REAL, TEXT, BLOB, NULL handling, type affinity (SQLite concept)
• Schema design - Table creation, primary keys, foreign keys, AUTOINCREMENT, unique constraints, check constraints, NOT NULL, default values
• Joins and relationships - INNER JOIN, LEFT/RIGHT/FULL OUTER JOIN (simulated), CROSS JOIN, self joins, complex multi-table queries
• Aggregations and grouping - COUNT, SUM, AVG, MIN, MAX, GROUP BY, HAVING clauses, GROUP_CONCAT
• String functions - UPPER, LOWER, LENGTH, SUBSTR, TRIM, REPLACE, LIKE, GLOB patterns
• Date and time functions - DATE, TIME, DATETIME, STRFTIME, JULIANDAY, date arithmetic, time zones
• Advanced queries - Common Table Expressions (CTEs), window functions (ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD), recursive CTEs, UNION/INTERSECT/EXCEPT
• Indexes and performance - CREATE INDEX, unique indexes, multi-column indexes, partial indexes, covering indexes, EXPLAIN QUERY PLAN analysis
• Subqueries - Scalar subqueries, correlated subqueries, EXISTS/NOT EXISTS, IN/NOT IN
• Transactions - BEGIN, COMMIT, ROLLBACK, SAVEPOINT, ACID properties, isolation levels
• Views - Creating views, updatable views, materialized views (simulated with triggers)
• Production patterns - Upsert with INSERT OR REPLACE, bulk insert optimization, CASE expressions, COALESCE for NULL handling
• Data manipulation - CAST for type conversion, JSON1 extension (JSON_EXTRACT, JSON_OBJECT, JSON_ARRAY), full-text search with FTS5
• Query optimization - Index selection strategies, query plan analysis, avoiding table scans, covering indexes
• Advanced techniques - Self-joins for hierarchical data, pivoting with CASE, running totals with window functions, generating series, calendar queries

What This Tutorial Does NOT Cover

Database-Specific Extensions: PostgreSQL's JSONB operators, MySQL's spatial functions, Oracle's PL/SQL - these are vendor-specific features not part of standard SQL

Application-Level ORMs: SQLAlchemy, Hibernate, ActiveRecord, Prisma - these are framework concerns, not SQL features

Deployment and Infrastructure: Docker Compose orchestration, connection pooling, replication setup - these are DevOps topics

Database Internals: B-tree implementation details, query optimizer algorithms, storage engine architecture - these are advanced internals beyond practical SQL usage

Migration Tools: Flyway, Liquibase, Alembic - these are tooling concerns for managing schema evolution

How to Use This Guide

1. Sequential or selective - Read examples in order for progressive learning, or jump to specific topics when you need a particular feature
2. Run everything - Copy and paste examples into your SQLite environment. Experimentation solidifies understanding.
3. Modify and explore - Change queries, add columns, insert different data, break things intentionally. Learn through experimentation.
4. Use as reference - Bookmark examples for quick lookups when you forget syntax or patterns
5. Complement with narrative tutorials - By-example learning is code-first; pair with comprehensive tutorials for deeper explanations

Best workflow: Open your terminal with SQLite in one window, this guide in another. Run each example as you read it. When you encounter something unfamiliar, run the example, modify it, see what changes.

Reference System: Examples are numbered (1-85) and grouped by level. This numbering appears in other SQL content at ayokoding.com, allowing you to reference specific examples elsewhere.

Structure of Each Example

Every example follows a consistent five-part format:

1. Brief Explanation (2-3 sentences): What the example demonstrates and why it matters
2. Mermaid Diagram (optional): Visual clarification when concept relationships benefit from visualization
3. Heavily Annotated Code: Every significant statement includes a comment explaining what it does and what it produces (using -- => notation)
4. Key Takeaway (1-2 sentences): The core insight you should retain from this example

This structure minimizes context switching - explanation, visual aid, runnable code, and distilled essence all in one place.

Execution Environment

All examples use a Docker-based SQLite container for reproducible, isolated execution across all platforms (Windows, macOS, Linux). SQLite is lightweight, requires no server setup, and provides standard SQL compliance ideal for learning.

One-time setup (run once before starting examples):

# Create SQLite container with persistent storage
docker run --name sqlite-tutorial \
  -v sqlite-data:/data \
  -d nouchka/sqlite3:latest tail -f /dev/null
 
# Connect to SQLite
docker exec -it sqlite-tutorial sqlite3 /data/tutorial.db

Every example is copy-paste runnable in this environment. Each example creates its own tables to ensure isolation and repeatability. Use .quit to exit SQLite shell.

Alternative: If you have SQLite installed locally, run sqlite3 tutorial.db instead of using Docker.

Relationship to Other Tutorials

This by-example tutorial complements other learning approaches. Choose based on your situation:

By-example is ideal if you have programming experience. It accelerates learning by leveraging your existing knowledge - you focus on "how SQL does this" rather than learning database concepts from scratch.

The 95% coverage represents depth and breadth of topics you'll encounter in production SQL work. It explicitly acknowledges that no tutorial covers everything, but these examples provide the foundation to understand the remaining 5% through official documentation and database-specific resources.

Prerequisites

• Basic programming knowledge (variables, functions, loops) or willingness to learn through examples
• Docker installed and running (for SQLite container) OR SQLite installed locally
• A terminal you're comfortable with

You don't need to understand database internals, normalization theory, or SQL standards yet - this tutorial teaches those through examples. You just need comfort running commands in a terminal.

Standard SQL Focus

This tutorial focuses on standard SQL that works across databases (MySQL, PostgreSQL, SQLite, SQL Server, Oracle). Where SQLite-specific features are used (like AUTOINCREMENT or type affinity), we note the standard SQL alternative.

Examples prioritize:

• Portable SQL syntax - Works on multiple database engines
• ANSI SQL compliance - Follows SQL standard when possible
• Practical patterns - Real-world query structures you'll use in production
• Clear annotations - Every query includes comments showing expected results

Comparison with By-Example for Other Technologies

Other technologies at ayokoding.com have similar by-example tutorials:

• PostgreSQL By-Example: 85 examples covering PostgreSQL-specific features (JSONB, full-text search, advanced indexes)
• Go By-Example: 85+ examples covering concurrency, interfaces, standard library patterns
• Java By-Example: 75-90 examples covering OOP, streams, concurrency, JVM patterns
• Elixir By-Example: 75-90 examples covering functional programming, pattern matching, OTP

The SQL version follows the same philosophy and structure but emphasizes SQL's declarative nature: you describe what you want, not how to compute it. SQL is fundamentally different from imperative languages - this tutorial helps you think in sets and transformations rather than loops and conditionals.

Learning Strategies

For Python/Data Science Developers

SQL is your primary data manipulation tool. Focus on aggregation (Examples 16-18), window functions (34-38), and analytics (61-70) to leverage your data analysis skills.

For Frontend/JavaScript Developers

SQL databases power your APIs. Focus on basic queries (Examples 3-5), schema design (Examples 23-28), and JSON handling (Examples 47-48) to understand backend data access.

For Backend/Java/C# Developers

SQL integrates directly with your applications. Focus on transactions (Example 27), subqueries (Examples 44-46), and optimization (Examples 51-55) for production-ready patterns.

For Complete Database Beginners

Start from Example 1 and follow sequentially through all 30 beginner examples for a comprehensive foundation in SQL fundamentals.

Code-First Philosophy

This tutorial prioritizes working code over theoretical discussion:

• No lengthy prose: Concepts are demonstrated, not explained at length
• Runnable examples: Every example runs in a SQLite container or local SQLite installation
• Learn by doing: Understanding comes from running and modifying SQL queries
• Pattern recognition: See the same patterns in different contexts across 85 examples

If you prefer narrative explanations, consider the by-concept tutorial (available separately). By-example learning works best when you learn through experimentation.

Ready to Start?

Jump into the beginner examples to start learning SQL through code:

• Beginner Examples (1-30) - Basic syntax, SELECT queries, WHERE clauses, schema design, aggregations
• Intermediate Examples (31-60) - Window functions, CTEs, subqueries, transactions, views
• Advanced Examples (61-85) - Query optimization, recursive CTEs, full-text search, advanced patterns

Each example is self-contained and runnable. Start with Example 1, or jump to topics that interest you most.

Examples by Level

Beginner (Examples 1–30)

• Example 1: Installing SQLite and First Query
• Example 2: Creating Your First Table
• Example 3: Basic SELECT Queries
• Example 4: Inserting Data with INSERT
• Example 5: Updating and Deleting Rows
• Example 6: Numeric Types (INTEGER and REAL)
• Example 7: Text Types and String Operations
• Example 8: NULL Handling
• Example 9: Date and Time Types
• Example 10: Boolean Values and Truthiness
• Example 11: WHERE Clause Filtering
• Example 12: Sorting with ORDER BY
• Example 13: Limiting Results with LIMIT and OFFSET
• Example 14: DISTINCT for Unique Values
• Example 15: Pattern Matching with LIKE and GLOB
• Example 16: COUNT, SUM, AVG, MIN, MAX
• Example 17: GROUP BY for Categorized Aggregation
• Example 18: HAVING Clause for Filtering Groups
• Example 19: INNER JOIN for Matching Rows
• Example 20: LEFT JOIN for Optional Matches
• Example 21: Self-Joins for Hierarchical Data
• Example 22: Multiple Joins
• Example 23: Primary Keys for Unique Identification
• Example 24: Foreign Keys for Relationships
• Example 25: Constraints (NOT NULL, CHECK, DEFAULT)
• Example 26: Indexes for Query Performance
• Example 27: Transactions for Data Consistency
• Example 28: Views for Query Simplification
• Example 29: Subqueries in WHERE
• Example 30: CASE Expressions for Conditional Logic

Intermediate (Examples 31–60)

• Example 31: Basic CTEs with WITH
• Example 32: Recursive CTEs for Hierarchical Data
• Example 33: CTEs for Complex Aggregations
• Example 34: ROW_NUMBER for Sequential Numbering
• Example 35: RANK and DENSE_RANK for Ranking
• Example 36: LAG and LEAD for Accessing Adjacent Rows
• Example 37: SUM, AVG, MIN, MAX as Window Functions
• Example 38: PARTITION BY with Window Functions
• Example 39: String Manipulation (SUBSTR, REPLACE, TRIM)
• Example 40: Date Arithmetic and Formatting
• Example 41: COALESCE and NULLIF for NULL Handling
• Example 42: UNION and UNION ALL
• Example 43: INTERSECT and EXCEPT
• Example 44: Correlated Subqueries
• Example 45: Scalar Subqueries in SELECT
• Example 46: IN and NOT IN with Subqueries
• Example 47: JSON Creation and Extraction
• Example 48: JSON Array Operations
• Example 49: FTS5 Virtual Tables
• Example 50: FTS5 Advanced Queries
• Example 51: EXPLAIN QUERY PLAN Analysis
• Example 52: Covering Indexes
• Example 53: Partial Indexes
• Example 54: Query Optimization Techniques
• Example 55: Transaction Performance
• Example 56: Pivoting Data with CASE
• Example 57: Running Totals and Moving Averages
• Example 58: Upsert with INSERT OR REPLACE
• Example 59: Generating Series and Calendar Queries
• Example 60: Hierarchical Aggregation

Advanced (Examples 61–85)

• Example 61: Percentile and Quartile Calculations
• Example 62: Cohort Analysis
• Example 63: Funnel Analysis
• Example 64: Sessionization
• Example 65: Survival Analysis (Customer Lifetime)
• Example 66: One-to-Many Relationship Modeling
• Example 67: Many-to-Many Relationship with Junction Table
• Example 68: Self-Referencing Foreign Keys
• Example 69: Polymorphic Associations
• Example 70: Slowly Changing Dimensions (Type 2)
• Example 71: Analyzing Query Performance with EXPLAIN
• Example 72: Index Strategies and Trade-offs
• Example 73: Query Rewriting for Performance
• Example 74: Batch Operations and Bulk Loading
• Example 75: Denormalization for Read Performance
• Example 76: Soft Deletes
• Example 77: Audit Logging
• Example 78: Optimistic Locking with Version Numbers
• Example 79: Idempotent Operations with Unique Constraints
• Example 80: Rate Limiting with Time Windows
• Example 81: Feature Flags
• Example 82: Time-Series Data Partitioning
• Example 83: Connection Pooling Simulation
• Example 84: Multi-Tenancy with Row-Level Filtering
• Example 85: Database Health Monitoring