Practical SQL for Data Analysis and Applications

Write SQL for everyday data questions, from filtering and aggregation to joins, CTEs, and window functions. Check the meaning of each row and verify results before using them.

Introductory · no CS background required · 8 weeks

Questions and practice

Why did a total change after joining two tables? Which equipment has no readings, and what counts as its latest record? Write queries whose output can be checked against the original question.

Work through example problems and solutions in the course materials. Define the expected output, write SQL, inspect intermediate results, and compare with hand-checked rows and totals.

Before you start

Familiarity with spreadsheet rows and columns. Prior programming, database, and algorithms courses are not required. Small Python examples are introduced when connecting queries to an application.

This course and Practical Data Structures and Algorithms can be taken independently. Choose SQL first for table-based analysis, or algorithms first for Python data processing.

Eight-week plan

The syllabus is available. Weekly lectures, worked readings, and practice files are planned; their detailed scope will be finalized as the materials are prepared.

Week 1

Tables, grain, and basic queries

Schema, keys, SELECT, WHERE, ORDER BY, DISTINCT, LIMIT, and NULL.

Output: A basic query set with an explicit meaning for each output row.

Planned · course materials not yet available
Week 2

Aggregation and conditional summaries

GROUP BY, aggregates, HAVING, CASE WHEN, and the denominator of an average.

Output: A grouped report and manually checked counts, totals, and averages.

Planned · course materials not yet available
Week 3

Joins and matching grains

Primary/foreign keys, 1:N relationships, INNER/LEFT JOIN, and aggregation before joining.

Output: A multi-table query with row-count and total checks before and after each join.

Planned · course materials not yet available
Week 4

Subqueries, CTEs, and existence checks

Subqueries, WITH, EXISTS/NOT EXISTS, staged aggregation, and composite conditions.

Output: A multi-stage query with the grain, key, and expected rows written for every CTE.

Planned · course materials not yet available
Week 5

Window functions and event order

ROW_NUMBER, RANK, LAG, SUM OVER, partitions, and explicit window frames.

Output: Latest-record, change-from-previous, and cumulative-total queries.

Planned · course materials not yet available
Week 6

Schema constraints and controlled writes

CREATE TABLE, types, NOT NULL/UNIQUE/CHECK, INSERT/UPDATE/DELETE, and transactions.

Output: A small schema and a write exercise that checks affected rows and rollback behavior.

Planned · course materials not yet available
Week 7

Query plans and application use

Indexes, EXPLAIN QUERY PLAN, parameter binding, permissions, and concurrent-write concepts.

Output: A plan comparison and a parameterized query called from a small program.

Planned · course materials not yet available
Week 8

An integrated SQL workflow

Combine earlier examples into a checked report or monitoring data layer; review dialect and pipeline boundaries.

Output: Schema, queries, expected results, and a reproducible verification record.

Planned · course materials not yet available