Read the record. Write the query.

Choosing columns

A query names the table and the columns you want back. The rest of the row stays in the table.

SELECT name, region, birth_year
FROM scholars
ORDER BY birth_year;

What to notice. birth_year is an integer, and the early years are negative. ORDER BY uses that number, not the spelling of the name.

Filtering rows

WHERE keeps the rows that match and drops the rest. Comparison is exact: 'italy' is not 'Italy'.

SELECT name, birth_year
FROM scholars
WHERE region = 'Italy'
ORDER BY birth_year;

What to notice. Two scholars are in Italy. The filter does not change the table. Run it again and the same two rows come back.

AND, OR, IN, and BETWEEN

AND narrows. OR widens. IN is a short OR. BETWEEN includes both ends.

SELECT name, region, birth_year
FROM scholars
WHERE region IN ('Italy', 'England')
  AND birth_year BETWEEN 1500 AND 1800
ORDER BY birth_year;

What to notice. Leonardo of Pisa is in Italy but was born before 1500, so he is out. Ada Lovelace is in England but was born after 1800, so she is out too.

Missing values

NULL means the value was never recorded. It is not zero, and it is not an empty string. Use IS NULL, not = NULL.

SELECT title, kind
FROM works
WHERE year IS NULL;

What to notice. Marginal star charts has no year. A comparison such as year = NULL never matches, in MySQL or here.

MySQL. MySQL uses the same IS NULL test. A column defined NULL is allowed to omit the value. NOT NULL forbids that.

Pattern matching

LIKE treats % as any sequence of characters. The rest of the pattern has to match exactly.

SELECT title, kind, year
FROM works
WHERE title LIKE 'On %'
   OR title LIKE '%Astron%'
ORDER BY year;

What to notice. 'On %' catches titles that start with On. '%Astron%' catches New Astronomy, which does not start that way.

MySQL. MySQL LIKE is the same. A backslash escapes % when you need a literal percent sign. Case folding depends on the column collation.

Sorting and limiting

ORDER BY arranges the rows. LIMIT keeps only the first of those arranged rows.

SELECT name, birth_year
FROM scholars
ORDER BY birth_year DESC
LIMIT 5;

What to notice. DESC puts the latest birth first. Without ORDER BY, LIMIT would keep an arbitrary five.

MySQL. MySQL writes this the same way. MySQL also allows LIMIT offset, count. The shared form is LIMIT count OFFSET offset.

Distinct values

DISTINCT collapses duplicate values in the result. It does not change how many works exist.

SELECT DISTINCT kind
FROM works
ORDER BY kind;

What to notice. There are three kinds, even though there are thirteen works. manuscript, paper, and treatise.

CASE

CASE turns a stored code into a phrase. It is an expression, so it sits in the select list like a column.

SELECT title,
       CASE kind
         WHEN 'manuscript' THEN 'copied by hand'
         WHEN 'paper' THEN 'printed note'
         ELSE 'bound treatise'
       END AS form
FROM works
ORDER BY form, title;

What to notice. ELSE covers treatise. If a kind matched nothing and there were no ELSE, the result would be NULL.

MySQL. MySQL uses the same CASE expression. The MySQL IF(kind = 'paper', 'printed note', 'other') function is a shortcut this engine does not have. CASE is the form that travels.

COALESCE

COALESCE returns the first argument that is not NULL. Use it when a missing value should display as a word.

SELECT title,
       COALESCE(CAST(year AS TEXT), 'undated') AS year_label
FROM works
ORDER BY title;

What to notice. Marginal star charts is the undated row. Every other year is cast to text so the column has one type.

MySQL. MySQL COALESCE is the same. IFNULL(year, 'undated') is the MySQL-only shortcut for two arguments.

Inner join

JOIN pairs rows that share a key. An inner join drops a row that has no partner.

SELECT s.name AS scholar, w.title, w.year
FROM works AS w
JOIN scholars AS s ON s.id = w.author_id
ORDER BY w.year;

What to notice. author_id on works points at scholars.id. Every work has an author, so this inner join returns every work.

Left join

A left join keeps every row of the left table. Columns from the right table are NULL when nothing matches.

SELECT w.title, l.name AS library
FROM works AS w
LEFT JOIN holdings AS h ON h.work_id = w.id
LEFT JOIN libraries AS l ON l.id = h.library_id
ORDER BY w.title, library;

