Log in Start learning

Module 2

SQL

Retrieve, combine and summarise data in relational databases — from first SELECT to window functions and query performance.

Difficulty:All beginner intermediate advanced expert

SQL Fundamentals

Databases, tables, keys and the anatomy of a query.

How relational databases work

✓How relational databases work — tables, keys and typesBefore writing SQL, understand what a database is, how tables relate through primary and foreign keys, and why data types matter.Beginner35 min

Selecting and filtering rows

✓SELECT, WHERE and sorting — your first real queriesRetrieve exactly the rows and columns you need, filter with conditions, sort results, remove duplicates and limit output.Beginner50 min

Aggregation and Logic

Summaries, grouping, NULLs and conditional logic.

Aggregating data

✓Aggregations, GROUP BY and HAVINGSummarise thousands of rows into business metrics, break them down by dimension, and filter groups.Beginner50 min

NULL and CASE

✓NULL and CASE WHEN — missing values and conditional logicHandle missing values correctly and build business categories and conditional calculations with CASE WHEN.Beginner40 min

Joins and Combining Data

Joining tables correctly and avoiding double counting.

Joins

✓Joins — combining tables without double countingINNER, LEFT, RIGHT, FULL and self joins, multi-table joins, and the fan-out problem that silently inflates totals.Intermediate60 min

Subqueries, CTEs and set operations

✓Subqueries, CTEs and UNIONBuild queries in steps with subqueries and common table expressions, compare rows to their own group with correlated subqueries, and stack results with UNION.Intermediate50 min

Functions

Dates, strings and numbers.

Date, string and numeric functions

✓Date, string and numeric functionsBucket dates into months and weeks, compute durations, clean text and round numbers — with notes for PostgreSQL and SQL Server.Intermediate45 min

Advanced Analytical SQL

Window functions and business query patterns.

Window functions

✓Window functions — ranking, LAG/LEAD, running totals and moving averagesCompute values across related rows without collapsing them — the most powerful analytical feature of SQL.Advanced70 min

Business query patterns

✓Business query patterns — period comparisons, customer-level analysis and cohortsThe queries analysts write every week — YoY and MTD comparisons, customer-level metrics, and cohort retention tables.Advanced70 min

Performance and Data Quality

Indexes, execution plans, validation and reconciliation.

Query performance

✓Query performance — indexes, execution plans and common problemsWhy some queries take milliseconds and others minutes, how indexes work, how to read an execution plan, and the habits that keep analytical SQL fast.Advanced45 min

Validation and reconciliation

✓Data validation and reconciliation in SQLProve that your numbers are right before anyone else finds out they are wrong — profiling, integrity checks and reconciliation to independent totals.Intermediate45 min

SQL · OrionStats