What You Will Learn in This SQL Course

How each lesson works

Welcome. Before we talk about SQL itself, here is how a lesson is built, so you always know where you are.

A lesson is a short stack of slides. Many slides show a small table, the query that runs on it, and the result that comes back. Between the slides there is a quick check with three options, so you find out straight away whether the idea landed. You can read in silence, or turn the narration on and follow the words as they light up.

After the slides come ten questions on the same material. Most concepts then add a hands-on practice of six steps, where you complete a real query yourself and see what it returns.

One more comfort. The whole course uses a single small data world, a music store with artists, albums, tracks, customers and invoices. The same rows come back lesson after lesson, so your attention stays on the SQL.

Block one: ask one table

The first block gives you the vocabulary and your first real answer. You meet the relational model, see how a SQL statement is written, learn how values, types and expressions behave, and meet the special marker that means no value was recorded here. Then you write your first query, and you learn how to add, change and remove rows.

Here are four rows of the tracks table:

By the end of the block, this is the kind of query you write:

SELECT name, unit_price
FROM tracks
WHERE unit_price > 0.99
ORDER BY name;

You are not expected to understand that syntax today. Read it as a promise instead: after block one, the question "which tracks cost more than 0.99, listed by name?" is one you answer yourself.

Block two: join two tables

One table rarely holds the whole answer. Titles live in albums, names live in artists, and only a shared number links them.

Block two brings tables together. You will give tables short aliases and pair their rows with inner, cross and outer joins, including the rows that have no partner. You will also join a table to itself, join three tables at once, and stack one result on top of another with set operations.

So, which albums belong to AC/DC?

SELECT albums.title
FROM albums
JOIN artists
  ON albums.artist_id = artists.artist_id
WHERE artists.name = 'AC/DC'
ORDER BY albums.title;

Two tables went in and one clean answer came out. That is the whole point of the block.

Block three: summarise a group

Sometimes you do not want the rows themselves; you want what they add up to. Block three teaches the aggregate functions that count, add and average, the grouping that applies them once per artist or once per month, the condition that filters whole groups, and window functions, which calculate across related rows without folding them into one.

Six album rows go in:

SELECT artists.name, COUNT(*) AS n_albums
FROM albums
JOIN artists
  ON albums.artist_id = artists.artist_id
GROUP BY artists.name
ORDER BY artists.name;

Four rows come out, one per artist. Notice that block three stands on block two: the join is still there, and the counting sits on top of it.

Block four: put one query inside another

Block four lets one query use the answer of another. You learn subqueries that give back a single value, a single row or a whole table; correlated subqueries, which are worked out again for every row; the existence test, which only asks whether any matching row is there; and common table expressions, which give a subquery a name at the top of the statement and can even walk down a chain of rows step by step.

The store has five artists, but only four of them have an album. Which one is missing?

SELECT name
FROM artists
WHERE NOT EXISTS (
  SELECT 1 FROM albums
  WHERE albums.artist_id = artists.artist_id
)
ORDER BY name;

The inner query runs for each artist and asks one small question: is there an album for this artist? The outer query keeps only the artists for which the answer is no.

Block five: build your own tables

Until now the tables were already there. Block five hands you the design. You create your own tables, give a column a default value, let the database work out a column from the others, and add the rules that keep the data honest: a column that may not be left empty, a value that may not repeat, and a reference that must point at a row that really exists. You also learn to change a table that is already in use, without losing what is inside it.

CREATE TABLE label_notes (
  artist_id INTEGER PRIMARY KEY,
  note      VARCHAR(40) NOT NULL,
  active    INTEGER DEFAULT 1
);
INSERT INTO label_notes (artist_id, note)
VALUES (1, 'back catalogue');
SELECT * FROM label_notes;

The insert never mentions the third column, and the stored row still has a value there. That is what a default does for you.

Block six: change several rows as one unit

Some changes only make sense together. Block six is about the transaction: a group of statements that succeeds as one unit, or leaves the data exactly as it was. Inside a transaction you can also set a savepoint, a marker you can come back to, so you undo part of the work and keep the rest.

The two tracks below both cost 1.29 before the change.

BEGIN;
UPDATE tracks SET unit_price = 1.49
 WHERE track_id = 8;
SAVEPOINT s1;
UPDATE tracks SET unit_price = 1.49
 WHERE track_id = 9;
ROLLBACK TO SAVEPOINT s1;
COMMIT;

Both updates ran, but the work done after the savepoint was undone before the group was committed. Track 8 keeps its new price, and track 9 goes back to the old one.

Block seven: save a query and find rows faster

Block seven is about two comforts. A view saves a query under a name and then behaves like a table for everyone who reads it, so a long query written once can be used again without being written again. An index is a structure the database keeps beside a table so it can find matching rows without reading every one of them. The database decides when to use it, and on a small table it may read the rows instead.

CREATE VIEW standard_tracks AS
SELECT name, unit_price
FROM tracks
WHERE unit_price = 0.99;
SELECT * FROM standard_tracks
ORDER BY name;

The second statement reads standard_tracks as if it were a table, and it brings back the two tracks priced 0.99 from the four rows you saw in block one. An index changes nothing in this result. At most it changes how the rows are reached, and only when the database judges that route the cheaper one.

Block eight: everyday functions

The last block collects the small tools you reach for every day. You learn the conditional expression that chooses a value row by row, the conversion that turns one kind of value into another, the text functions that cut, join and clean up strings, and the date functions that pull a year or a month out of a date.

SELECT name,
       CASE WHEN unit_price > 0.99
            THEN 'premium' ELSE 'standard'
       END AS band
FROM tracks
ORDER BY name;

The four tracks keep their four rows, and each one gets a label decided by its own price. These eight blocks teach SQL as the standard describes it, so the ideas carry over between database products and the skill follows you when the product changes. Syntax, data types and functions still differ in places, so read the manual of the product you land on and run a statement there before you trust it.

Key takeaways

• A lesson is slides with small tables and quick checks, then ten questions, and, where the concept carries one, a six-step practice on the same music store.
• Block one gives you one table: filter its rows, sort them, and add, change or remove them.
• Block two joins tables, so an album can be shown next to the name of its artist.
• Block three summarises, so that counting and adding turn many rows into one row per group.
• Block four puts one query inside another, so a query can use what a second query found.
• Block five is where you create your own tables and the rules that protect them, and block six groups changes so they succeed or fail together.
• Block seven saves a query as a view and speeds up the search with an index, and block eight adds the everyday choices, conversions, text tools and date tools.
• The order matters: each block uses what the one before it gave you, and the ideas carry over between database products, with the syntax and the types checked against the manual of each one.