lesson 60 Database engineering · advanced

DML: INSERT, UPDATE & DELETE

Insert, update and delete records safely.

the idea

INSERT adds rows; UPDATE changes matching rows; DELETE removes matching rows. Use WHERE to constrain modifications. These exercises touch only disposable copies in memory and are reset on every run.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

lab_items3 columns · 3 rows

idinteger · key	nametext	pricereal
1	Notebook	12
2	Lamp	45
3	Keyboard	80

all 3 rows

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

example.sql

INSERT INTO lab_items VALUES(4,'Pen',8);
UPDATE lab_items
SET price=price+5
WHERE id=1;
DELETE FROM lab_items
WHERE id=2;
SELECT id,name,price
FROM lab_items;

Watch for: An UPDATE without WHERE updates every row; that can be intentional only when the requirement says so.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1
Read rows before mutation. DML changes data. Note the current lab_items rows so you can see inserts, updates, and deletes afterward.
step-1.sql
sql›
```
SELECT id, name, price
FROM lab_items
ORDER BY id;
```
2
Insert, update, then delete. INSERT adds rows, UPDATE changes matching rows, DELETE removes them. Run the full script to see the net effect.
step-2.sql
sql›
```
INSERT INTO lab_items VALUES(4,'Pen',8);
UPDATE lab_items
SET price=price+5
WHERE id=1;
DELETE FROM lab_items
WHERE id=2;
SELECT id,name,price
FROM lab_items;
```