INSERT INTO
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — Adding a row
Until now you have only read data. INSERT INTO adds a row: name the table, list the columns you fill, then give one value per column, in the same order.
Run it — the SELECT at the end shows the result:
INSERT INTO trainers (name, hometown, badges)
VALUES ('Casey', 'Moonlight Hollow', 1);
SELECT * FROM trainers;Casey is the fifth row. You did not give a trainer_id: the column is an INTEGER PRIMARY KEY, so the database numbers the row itself.
Text goes in single quotes, numbers are written bare.
Your sandbox resets on every run
In CodeLife, to simplify learning, each run starts from the original data: your changes last until the end of the run. That is why every example ends with a SELECT — it is the only way to see what you did. Run the example twice: still one Casey, no duplicate.
Exercise 2 — Which columns, in which order
The column list is yours: any order, and you may skip columns. A skipped column gets NULL (or its default value, if the table defines one):
INSERT INTO trainers (badges, name)
VALUES (0, 'Riley');
SELECT * FROM trainers WHERE name = 'Riley';badges came first in the list, so 0 came first in VALUES. hometown was not listed: it is NULL.
What you cannot skip is a column declared NOT NULL without a default — like name here. Try it:
INSERT INTO trainers (hometown, badges)
VALUES ('Azure City', 2);
-- Error: NOT NULL constraint failed: trainers.nameAlways list the columns
INSERT INTO trainers VALUES (5, 'Casey', 'Moonlight Hollow', 1) also works, but then you must give every column in table order, id included. Add a column to the table one day and the statement breaks. Listing the columns is the safe habit.
Exercise 3 — The skipped column
INSERT INTO trainers (name) VALUES ('Sam'); — what do hometown and badges contain for Sam?
Hint : Nothing given, nothing stored: that is what NULL means.
Exercise 4 — Jordan's first Codémon
Complete the statement so that it gives Jordan (trainer_id 4) a new Codémon: Ghostchu, type ghost, level 7.
Hint : The first value is a name; the last one is Jordan's id.
Exercise 5 — Several rows at once
One INSERT can add several rows: one pair of parentheses per row, separated by commas.
INSERT INTO trainers (name, hometown, badges)
VALUES ('Casey', 'Moonlight Hollow', 1),
('Riley', 'Sunset Valley', 0);
SELECT trainer_id, name, hometown, badges FROM trainers ORDER BY trainer_id;The values can also come from a query: INSERT INTO … SELECT … copies the rows that the SELECT returns. Here, a junior for every Azure City trainer:
INSERT INTO trainers (name, hometown, badges)
SELECT name || ' Jr.', hometown, 0
FROM trainers
WHERE hometown = 'Azure City';
SELECT name, hometown, badges FROM trainers ORDER BY trainer_id;The id of the row you just added
last_insert_rowid() returns the id generated by the latest INSERT. Handy to link one row to it right away: INSERT INTO codemon (name, type, level, trainer_id) VALUES ('Phantochu', 'ghost', 6, last_insert_rowid()); — but it changes after every insert, so the next statement would already see Phantochu's id.
Exercise 6 — What happens?
trainers has the columns trainer_id, name (NOT NULL), hometown, badges. Match each statement with its outcome.
INSERT INTO trainers (name) VALUES ('Sam')INSERT INTO trainers (name, badges) VALUES ('Sam', 2), ('Kim', 0)INSERT INTO trainers (name, badges) VALUES ('Sam')INSERT INTO trainers (hometown) VALUES ('Azure City')Exercise 7 — Count the values
What happens with INSERT INTO trainers (name, hometown, badges) VALUES ('Sam', 'Azure City');?
Hint : Columns and values must match one for one.
Exercise 8 — Two recruits for Jordan
Complete the statement so that Jordan (trainer_id 4) receives two Codémons in one INSERT: Ghostchu and Leafchu (a plant Codémon of level 5).
Hint : A name is text, so it needs quotes; both Codémons go to the same trainer.
Exercise 9 — New recruit
Professor Pi signs a new trainer: Casey, from Moonlight Hollow, 0 badges — and Casey's first Codémon, Phantochu (type ghost, level 6, captured on 2023-04-01).
Insert the trainer, then the Codémon linked to Casey (last_insert_rowid() gives you Casey's new id). Finish with a query showing trainer, codemon, type and level for Casey's team (join the two tables).
-- Write your statements below, and finish with a SELECT to check the result