What to notice. Marginal star charts is not on any shelf, so library is NULL. Elements sits in two libraries, so it appears twice.

Right join

A right join keeps every row of the right table. It is the left join with the tables swapped.

SELECT w.title, h.library_id, h.condition
FROM holdings AS h
RIGHT JOIN works AS w ON w.id = h.work_id
ORDER BY w.title;

What to notice. library_id NULL means the work is not held. On the Economy of Machinery is held, and its condition is the NULL, which is a different fact.

MySQL. MySQL has always had RIGHT JOIN. Write the tables in the order that makes the kept side obvious. When either order is readable, a LEFT JOIN is the usual choice.

Three tables

Each JOIN adds one table and one matching rule. The FROM line is a path through the keys, not a list of names.

SELECT s.name AS reader, w.title, r.year, r.hours
FROM readings AS r
JOIN scholars AS s ON s.id = r.scholar_id
JOIN works AS w ON w.id = r.work_id
ORDER BY r.year, reader;

What to notice. readings is the middle table. scholar_id leads to a person, work_id leads to a title, and the hours stay on the reading.

UNION

UNION stacks two results that share a shape. Duplicate rows are removed. UNION ALL keeps them.

SELECT s.name AS label, 'scholar' AS kind
FROM scholars AS s
WHERE s.region = 'England'
UNION
SELECT w.title, 'work'
FROM works AS w
WHERE w.kind = 'paper'
ORDER BY kind, label;

What to notice. The first leg is people, the second is papers. The kind column remembers which leg a row came from. MySQL and this engine both lack FULL OUTER JOIN. Build it from a left join, a right-side anti-join, and UNION.

GROUP BY

GROUP BY folds many rows into one row per group. COUNT, SUM, AVG, MIN, and MAX are computed per group.

SELECT region, COUNT(*) AS scholars
FROM scholars
GROUP BY region
ORDER BY scholars DESC, region;

What to notice. England has three scholars. Egypt and Italy have two. The other regions have one.

MySQL. MySQL also wants every selected column to be grouped or aggregated. ONLY_FULL_GROUP_BY, the default, rejects a bare name beside COUNT(*).

HAVING

WHERE filters rows before the groups form. HAVING filters the groups after the aggregates exist.

SELECT region, COUNT(*) AS scholars
FROM scholars
GROUP BY region
HAVING COUNT(*) >= 2
ORDER BY scholars DESC, region;

What to notice. Regions with a single scholar disappear. You cannot write WHERE COUNT(*) >= 2, because WHERE runs too early.

GROUP_CONCAT

GROUP_CONCAT packs the values of a group into one string. Galileo has two works, so his titles share a cell.

SELECT s.name,
       GROUP_CONCAT(w.title, ', ') AS titles
FROM scholars AS s
JOIN works AS w ON w.author_id = s.id
GROUP BY s.id, s.name
ORDER BY s.name;

What to notice. Scholars with no work are absent because this is an inner join. A left join would keep them, with a NULL list.

MySQL. MySQL uses GROUP_CONCAT(w.title ORDER BY w.title SEPARATOR ', '). The separator is a clause there, not a second argument. MySQL also caps the length with group_concat_max_len.

Pivot

A pivot turns values in a column into column headings. MySQL has no PIVOT keyword. One SUM of a CASE expression builds each new column.

SELECT s.region,
       SUM(CASE WHEN w.kind = 'manuscript' THEN 1 ELSE 0 END) AS manuscripts,
       SUM(CASE WHEN w.kind = 'paper' THEN 1 ELSE 0 END) AS papers,
       SUM(CASE WHEN w.kind = 'treatise' THEN 1 ELSE 0 END) AS treatises
FROM scholars AS s
JOIN works AS w ON w.author_id = s.id
GROUP BY s.region
ORDER BY s.region;

What to notice. England has no manuscripts, two papers, and one treatise: the Economy, the Notes, and the Principia. Italy has one of each. A zero is a real count, not a missing region.

MySQL. MySQL writes this same CASE form. There is no PIVOT (COUNT(kind) FOR kind IN (...)) operator. That operator belongs to SQL Server and Oracle. The reading lesson after unpivot shows it.

Unpivot

An unpivot turns columns back into rows. MySQL has no UNPIVOT keyword. UNION ALL stacks one select per column, and a label remembers which column the value came from.

