SQL Fundamentals

Class Duration

14 hours of live training delivered over 2 days.

Student Prerequisites

Comfortable working with data in some form (spreadsheets, code, or reports)
Basic familiarity with running commands or using developer tools
No prior SQL or database experience required

Target Audience

This course is designed for developers and analysts who are new to SQL: application developers who have leaned on ORMs or AI assistants without understanding the queries underneath, data analysts moving beyond spreadsheets, and anyone who needs to read, write, and reason about relational queries with confidence. The course is taught on PostgreSQL 18 but teaches standard SQL first, so the skills transfer directly to other relational databases.

Description

SQL Fundamentals gives participants a solid, durable foundation in the SQL language. The course is standards-first — grounded in the current SQL:2023 international standard — and taught hands-on using PostgreSQL 18, the latest major release of the world's most popular open source database. Participants begin with the relational model: tables, rows, keys, and why relational thinking has outlasted decades of alternatives. From there the course builds query skills layer by layer: SELECT fundamentals, filtering and sorting, joins of every type taught visually with diagrams before syntax, aggregation with GROUP BY and HAVING, subqueries and common table expressions, and a gentle introduction to window functions.

The second day turns to working with data rather than just reading it: INSERT, UPDATE, and DELETE; transaction basics and why atomicity matters; constraints and keys as the database's built-in safety net; and the practical realities of NULL semantics and data type choices. Participants develop intuition for when indexes help, learn to read simple execution plans without fear, and adopt query style habits that make SQL readable by teammates. The course closes with a timely topic: working with SQL in the age of AI assistants — how to review, verify, and correct generated SQL rather than trusting it blindly.

Learning Outcomes

Explain the relational model: tables, rows, columns, keys, and relationships
Write SELECT queries with confidence, from single-table lookups to multi-table reports
Filter and sort result sets precisely with WHERE, ORDER BY, LIMIT, and DISTINCT
Choose and write the correct join — inner, left, right, full, and cross — for any question
Summarize data with aggregate functions, GROUP BY, and HAVING
Decompose complex questions with subqueries and common table expressions (CTEs)
Apply basic window functions for rankings and running totals
Insert, update, and delete data safely, including the RETURNING clause
Use transactions to group changes atomically with BEGIN, COMMIT, and ROLLBACK
Read and define constraints: primary keys, foreign keys, unique, not-null, and check
Reason correctly about NULL in comparisons, aggregates, and joins
Select appropriate data types for text, numbers, dates, times, and booleans
Develop intuition for when an index will and will not help a query
Read a basic EXPLAIN plan and spot a sequential scan versus an index scan
Critically review SQL produced by AI assistants for correctness, safety, and performance

Training Materials

Comprehensive courseware is distributed online at the start of class. All students receive a downloadable MP4 recording of the training.

Software Requirements

Students will need a free, personal GitHub account to access the courseware, Docker Desktop or Podman to run PostgreSQL 18 locally, and a SQL client (VS Code with the PostgreSQL extension, pgAdmin, or psql). If students are unable to configure a local environment, a cloud-based environment can be provided.

Training Topics

The Relational Model and SQL

Tables, rows, columns, and schemas
Keys and relationships between tables
The SQL standard: what SQL:2023 covers and why it matters
PostgreSQL 18 as our teaching database
Standard SQL vs. database-specific dialects
Setting up and connecting to PostgreSQL

SELECT Fundamentals

Anatomy of a query
Selecting columns and expressions
Column aliases and computed values
DISTINCT and removing duplicates
Logical query processing order: how SQL actually evaluates

Filtering and Sorting

WHERE with comparison and logical operators
BETWEEN, IN, and LIKE pattern matching
Case sensitivity and string comparisons
ORDER BY: single and multiple sort keys
LIMIT, OFFSET, and standard FETCH FIRST

Joins, Taught Visually

Why data lives in multiple tables
INNER JOIN: matching rows, drawn before written
LEFT and RIGHT OUTER JOIN: keeping the unmatched
FULL OUTER JOIN and CROSS JOIN
Self-joins
Join conditions vs. filter conditions
Multi-table joins step by step

Aggregation and GROUP BY

COUNT, SUM, AVG, MIN, MAX
GROUP BY: one row per group
HAVING vs. WHERE
Grouping by multiple columns
Common aggregation mistakes and how to spot them

Subqueries and CTEs

Scalar subqueries in SELECT and WHERE
IN, EXISTS, and correlated subqueries
Derived tables in FROM
Common table expressions with WITH
Refactoring nested queries into readable CTEs

Window Functions: An Introduction

What windows add beyond GROUP BY
ROW_NUMBER, RANK, and DENSE_RANK
Running totals with SUM OVER
PARTITION BY and ORDER BY in windows
When to reach for a window function

Modifying Data

INSERT: single rows, multiple rows, and from queries
UPDATE with precise WHERE clauses
DELETE and the danger of the missing WHERE
The RETURNING clause for seeing what changed

Transactions Basics

Atomicity: all or nothing
BEGIN, COMMIT, and ROLLBACK
Autocommit behavior in clients
A first look at concurrent access and isolation

Constraints and Keys

Primary keys and surrogate vs. natural keys
Foreign keys and referential integrity
UNIQUE, NOT NULL, and CHECK constraints
DEFAULT values
How constraints catch bugs before they become data

NULL Semantics and Data Types

NULL: unknown, not zero, not empty
Three-valued logic in WHERE clauses
NULL in aggregates, joins, and sorting
COALESCE and NULLIF
Text, numeric, date/time, and boolean types
Choosing types: common mistakes to avoid

Indexing Intuition and Execution Plans

What an index is and what it costs
When indexes help: lookups, joins, sorts
When they do not: small tables, low selectivity
Reading EXPLAIN gently: scans, joins, and estimates
Asking the database why a query is slow

Query Readability and Style

Formatting conventions that aid review
Naming, aliasing, and commenting queries
Building complex queries incrementally
Writing SQL your teammates can maintain

SQL in the Age of AI Assistants

What AI assistants do well and poorly with SQL
Reviewing generated queries: correctness, NULL handling, joins
Verifying results against known answers
Safety: generated UPDATE and DELETE statements
Using AI as an accelerator, not a substitute for understanding