INSERT INTO

The full text of this chapter, to read or revisit. To practise, do the exercises.

▶ Do the exercises

Exercise 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.name

Always 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?

Options
• NULL, since no value and no default were given
• The INSERT fails: every column must receive a value
• The values of the previous row
• An empty text and 0

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.

Items to match
• 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')
Definitions
• One row, hometown and badges NULL
• Two rows in one statement
• Error: 2 columns listed, 1 value given
• Error: name is NOT NULL

Exercise 7 — Count the values

What happens with INSERT INTO trainers (name, hometown, badges) VALUES ('Sam', 'Azure City');?

Options
• An error: 3 columns are listed but only 2 values are given
• Sam is inserted with badges = NULL
• Sam is inserted with badges = 0
• Sam is inserted twice

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).

Starter code
-- Write your statements below, and finish with a SELECT to check the result
Back to the course