SELECT name, 'birth' AS event, birth_year AS year
FROM scholars
UNION ALL
SELECT name, 'death', death_year
FROM scholars
ORDER BY name, event;

What to notice. Each scholar becomes two rows. Euclid's birth is -325 and his death is -265. The pivot lesson went from tall to wide. This one goes from wide to tall.

MySQL. MySQL writes this same UNION ALL. There is no UNPIVOT (year FOR event IN (birth_year, death_year)) operator. The next lesson is that operator, illustrated as Oracle.

PIVOT and UNPIVOT (Oracle)

Oracle folds the reshape into one operator. PIVOT names the aggregate, the column to spread, and the values that become headings. UNPIVOT names the value column, the label column, and the source columns.

SELECT region, manuscript, paper, treatise
FROM (
  SELECT s.region, w.kind
  FROM scholars AS s
  JOIN works AS w ON w.author_id = s.id
) AS sourced
PIVOT (
  COUNT(kind)
  FOR kind IN (manuscript, paper, treatise)
) AS pivoted;

SELECT name, event, year
FROM scholars
UNPIVOT (
  year FOR event IN (birth_year, death_year)
) AS unpivoted;

What to notice. Run shows an Oracle illustration of both statements. Empty pivot cells are NULL, not the zero from the CASE lesson. UNPIVOT labels each value with the source column, so the events are BIRTH_YEAR and DEATH_YEAR, not the words birth and death.

Oracle. These grids are what Oracle would return for the statements above. This page did not execute them. MySQL has neither operator. SQL Server uses the same operators and the same NULL for a missing pivot cell.

A subquery with IN

A subquery is a query inside a query. IN tests membership in that inner result.

SELECT title, year
FROM works
WHERE author_id IN (
  SELECT id
  FROM scholars
  WHERE region = 'England'
)
ORDER BY year;

What to notice. The inner query returns the three English scholars. The outer query returns the works they wrote.

EXISTS

EXISTS is true when the inner query returns any row. It does not care which columns, so the inner list is 1.

SELECT s.name
FROM scholars AS s
WHERE EXISTS (
  SELECT 1
  FROM readings AS r
  WHERE r.scholar_id = s.id
    AND r.hours >= 40
)
ORDER BY s.name;

What to notice. The long readings belong to Hypatia, Leonardo of Pisa, Newton, and Émilie du Châtelet. A scholar can match once even with several long readings.

String functions

Functions shape a value without changing the stored row. SUBSTR takes a start and a length. LENGTH counts characters.

SELECT name,
       SUBSTR(region, 1, 3) AS code,
       LENGTH(name) AS letters
FROM scholars
ORDER BY letters DESC
LIMIT 5;

What to notice. The code is the first three characters of the region. The longest names sort to the top, and LIMIT keeps five.

MySQL. MySQL spells the slice SUBSTRING(region, 1, 3) or SUBSTR. CHAR_LENGTH(name) counts characters. LENGTH(name) in MySQL counts bytes, which matters once names leave ASCII.

Year spans

These years are integers, so a life span is subtraction. Date functions are for values that are dates.

SELECT name,
       death_year - birth_year AS span
FROM scholars
WHERE birth_year IS NOT NULL
  AND death_year IS NOT NULL
ORDER BY span DESC;

What to notice. The longest span in this room is Hypatia. Negative birth years still subtract correctly.

MySQL. If birth and death were DATE columns, MySQL would use TIMESTAMPDIFF(YEAR, birth, death). Here you would use CAST(strftime('%Y', death) AS INTEGER) - CAST(strftime('%Y', birth) AS INTEGER). DATE_FORMAT(birth, '%Y') is the MySQL way to pull a year out of a date. Integer years need none of that.

INSERT

INSERT adds a row. List the columns you are filling so a later column added to the table does not shift your values.

INSERT INTO annotations (reading_id, topic, insight, page)
VALUES (1, 'method', 'Count what the margin already counted.', 4);

SELECT topic, insight, page
FROM annotations
WHERE topic = 'method';

What to notice. The id is omitted, so the table assigns the next one. Reset puts the original annotations back and drops this one. Refresh does the same.

MySQL. MySQL assigns the number with AUTO_INCREMENT. Here an INTEGER PRIMARY KEY does that job. The INSERT column list is the same in both.

UPDATE

UPDATE changes values already stored. The WHERE clause chooses the rows. Without it, every row in the table changes.

