SQL Course

Lesson 1 • Setting Up a Practice Environment

Guides installation of a local database engine and a GUI client. Students run their first connection and verify the environment before writing any SQL.

Lesson 2 • What Is a Relational Database

Defines relational databases and contrasts them with flat files and NoSQL stores. Establishes the data model that SQL operates on throughout the course.

Lesson 3 • Normalization Principles

Covers first through third normal forms and the reasoning behind each. Prepares students to read and design schemas they will query throughout the course.

Lesson 1 • Sorting and Limiting Results

Covers ORDER BY direction and LIMIT or TOP syntax for result control. Sorting and pagination are essential for readable, performant output.

Lesson 2 • Filtering Rows with WHERE

Teaches comparison and logical operators to restrict result sets. Filtering is the most frequent real-world SQL task and underpins all later query types.

Lesson 3 • SELECT and FROM Clauses

Introduces the minimal query structure and column selection syntax. Connects directly to the table and column concepts established in Chapter 1.

Lesson 1 • Grouping Data with GROUP BY

Explains how GROUP BY partitions rows before aggregation. Students build multi-level summaries that mirror common analytical reporting needs.

Lesson 2 • Core Aggregate Functions

Introduces COUNT, SUM, AVG, MIN, and MAX with practical examples. Aggregates transform row-level data into the summary metrics most reports require.

Lesson 3 • Distinct Values and Counting

Uses DISTINCT and COUNT(DISTINCT) to analyze unique values within groups. Extends aggregate skills to cardinality and deduplication scenarios.

Lesson 1 • Joining Three or More Tables

Extends join skills to multi-table chains and bridge tables for many-to-many relationships. Builds the query complexity needed for real-world reporting.

Lesson 2 • Self Joins and Cross Joins

Covers joining a table to itself and generating Cartesian products. These specialized joins solve hierarchical and combinatorial problems in real schemas.

Lesson 3 • INNER JOIN Fundamentals

Teaches the INNER JOIN syntax and the matching logic behind it. INNER JOIN is the most common join type and the foundation for all other joins.

Lesson 1 • Common Table Expressions (CTEs)

Introduces WITH clause CTEs as readable alternatives to nested subqueries. CTEs are the preferred modern pattern for organizing complex query logic.

Lesson 2 • Subqueries in FROM and EXISTS

Uses derived tables in FROM and EXISTS for existence checks. These patterns replace complex joins and improve query expressiveness.

Lesson 3 • UNION, INTERSECT, and EXCEPT

Combines result sets from multiple queries using set operators. Students learn column alignment rules and deduplication behavior for each operator.

Lesson 1 • Inserting and Updating Rows

Teaches INSERT, UPDATE, and bulk insert patterns with safe WHERE usage. Accurate data modification is essential for any application-facing database role.

Lesson 2 • Creating and Altering Tables

Covers CREATE TABLE with data types and constraints, plus ALTER TABLE for schema changes. Schema design decisions made here affect all downstream queries.

Lesson 3 • Deleting Data Safely

Explains DELETE and TRUNCATE with their behavioral differences and risks. Students practice safe deletion patterns before touching production-like data.

Lesson 1 • Aggregate Window Functions

Applies SUM, AVG, COUNT, and similar functions over a window frame. Running totals and moving averages are the most requested analytical outputs.

Lesson 2 • Offset Functions: LAG and LEAD

Uses LAG and LEAD to access prior and next row values within a partition. Period-over-period comparisons are a core analytical requirement in business reporting.

Lesson 3 • FIRST_VALUE, LAST_VALUE, and NTH_VALUE

Retrieves boundary and positional values within a window partition. These functions complete the toolkit for peer-group and benchmark comparisons.

Lesson 4 • Window Function Syntax and Concepts

Explains the OVER clause, PARTITION BY, and ORDER BY within windows. Understanding the window frame is prerequisite to all functions in this chapter.

Lesson 1 • Indexes: Types and Usage

Covers B-tree, composite, and covering indexes and when each applies. Correct indexing is the single highest-impact performance lever available to SQL writers.

Lesson 2 • Query Rewriting Best Practices

Applies sargability rules, join order hints, and CTE materialization awareness. These rewriting habits produce consistently faster SQL across different database engines.

Lesson 3 • Identifying and Fixing Slow Queries

Uses EXPLAIN and EXPLAIN ANALYZE to locate bottlenecks in real queries. Students rewrite inefficient patterns using the techniques introduced in this chapter.