What a database is for

A file holds data. A table makes promises about it

Everything in this course could be kept in a JSON file. Programs do it every day, and it works until two things happen at once: two people write at the same moment, or one field turns out to be missing in row 4,812.

A table is the same rows with promises attached. Every column has a type. Some columns may never be empty. Some values may never repeat. A number in one table has to exist in another. The database refuses to store anything that breaks a promise, so the program that reads it later does not have to check.

// a file: every rule lives in the code that reads it, and is optional
[ { "name": "Vesper", "price": 890 },
  { "name": "Silica", "price": "640" },       // a string this time. nothing stopped it
  { "name": "Vesper", "lead_days": 10 } ]     // a second Vesper, and no price at all

// a table: the rules live with the data
create table doors (
  id        integer primary key,
  name      text    not null unique,          -- never empty, never repeated
  price     real    not null,                 -- always a number, always there
  lead_days integer not null
);

What you get that a file cannot give you

The last row is the one people underestimate. "Which customers ordered a pivot door in the last quarter but nothing since?" is a morning of code against a file, and four lines of SQL against a database — and the database will do it in milliseconds because it was built for exactly that question shape.

The database this course runs against

One dataset for all twenty-two lessons: a door factory. Six tables, small enough to read and awkward enough to be honest. There is a customer who has never ordered, a door nobody has bought, two missing cities and four jobs with no fitter assigned. Those awkward rows are where the interesting lessons live.

customers   id · name · city · country · joined
doors       id · name · style · material · price · lead_days · discontinued
orders      id · customer_id → customers.id · placed · status
order_items id · order_id → orders.id · door_id → doors.id · qty · unit_price
fitters     id · name · city · hourly
jobs        id · order_id → orders.id · fitter_id → fitters.id · day · hours

Six clauses, and they do not run in the order you write them

This single fact explains most beginner confusion. where cannot use a name invented in select, because the name does not exist yet. having filters groups, where filters rows. order by can use a select alias, because sorting happens after the columns are worked out. Keep this list in your head and SQL stops being surprising.

select   c.name, count(*) as orders
from     orders o
join     customers c on c.id = o.customer_id
where    o.status <> 'cancelled'
group by c.name
having   count(*) > 2
order by orders desc
limit    5;

from / join    →  collect the rows, joined together
where          →  throw away rows, one at a time
group by       →  fold the survivors into groups
having         →  throw away groups
select         →  work out the columns you asked for
order by       →  sort what is left
limit          →  take the first few

One language, several dialects

SQLite runs in this page, in phones and in small services. PostgreSQL is what most teams reach for on a server. MySQL is everywhere in older estates. SQL Server and Oracle run large companies.

Everything in the first thirteen lessons of this course is the same in all of them. What differs is at the edges: date functions, how an automatic id is declared, what limit is called, JSON support, and how strict the type system is. Where a dialect matters, the lesson says so.