UPDATE readings
SET hours = hours + 1
WHERE id = 1;

SELECT id, hours
FROM readings
WHERE id = 1;

What to notice. Reading 1 is Hypatia on the Elements, 60 hours. After this statement it is 61. Reset restores 60.

DELETE

DELETE removes rows. WHERE decides which ones. The count afterward shows how many undated pages remain.

DELETE FROM annotations
WHERE page IS NULL;

SELECT COUNT(*) AS undated_pages
FROM annotations
WHERE page IS NULL;

What to notice. One annotation has no page: the note on translating Principia. After the delete, the count is 0. The other annotations stay.

Transactions

BEGIN starts a unit of work. COMMIT keeps it. ROLLBACK throws it away. Until one of those, the changes are provisional.

BEGIN;

UPDATE readings
SET hours = hours + 5
WHERE id = 1;

ROLLBACK;

SELECT id, hours
FROM readings
WHERE id = 1;

What to notice. The update adds 5 hours, then ROLLBACK removes that change. The select still shows 60, the original value.

MySQL. MySQL writes START TRANSACTION, then COMMIT or ROLLBACK. Autocommit is on, so a lone UPDATE is already committed. Open a transaction when several statements must succeed or fail together.

CREATE TABLE

CREATE TABLE names the columns and the rules. A primary key identifies the row. NOT NULL refuses a missing value.

DROP TABLE IF EXISTS scratch_notes;

CREATE TABLE scratch_notes (
  id INTEGER PRIMARY KEY,
  body TEXT NOT NULL
);

INSERT INTO scratch_notes (body)
VALUES ('A margin note for the next reader.');

SELECT id, body
FROM scratch_notes;

What to notice. scratch_notes is yours. It appears in the table list after you run this, and Reset drops it with everything else you added.

MySQL. MySQL would declare id INT PRIMARY KEY AUTO_INCREMENT. DROP TABLE IF EXISTS works in both. A foreign key is REFERENCES other_table (id), the same idea you can see on works.author_id.

Indexes

An index is a side structure the engine can use to find rows without reading the whole table. It does not change the result.

CREATE INDEX IF NOT EXISTS idx_readings_scholar
ON readings (scholar_id, year);

SELECT year, hours
FROM readings
WHERE scholar_id = 8
ORDER BY year;

What to notice. The index is on scholar and year, which matches the filter and the sort for Newton's three readings.

MySQL. MySQL has no IF NOT EXISTS on CREATE INDEX. Write CREATE INDEX idx_readings_scholar ON readings (scholar_id, year); running it twice is an error until you DROP INDEX.

Views

A view stores a query under a name. Selecting from the view runs that query. The rows are not copied into a new table.

DROP VIEW IF EXISTS scholar_hours;

CREATE VIEW scholar_hours AS
SELECT s.name AS name, SUM(r.hours) AS hours
FROM scholars AS s
JOIN readings AS r ON r.scholar_id = s.id
GROUP BY s.id, s.name;

SELECT name, hours
FROM scholar_hours
ORDER BY hours DESC;

What to notice. scholar_hours totals every recorded reading. Émilie du Châtelet leads, because Principia alone accounts for 80 hours.

ROW_NUMBER

A window function computes a value across related rows without folding them away. ROW_NUMBER assigns 1, 2, 3 in the order you give.

SELECT s.name,
       SUM(r.hours) AS hours,
       ROW_NUMBER() OVER (ORDER BY SUM(r.hours) DESC, s.name) AS place
FROM scholars AS s
JOIN readings AS r ON r.scholar_id = s.id
GROUP BY s.id, s.name
ORDER BY place;

What to notice. The place follows total hours, then name, so ties would still get distinct places. GROUP BY still returns one row per scholar. The window numbers those rows.

RANK and DENSE_RANK

RANK leaves a gap after a tie. DENSE_RANK does not. ROW_NUMBER never ties.

SELECT name,
       hours,
       RANK() OVER (ORDER BY hours DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY hours DESC) AS dense_rank
FROM (
  SELECT s.name AS name, SUM(r.hours) AS hours
  FROM scholars AS s
  JOIN readings AS r ON r.scholar_id = s.id
  GROUP BY s.id, s.name
) AS totals
ORDER BY rank, name;

What to notice. Compare rank and dense_rank on any shared hour total. If every total is unique, the two columns match, which is itself the lesson.

