SQL Challenger
0 of 250 complete

Lessons

0 of 250 complete

Saved in this browser

Lesson 168 of 250

Moderate

Apply stock changes from a delivery file

Your task

Create a temporary table named delivery with album_id and copies. Add a delivery of four copies for album 2 and six copies for album 3, then increase matching stock values by those amounts. Return id and stock for every album, sorted by ID.

A staging table keeps incoming changes separate until you are ready to apply them.

Sample data

Table:albums
idtitlegenrepricestock
1Night BusJazz187
2Static BloomIndie224
3Soft CircuitElectronic240
4Blue HourJazz163
5CoastlineIndie198
6Low TideAmbient212

Expected output

idstock
17
28
36
43
58
62
Show a hint

Use UPDATE FROM delivery with a condition matching the album ID.

Writing and modeling data guide

Define the shape of a table

A primary key identifies a record. NOT NULL makes a value mandatory, and CHECK restricts allowed values. Put these rules in the table definition so SQLite enforces them on writes. The PRAGMA query below inspects the column declarations.

CREATE TABLE deliveries (
  id INTEGER PRIMARY KEY,
  album_id INTEGER NOT NULL REFERENCES albums(id),
  copies INTEGER NOT NULL CHECK (copies > 0)
);
PRAGMA table_info(deliveries);

Preview the rows before changing them

In an update or delete, WHERE determines which rows change. Try the same condition in a SELECT first. The write exercises start from a fresh practice database on each run, so include all the needed statements in one submission.

SELECT id, title, stock FROM albums WHERE id = 3;
UPDATE albums SET stock = stock + 5 WHERE id = 3;
SELECT id, title, stock FROM albums WHERE id = 3;

Group changes into a transaction

BEGIN starts a transaction. COMMIT keeps its changes and ROLLBACK undoes them. Here the final SELECT shows the original stock because the UPDATE was rolled back.

BEGIN;
UPDATE albums SET stock = 0 WHERE id = 1;
ROLLBACK;
SELECT title, stock FROM albums WHERE id = 1;
SQLiteCtrl / ⌘ + Enter

Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.

Results

Loading SQLite…