SQL Fundamentals: Joins, NULL, Aggregate Functions, and Subqueries

Part 1 of 18 · ~2 min

Overview

Nobody in Monday's 9am leadership meeting is going to read your SQL. They're going to look at a slide, nod, and make a decision — which is exactly why a wrong number here is more dangerous than almost any other kind of bug you'll write. A crashed script gets noticed immediately. A query that quietly drops a department, quietly skips a row with a missing value, or quietly returns zero results when the honest answer was "everyone" produces no error at all. It produces a slide. And a wrong slide looks exactly as confident as a right one.

You've just inherited that report. Every Monday, before the meeting, five small queries run against two tables and turn into five slides: how big each team is, who might be underpaid relative to a teammate, who's the top earner on each team, which teams have seats nobody's filled, and a log of the last correction payroll made. All five queries run against the exact same two tables backing this site's own code lab — copy any statement out of this reference into that live SQL runner and it'll execute against this schema:

CREATE TABLE Departments (
  dept_id INTEGER PRIMARY KEY,
  dept TEXT NOT NULL
);

CREATE TABLE Employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL,
  salary INTEGER
);

INSERT INTO Departments (dept_id, dept) VALUES
  (1, 'Sales'), (2, 'IT'), (3, 'HR'), (4, 'Finance');

INSERT INTO Employees (id, name, dept, salary) VALUES
  (1, 'Alice', 'Sales', 50000),
  (2, 'Bob', 'Sales', 60000),
  (3, 'Carol', 'IT', 70000),
  (4, 'Dave', 'IT', NULL),
  (5, 'Eve', 'HR', 55000);

Two facts got baked into that seed data on purpose, and you're going to run into both of them, repeatedly, in ways that don't look related the first few times. Finance is a fully budgeted department with nobody hired into it yet — the req is open, the seat is real, the person isn't. And Dave started this week. He has a badge, a desk, and a row in this exact table — what he doesn't have is a number in salary, because payroll's onboarding paperwork hasn't cleared. Not $0. Not blank. Genuinely absent. Track these two going forward, because a surprising fraction of the wrong numbers a report like this can produce trace straight back to one of them — often in a spot you wouldn't have guessed.

Read front to back if you want to watch the report's five slides get built up in order, each one occasionally breaking something an earlier slide relied on. Already chasing a specific bug instead? The sidebar takes you straight to whichever chapter covers it.