LAG and LEAD

LAG looks at the previous row in the window. LEAD looks at the next one. PARTITION BY restarts the sequence for each scholar.

SELECT s.name,
       r.year,
       r.hours,
       LAG(r.hours) OVER (PARTITION BY r.scholar_id ORDER BY r.year) AS previous_hours,
       LEAD(r.hours) OVER (PARTITION BY r.scholar_id ORDER BY r.year) AS next_hours
FROM readings AS r
JOIN scholars AS s ON s.id = r.scholar_id
ORDER BY s.name, r.year;

What to notice. Newton has three readings, in 1664, 1666, and 1669. His first previous_hours is NULL because nothing precedes it. A scholar with one reading has NULL on both sides.

A running total

SUM can be a window. With ORDER BY inside OVER, each row includes itself and the earlier rows of its partition.

SELECT s.name,
       r.year,
       r.hours,
       SUM(r.hours) OVER (
         PARTITION BY r.scholar_id
         ORDER BY r.year
       ) AS running_hours
FROM readings AS r
JOIN scholars AS s ON s.id = r.scholar_id
ORDER BY s.name, r.year;

What to notice. Newton's running hours go 40, then 65, then 77. Without the ORDER BY inside OVER, every row in the partition would show the same grand total.

Moving totals and averages

A frame cuts the window down to a few neighboring rows. ROWS BETWEEN 1 PRECEDING AND CURRENT ROW is this row and the one just before it.

SELECT s.name,
       r.year,
       r.hours,
       SUM(r.hours) OVER (
         PARTITION BY r.scholar_id
         ORDER BY r.year
         ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
       ) AS moving_total,
       AVG(r.hours) OVER (
         PARTITION BY r.scholar_id
         ORDER BY r.year
         ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
       ) AS moving_average
FROM readings AS r
JOIN scholars AS s ON s.id = r.scholar_id
ORDER BY s.name, r.year;

What to notice. Newton's hours are 40, then 25, then 12. The moving totals are 40, 65, and 37. The moving averages are 40, 32.5, and 18.5. The first row has no predecessor, so the frame is only itself. The running-total lesson keeps every earlier row. This one forgets them.

MySQL. MySQL 8 writes the same ROWS BETWEEN frame. ROWS counts positions. RANGE counts sort values, so two rows with the same year would both sit inside a RANGE frame. An OVER clause that names only ORDER BY defaults to RANGE UNBOUNDED PRECEDING through the current row.

Frames and EXCLUDE

EXCLUDE drops rows from a frame after the edges are chosen. CURRENT ROW drops this row. GROUP drops every peer that shares the sort value. TIES drops the other peers and keeps this row.

WITH pages AS (
  SELECT 1 AS pos, 4 AS amount
  UNION ALL SELECT 2, 10
  UNION ALL SELECT 3, 10
  UNION ALL SELECT 4, 10
)
SELECT pos,
       amount,
       SUM(amount) OVER (
         ORDER BY amount
         RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
         EXCLUDE CURRENT ROW
       ) AS exclude_current,
       SUM(amount) OVER (
         ORDER BY amount
         RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
         EXCLUDE GROUP
       ) AS exclude_group,
       SUM(amount) OVER (
         ORDER BY amount
         RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
         EXCLUDE TIES
       ) AS exclude_ties
FROM pages
ORDER BY amount, pos;

What to notice. The three 10s are one peer group under RANGE, because they share the sort value. For each of them, exclude current is 24 (the 4 plus the other two 10s), exclude group is 4 (the whole group of 10s is gone), and exclude ties is 14 (the 4 plus this 10). The lone 4 has nothing else in its group, so exclude current and exclude group are NULL, and exclude ties is 4.

MySQL. MySQL 8 writes EXCLUDE CURRENT ROW, EXCLUDE GROUP, EXCLUDE TIES, and EXCLUDE NO OTHERS the same way. RANGE is what makes equal amounts one peer group. ROWS would step one position at a time and would not treat the three 10s as a group.

MATCH_RECOGNIZE (Oracle)

Oracle walks the rows in order and looks for a pattern, the way a regular expression looks for a pattern in text. DEFINE names the kinds of row. PATTERN says how they must line up.

