Description

Login
Back

Beginner · 12 min

Writing sample data

Insert, update, and delete rows in an isolated database.

Sample database

CREATE TABLE groups (id INTEGER PRIMARY KEY, label TEXT NOT NULL);
CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT, group_id INTEGER REFERENCES groups(id));
CREATE TABLE sessions (id INTEGER PRIMARY KEY, member_id INTEGER NOT NULL REFERENCES members(id), topic TEXT NOT NULL, minutes INTEGER NOT NULL CHECK(minutes > 0), completed INTEGER NOT NULL CHECK(completed IN (0, 1)), day TEXT NOT NULL);
INSERT INTO groups VALUES (1, 'Morning'), (2, 'Evening');
INSERT INTO members VALUES (1, 'Ada', 'Oslo', 1), (2, 'Bo', 'Rome', 1), (3, 'Cy', NULL, 2), (4, 'Dee', 'Oslo', 2);
INSERT INTO sessions VALUES
  (1, 1, 'Travel', 20, 1, '2026-01-01'),
  (2, 1, 'Food', 10, 0, '2026-01-02'),
  (3, 2, 'Travel', 30, 1, '2026-01-01'),
  (4, 2, 'Music', 15, 1, '2026-01-03'),
  (5, 3, 'Music', 25, 0, '2026-01-02'),
  (6, 3, 'Travel', 15, 1, '2026-01-04');

Insert named fields

INSERT with explicit column names makes the value mapping clear. Required columns and constraints still apply. This playground recreates the sample database on every run, so these changes affect only the current exercise.

INSERT INTO members (id, name, city, group_id) VALUES (5, 'Eli', 'Lima', 2);
SELECT name FROM members WHERE id = 5;

Output

name
Eli

Target updates and deletes

UPDATE and DELETE affect every matching row; omitting WHERE can affect the whole table. Preview the intended rows and use a precise condition. The two changes here target different, known rows.

UPDATE members SET city = 'Lima' WHERE id = 3;
DELETE FROM members WHERE id = 4;
SELECT id, city FROM members ORDER BY id;

Output

id | city
1 | Oslo
2 | Rome
3 | Lima

Put it into practice

  1. Read the sample tables and predict the result before running the query.
  2. Solve the task, compare the returned rows, and explain why the solution works.

Try it yourself

Code runs on this device. When you are signed in, drafts sync to your account.

Run queries on a fresh sample database. Table changes last for this run only; your query draft is saved separately. Results show column names followed by rows. NULL means a missing value.

Apply what you learned

Update only member 3 to the city Lima, then return all member IDs and cities in ID order.

Show solution
UPDATE members SET city = 'Lima' WHERE id = 3;
SELECT id, city FROM members ORDER BY id;

The targeted update changes the missing city while preserving the other members.

Practice