Materialize a restock shortlist
Your task
Create a temporary table called restock_shortlist containing title and stock for albums with stock of two or less. Then return those columns from the new table sorted by title.
CREATE TABLE AS can save the result of a query into a new table.
Sample data
albums| id | title | genre | price | stock |
|---|---|---|---|---|
| 1 | Night Bus | Jazz | 18 | 7 |
| 2 | Static Bloom | Indie | 22 | 4 |
| 3 | Soft Circuit | Electronic | 24 | 0 |
| 4 | Blue Hour | Jazz | 16 | 3 |
| 5 | Coastline | Indie | 19 | 8 |
| 6 | Low Tide | Ambient | 21 | 2 |
Expected output
| title | stock |
|---|---|
| Low Tide | 2 |
| Soft Circuit | 0 |
Show a hint
Use CREATE TEMP TABLE restock_shortlist AS SELECT, then query the result.
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;Worked solution
CREATE TEMP TABLE restock_shortlist AS SELECT title,stock FROM albums WHERE stock<=2; SELECT title,stock FROM restock_shortlist ORDER BY title;Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.
Results
Loading SQLite…
