SQL Challenger
0 of 250 complete

Lessons

0 of 250 complete

Saved in this browser

Lesson 163 of 250

Easy

Insert a shop order and return its ID

Your task

Insert order 211 for customer 7 on 2025-03-03 through the online channel, with status completed and total_cents 2600. Return the new id and status from the same statement.

RETURNING can show selected fields from a newly inserted row.

Sample data

Table:shop_orders
idcustomer_idordered_onchannelstatustotal_cents
20112025-01-04onlinecompleted2500
20222025-01-10storecompleted1800
20322025-01-10storecompleted3200
20432025-01-18marketplacerefunded5000
20542025-02-02storecompleted2700
20612025-02-04onlinecompleted4000
20752025-02-18onlinecancelled2400
20862025-02-28onlinecompleted1700
20942025-03-16onlinecompleted2900
21082025-03-09storecompleted3100

Expected output

idstatus
211completed
Show a hint

Use INSERT with an explicit column list and finish with RETURNING id, status.

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…