SELECT *
FROM readings
MATCH_RECOGNIZE (
  PARTITION BY scholar_id
  ORDER BY year
  MEASURES
    FIRST(down.year) AS start_year,
    LAST(down.year) AS end_year,
    COUNT(*) AS steps
  ONE ROW PER MATCH
  AFTER MATCH SKIP PAST LAST ROW
  PATTERN (down{2,})
  DEFINE
    down AS hours < PREV(hours)
);

What to notice. Newton's hours fall from 40 to 25 to 12, two steps down. The pattern down{2,} needs at least two declines, so the match starts in 1666 and ends in 1669. Émilie du Châtelet falls only once, from 80 to 16, and does not match. ONE ROW PER MATCH returns the partition column and the measures.

Oracle. This grid is what Oracle would return. This page did not execute the statement. MySQL has no MATCH_RECOGNIZE. Snowflake and Trino do.

A common table expression

WITH names a query so the rest of the statement can read it like a table. The name lasts for that statement only.

WITH long_reads AS (
  SELECT scholar_id, work_id, hours
  FROM readings
  WHERE hours >= 30
)
SELECT s.name, w.title, long_reads.hours
FROM long_reads
JOIN scholars AS s ON s.id = long_reads.scholar_id
JOIN works AS w ON w.id = long_reads.work_id
ORDER BY long_reads.hours DESC, s.name;

What to notice. long_reads keeps readings of 30 hours or more. The joins then replace the ids with a person and a title.

Recursive lineage

WITH RECURSIVE walks a graph. The first part of the UNION is the start. The second part joins the rows already found to the next edge.

WITH RECURSIVE lineage AS (
  SELECT w.id, w.title, 0 AS depth
  FROM works AS w
  WHERE w.title = 'Principia'

  UNION ALL

  SELECT cited.id, cited.title, lineage.depth + 1
  FROM lineage
  JOIN citations AS c ON c.citing_work_id = lineage.id
  JOIN works AS cited ON cited.id = c.cited_work_id
  WHERE lineage.depth < 6
)
SELECT depth, title
FROM lineage
ORDER BY depth, title;

What to notice. Principia is depth 0. It cites New Astronomy, Starry Messenger, and Elements directly. On the Revolutions appears at depth 2, and Elements appears again because the astronomy books cite it too. The depth guard stops a cycle from running forever. This citation list has no cycle.

MySQL. MySQL 8 writes the same WITH RECURSIVE … UNION ALL pattern. Older MySQL had no recursive CTE. The depth limit belongs in the recursive leg in both engines.

Reading a plan

EXPLAIN asks the engine how it would run a query. The result is a plan, not the rows of the query.

EXPLAIN QUERY PLAN
SELECT w.title
FROM works AS w
JOIN scholars AS s ON s.id = w.author_id
WHERE s.region = 'England';

What to notice. Look for the join order and whether a scan of works or scholars is enough at this size. An index matters more when the table grows. The answer rows do not change because you asked for a plan.

MySQL. MySQL uses EXPLAIN, or EXPLAIN ANALYZE to run the query and report actual times. EXPLAIN QUERY PLAN is the SQLite spelling. Read either one for join order and scans, not for the business answer.

Stored procedures

A MySQL procedure is a named program stored in the database. CALL runs it. This engine has no procedure language, so the statement below is here to read.

DELIMITER //
CREATE PROCEDURE scholar_span(IN scholar_name VARCHAR(120))
BEGIN
  SELECT name, death_year - birth_year AS span
  FROM scholars
  WHERE name = scholar_name;
END //
DELIMITER ;

CALL scholar_span('Ada Lovelace');

What to notice. The procedure would return one scholar's span. The year-span lesson is the same question as a query you can run. Procedures earn their place when several statements, branches, and grants must travel with the database.

DELIMITER changes the client separator so the semicolons inside the procedure do not end the CREATE. The engine in this browser will reject the script. That rejection is the lesson: procedures are MySQL (and other server databases), not part of the file running in the page.

User variables

MySQL session variables start with @. SET assigns one, and later statements in the same session can read it.

SET @region := 'England';

SELECT name, birth_year
FROM scholars
WHERE region = @region
ORDER BY birth_year;

What to notice. The runnable form of this question is the filtering lesson: put 'England' in the WHERE clause, or use a WITH clause to name it. Binding a parameter in an application driver is the safer version of the same idea.

:= assigns in a SET. = also assigns there, which is easy to confuse with a comparison. This page does not keep a MySQL session, so @region has nowhere to live. The queries you can run are still the whole curriculum above.