Application Development

SQL & Databases

Relational thinking, querying, joins, aggregation, schema design and indexes, taught against a real database engine rather than through an abstraction over it.

The course project

The Archive Database

A designed, normalized schema with real data loaded, indexed and queried, and every query explained.

0%
course

14 modules · 28 lessons

Each module ends with a practice test, a graded build, and a step of the course project.

Part I: Asking

01

Tables, Rows and Columns

The relational idea, and why it is not a spreadsheet.

02

SELECT and WHERE

Choosing columns, then choosing rows.

03

Order, Limit and Distinct

Arranging results, and the trap in taking the top ten.

04

NULL

Unknown is not empty, and it is not equal to anything, including itself.

Section project · coming soon

Ten Questions of a Database

Answer ten real questions from a teaching database with SELECT.

Part II: Combining

05

Joins

Putting tables back together, and the ways rows quietly vanish.

06

Outer Joins

When the absence is the answer.

07

Grouping and Aggregates

Many rows into one number, and the order the clauses actually run in.

08

Subqueries and CTEs

Queries inside queries, and how to write them so they can be read.

Section project · coming soon

Join Four Tables

Combine tables with joins and aggregates to build a report.

Part III: Building

09

Changing Data

INSERT, UPDATE, DELETE, and the safety habits that are not optional.

10

Designing a Schema

Store each fact once, and let the database enforce it.

Section project · coming soon

Design a Small Schema

Create tables, keys and constraints for a real community need.

Part IV: Making It Fast

11

Indexes

Why a query gets slow, and the one thing that usually fixes it.

12

Where the ORM Leaks

Going back to Django, now able to read what it writes.

Section project · coming soon

Find the Slow Query

Read a query plan and make a slow query fast with an index.

Part V: Confidence

13

Checking Your Own Queries

The questions to ask before you trust a number a query gave you.

Section project · coming soon

Change It Safely

Use transactions so a failed change leaves nothing half-done.

Part VI: Capstone

14

The Archive Database

Requirements and acceptance criteria. No instructions.

All courses