Learn
Software Engineering
Data
Databases
SQL
Overview

Overview

What is SQL?

SQL (Structured Query Language) is the standard language for managing and querying relational databases. It enables you to create, read, update, and delete data stored in tables with defined relationships.

SQL is declarative - you describe what you want, not how to get it. The database engine determines the optimal execution plan. This makes SQL powerful for data analysis, reporting, and application backends.

Why Learn SQL?

  • Universal: Works across MySQL, PostgreSQL, SQLite, SQL Server, Oracle with minor syntax differences
  • Essential: Required skill for data analysis, backend development, and database administration
  • Powerful: Handles complex queries, aggregations, and transformations declaratively
  • Mature: 40+ years of development, proven in production at massive scale
  • Portable: Skills transfer across database systems and programming languages

SQL powers everything from mobile apps (SQLite) to enterprise data warehouses (PostgreSQL, Oracle). Learning SQL opens doors to data-driven careers.

What You’ll Learn

This SQL learning path covers:

Fundamentals

  • Data Types - INTEGER, TEXT, REAL, BLOB, NULL handling
  • Basic Queries - SELECT, INSERT, UPDATE, DELETE, WHERE filtering
  • Sorting and Limiting - ORDER BY, LIMIT, OFFSET for pagination
  • Aggregation - COUNT, SUM, AVG, MIN, MAX, GROUP BY
  • Joins - INNER JOIN, LEFT JOIN, self-joins, multiple table queries

Intermediate Topics

  • Subqueries - Scalar, correlated, EXISTS/NOT EXISTS patterns
  • Common Table Expressions (CTEs) - WITH clauses for readable queries
  • Window Functions - ROW_NUMBER, RANK, LAG, LEAD, running totals
  • String & Date Functions - Text manipulation, date arithmetic, formatting
  • Set Operations - UNION, INTERSECT, EXCEPT for combining queries

Advanced Concepts

  • Query Optimization - EXPLAIN QUERY PLAN, indexes, query rewriting
  • Transactions - ACID properties, BEGIN, COMMIT, ROLLBACK
  • Data Modeling - Normalization, foreign keys, relationships
  • Production Patterns - Audit logs, soft deletes, optimistic locking
  • Analytics - Cohort analysis, funnel analysis, percentiles

Database Design

  • Schema Design - Primary keys, foreign keys, constraints
  • Normalization - 1NF, 2NF, 3NF for data integrity
  • Denormalization - Performance trade-offs for read-heavy workloads
  • Indexes - B-tree, covering, partial, composite indexes

SQL Dialects

While this content focuses on standard SQL using SQLite for examples, the concepts apply to all major database systems:

  • SQLite - Lightweight, serverless, embedded databases (mobile apps, prototypes)
  • PostgreSQL - Advanced open-source database (JSONB, full-text search, GIS)
  • MySQL/MariaDB - Popular open-source database (web applications, WordPress)
  • SQL Server - Microsoft’s enterprise database (Windows integration, business intelligence)
  • Oracle - Enterprise database (massive scale, mission-critical systems)

Syntax differences are minor - once you learn SQL fundamentals, switching databases is straightforward.

Learning Paths

Choose your learning approach based on experience and goals:

For Beginners

Start with foundational tutorials:

  1. Quick Start (Coming Soon) - Get up and running in minutes
  2. Beginner Tutorial (Coming Soon) - Learn SQL from scratch
  3. By Example - 85 annotated examples (code-first approach)

For Experienced Developers

Jump directly to:

  1. By Example Tutorial - 85 examples covering 95% of SQL through heavily annotated code
  2. Advanced Tutorial (Coming Soon) - Deep dives into optimization and production patterns

For Problem Solvers

Use:

  1. Cookbook (Coming Soon) - Solution recipes for common SQL tasks

Prerequisites

To follow SQL tutorials, you need:

  • Docker installed (for SQLite container) OR SQLite installed locally
  • Terminal/Command Line comfort
  • Basic programming concepts (variables, functions, loops) - helpful but not required

No prior database experience needed - these tutorials teach from first principles.

Tools and Setup

Recommended Setup

  • Docker with SQLite container (cross-platform, isolated environment)
  • SQLite command-line (lightweight alternative to Docker)
  • DBeaver or DB Browser for SQLite (GUI tools for visual query building)

Quick Start 

# Using Docker (recommended)
docker run --name sqlite-tutorial \
  -v sqlite-data:/data \
  -d nouchka/sqlite3:latest tail -f /dev/null

docker exec -it sqlite-tutorial sqlite3 /data/tutorial.db

# Using local SQLite
sqlite3 tutorial.db

See By Example Tutorial - Overview for detailed setup instructions.

How SQL Fits in Your Tech Stack

SQL databases serve as:

  • Application backends - Store user data, content, transactions
  • Analytics platforms - Data warehouses for business intelligence
  • Caching layers - Fast data access with indexes
  • Message queues - Transactional job queues
  • Configuration stores - Application settings and feature flags

SQL complements:

  • Application frameworks - Django, Rails, Spring Boot use ORMs over SQL
  • Business intelligence tools - Tableau, Metabase, Looker query SQL databases
  • ETL pipelines - Data transformation workflows read/write SQL databases

Next Steps

Ready to start learning?

  1. By Example Tutorial - Start here for code-first learning (85 examples)
  2. Beginner Tutorial (Coming Soon) - Narrative-driven comprehensive guide
  3. Advanced Tutorial (Coming Soon) - Query optimization and production patterns

For PostgreSQL-specific features (JSONB, full-text search, advanced indexes), see the PostgreSQL learning path.

Community and Resources

  • Official SQLite Documentation - sqlite.org/docs
  • SQL Standards - ISO/IEC 9075 (for reference)
  • Practice Platforms - LeetCode SQL, HackerRank SQL challenges
  • Books - “SQL Performance Explained” by Markus Winand

SQL is a foundational skill that compounds over your career. Invest in learning it deeply - you’ll use it for decades.

Last updated