SQL Concepts for Data Engineers Course

Lesson 1 • Normalization Principles

Reduces data redundancy by applying normal forms up to 3NF. Prepares students to evaluate schema quality before querying.

Lesson 2 • Database Schemas and Catalogs

Explains how schemas organise objects within a database instance. Connects schema awareness to multi-team data engineering environments.

Lesson 3 • Relational Model Core Concepts

Introduces tables, rows, columns, and domains as the building blocks of relational data. Establishes vocabulary used throughout the course.

Lesson 4 • Entity-Relationship Modeling

Translates business requirements into ER diagrams before writing SQL. Provides a design-first approach to schema creation.

Lesson 1 • Pattern Matching and String Filters

Uses LIKE, wildcards, and string functions to filter text data. Supports common data-cleaning tasks in engineering workflows.

Lesson 2 • Filtering Rows with WHERE

Applies comparison and logical operators to restrict result sets. Directly enables targeted data extraction for pipeline inputs.

Lesson 3 • SELECT Statement Structure

Covers the logical and physical execution order of a SELECT query. Anchors all subsequent query-writing to a consistent mental model.

Lesson 4 • Expressions and Computed Columns

Builds derived values using arithmetic, string, and conditional expressions. Extends raw column data into pipeline-ready outputs.

Lesson 1 • DISTINCT and Deduplication

Removes duplicate rows using DISTINCT and COUNT DISTINCT. Supports data-quality checks common in ingestion validation steps.

Lesson 2 • Rollup and Cube Extensions

Generates subtotals and cross-tabulations using ROLLUP and CUBE. Enables multi-level summary reports without multiple queries.

Lesson 3 • Filtering Groups with HAVING

Applies post-aggregation filters using HAVING to remove unwanted groups. Distinguishes HAVING from WHERE in execution order.

Lesson 4 • Aggregate Functions Overview

Introduces COUNT, SUM, AVG, MIN, and MAX as the core aggregation toolkit. Establishes how aggregates collapse multiple rows into single values.

Lesson 1 • Inner Join Fundamentals

Returns only matching rows from both tables using INNER JOIN. Forms the baseline join pattern for most data engineering queries.

Lesson 2 • Self Joins and Cross Joins

Joins a table to itself and generates Cartesian products for specific use cases. Expands the join toolkit for hierarchical and combinatorial data.

Lesson 3 • Outer Joins and NULL Handling

Preserves unmatched rows using LEFT, RIGHT, and FULL OUTER JOIN. Critical for detecting missing relationships in pipeline data.

Lesson 4 • Set Operations on Result Sets

Combines or compares result sets using UNION, INTERSECT, and EXCEPT. Supports incremental load patterns and data reconciliation tasks.

Lesson 1 • EXISTS and NOT EXISTS

Tests for row existence using semi-join and anti-join patterns. Provides efficient alternatives to IN for large correlated datasets.

Lesson 2 • Recursive CTEs

Traverses hierarchical and graph-structured data using recursive WITH clauses. Enables parent-child traversal without procedural code.

Lesson 3 • Derived Tables in FROM

Uses subqueries in the FROM clause as virtual tables for further querying. Enables multi-step transformations within a single SQL statement.

Lesson 4 • Scalar and Inline Subqueries

Embeds subqueries in SELECT and WHERE clauses to compute single values. Introduces the concept of nested query execution.

Lesson 1 • Ranking Functions

Assigns ordinal positions using ROW_NUMBER, RANK, and DENSE_RANK. Supports deduplication, top-N selection, and leaderboard generation.

Lesson 2 • Window Function Syntax

Introduces the OVER clause and its components: PARTITION BY, ORDER BY, and frame. Establishes the execution model that distinguishes window from aggregate functions.

Lesson 3 • Aggregate Window Functions

Applies SUM, AVG, and COUNT over a sliding or cumulative window frame. Produces running totals and moving averages for time-series pipelines.

Lesson 4 • Offset Functions

Accesses preceding and following row values using LAG and LEAD. Enables period-over-period comparisons without self-joins.

Lesson 1 • Creating and Altering Tables

Defines table structure with CREATE TABLE and modifies it with ALTER TABLE. Covers the DDL operations central to schema management in pipelines.

Lesson 2 • Deleting and Truncating Data

Removes rows with DELETE and clears tables with TRUNCATE. Covers safe deletion patterns critical for pipeline idempotency.

Lesson 3 • Inserting and Updating Data

Loads and modifies rows using INSERT, UPDATE, and bulk insert patterns. Directly supports data ingestion and correction workflows.

Lesson 4 • Constraints and Indexes

Enforces data integrity with NOT NULL, UNIQUE, CHECK, and foreign key constraints. Introduces indexes as a performance tool tied to query patterns.

Lesson 1 • Query Rewriting Techniques

Rewrites inefficient queries using sargable predicates and join substitutions. Produces equivalent logic with lower execution cost.

Lesson 2 • Partitioning for Performance

Applies table partitioning to enable partition pruning and parallel scans. Scales query performance on large fact tables in data warehouses.

Lesson 3 • Index Design and Usage

Selects and creates indexes that align with query access patterns. Reduces full-table scans in high-frequency pipeline queries.

Lesson 4 • Understanding Execution Plans

Reads query execution plans to identify costly operations and bottlenecks. Connects plan analysis to targeted optimisation decisions.