The bakery, stage 2: the data

In short: Stage 2 fills the bakery tables with INSERT, RETURNING and INSERT … SELECT, and records the first order inside a transaction.

Problem

Empty tables

The tables from stage 1 are ready and empty. The bakery has three categories, eight products, five customers and its first orders. In this stage you write them in, and every rule from stage 1 will guard the data.

All the fields start from the database you have made so far: the five tables and the data from the earlier steps of the stage.

The idea

The first row

INSERT INTO table (columns) VALUES (values) writes a row. The column id is not in the list: INTEGER PRIMARY KEY without a value gets the next number on its own.

SQLempty database

INSERT INTO categories (name) VALUES ('Bread');
SELECT * FROM categories;

INSERT INTO categories (name) VALUES ('Bread'); SELECT * FROM categories;

▶ RunRestore the queryFresh database

The columns in brackets matter. INSERT INTO categories VALUES (1, 'Bread') works too, but it depends on the order of the columns in the table and breaks with the first new column.

Try it

The three categories

Write the three categories with one INSERT: Bread, Pastries and Drinks, in this order (bread is 1, pastries 2, drinks 3).

SQLempty database

INSERT INTO categories (name) VALUES
  ('Bread');

INSERT INTO categories (name) VALUES ('Bread');

Check▶ Run

The idea

What the database wrote

The program often needs the number of the new row, for example to show "The product was added with number 1". RETURNING returns the columns of the written row:

SQLempty database

INSERT INTO products (name, category_id, price, stock)
VALUES ('Country bread', 1, 2.40, 30)
RETURNING id, name, stock;

INSERT INTO products (name, category_id, price, stock) VALUES ('Country bread', 1, 2.40, 30) RETURNING id, name, stock;

▶ RunRestore the queryFresh database

RETURNING also returns the values the database put in on its own: without stock in the list it would return 0 from DEFAULT. The eight products are written with one INSERT:

SQLempty database

INSERT INTO products (name, category_id, price, stock) VALUES
  ('Country bread', 1, 2.40, 30),
  ('Rye bread', 1, 2.80, 20),
  ('Baguette', 1, 1.60, 25),
  ('Croissant', 2, 1.30, 40),
  ('Kozunak', 2, 6.50, 10),
  ('Baklava', 2, 3.90, 15),
  ('Coffee', 3, 1.80, 100),
  ('Ayran', 3, 1.20, 30);
SELECT * FROM products;

INSERT INTO products (name, category_id, price, stock) VALUES ('Country bread', 1, 2.40, 30), ('Rye bread', 1, 2.80, 20), ('Baguette', 1, 1.60, 25), ('Croissant', 2, 1.30, 40), ('Kozunak', 2, 6.50, 10), ('Baklava', 2, 3.90, 15), ('Coffee', 3, 1.80, 100), ('Ayran', 3, 1.20, 30); SELECT * FROM products;

▶ RunRestore the queryFresh database

More precisely, if you are curious

RETURNING exists in SQLite since version 3.35 and in PostgreSQL. MySQL does not have it: there the number is taken with LAST_INSERT_ID(), and in SQLite also with last_insert_rowid().

See it

The rules guard

The rules from stage 1 are now working. A product with price 0:

SQLempty database

INSERT INTO products (name, category_id, price)
VALUES ('Baguette', 1, 0);

INSERT INTO products (name, category_id, price) VALUES ('Baguette', 1, 0);

▶ RunRestore the queryFresh database

A product in category 9, which does not exist:

SQLempty database

INSERT INTO products (name, category_id, price)
VALUES ('Baguette', 9, 1.60);

INSERT INTO products (name, category_id, price) VALUES ('Baguette', 9, 1.60);

▶ RunRestore the queryFresh database

A product without a name:

SQLempty database

INSERT INTO products (category_id, price)
VALUES (1, 1.60);

INSERT INTO products (category_id, price) VALUES (1, 1.60);

▶ RunRestore the queryFresh database

None of the three goes in. The error comes from the database, so it does not matter who sends the row: the site, an import script from Excel or a colleague in the console.

Try it

The customers

Write the five customers with one INSERT (columns name, email, city): Ivan Kolev, ivan.kolev@example.com, Sofia; Ana Petrova, ana.petrova@example.com, Plovdiv; Boris Nikolov, boris.nikolov@example.com, Sofia; Desislava Marinova, desislava.marinova@example.com, Varna; Kalin Todorov, kalin.todorov@example.com, no city.

SQLempty database

INSERT INTO customers (name, email, city) VALUES
  ('Ivan Kolev', 'ivan.kolev@example.com', 'Sofia');

INSERT INTO customers (name, email, city) VALUES ('Ivan Kolev', 'ivan.kolev@example.com', 'Sofia');

Check▶ Run

More precisely, if you are curious

UNIQUE compares the text letter by letter: Ivan.Kolev@example.com and ivan.kolev@example.com are two different e-mails for the database. That is why the program turns the e-mail into small letters before writing it, or the table keeps UNIQUE on lower(email) with an index (CREATE UNIQUE INDEX … ON customers (lower(email))).

See it

The same e-mail

SQLempty database

INSERT INTO customers (name, email, city)
VALUES ('Ivan Kolev', 'ivan.kolev@example.com', 'Plovdiv');

INSERT INTO customers (name, email, city) VALUES ('Ivan Kolev', 'ivan.kolev@example.com', 'Plovdiv');

▶ RunRestore the queryFresh database

Ivan from Plovdiv is another person with the same e-mail, and the database does not let him in. Signing up on the site shows exactly this error as "This e-mail is already registered".

The idea

The order and its rows

An order without status gets 'new' from DEFAULT:

SQLempty database

INSERT INTO orders (customer_id, ordered_at)
VALUES (1, '2026-10-01')
RETURNING id, status;

INSERT INTO orders (customer_id, ordered_at) VALUES (1, '2026-10-01') RETURNING id, status;

▶ RunRestore the queryFresh database

A real order, though, is two kinds of rows: one in orders and several in order_items. If the program crashes between them, an order without a single product is left. That is why the two are written in a transaction: BEGIN, the inserts, COMMIT. Until COMMIT nothing is final.

Try it

The first order

Ivan (customer 1) ordered on 2026-10-01 two country breads (product 1 at 2.40) and three croissants (product 4 at 1.30). The order is delivered. Write it in one transaction: the order (it will be number 1) and its two rows.

SQLempty database

BEGIN;
INSERT INTO orders (customer_id, ordered_at, status) VALUES (1, '2026-10-01', 'delivered');
COMMIT;

BEGIN; INSERT INTO orders (customer_id, ordered_at, status) VALUES (1, '2026-10-01', 'delivered'); COMMIT;

Check▶ Run

See it

Cancelling

The program has written the order and finds out that something is wrong (the card was declined, a row stopped with an error). Then instead of COMMIT it sends ROLLBACK:

SQLempty database

BEGIN;
INSERT INTO orders (customer_id, ordered_at) VALUES (2, '2026-10-02');
SELECT COUNT(*) AS during FROM orders;
ROLLBACK;
SELECT COUNT(*) AS after FROM orders;

BEGIN; INSERT INTO orders (customer_id, ordered_at) VALUES (2, '2026-10-02'); SELECT COUNT(*) AS during FROM orders; ROLLBACK; SELECT COUNT(*) AS after FROM orders;

▶ RunRestore the queryFresh database

During the transaction there are 2 orders, and after ROLLBACK 1 again: the second has disappeared, as if there had been no INSERT.

More precisely, if you are curious

Here every field stops at the first error, like the console. The program does the same with try: on an exception it sends ROLLBACK. The transaction is not cancelled on its own when one query fails, so ROLLBACK is the program's job.

See it

The other orders

Orders 2 to 6 are written the same way. Here they are, with the number of orders by status at the end:

SQLempty database

INSERT INTO orders (customer_id, ordered_at, status) VALUES (2, '2026-10-02', 'delivered');
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (2, 5, 1, 6.50), (2, 7, 2, 1.80);
INSERT INTO orders (customer_id, ordered_at, status) VALUES (1, '2026-10-08', 'delivered');
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (3, 3, 2, 1.60), (3, 8, 2, 1.20);
INSERT INTO orders (customer_id, ordered_at, status) VALUES (3, '2026-10-10', 'cancelled');
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (4, 6, 4, 3.90);
INSERT INTO orders (customer_id, ordered_at, status) VALUES (4, '2026-10-15', 'paid');
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (5, 1, 1, 2.40), (5, 4, 6, 1.30), (5, 7, 1, 1.80);
INSERT INTO orders (customer_id, ordered_at, status) VALUES (2, '2026-10-20', 'new');
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (6, 2, 1, 2.80), (6, 6, 2, 3.90);
SELECT status, COUNT(*) AS orders
FROM orders
GROUP BY status
ORDER BY status;

INSERT INTO orders (customer_id, ordered_at, status) VALUES (2, '2026-10-02', 'delivered'); INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (2, 5, 1, 6.50), (2, 7, 2, 1.80); INSERT INTO orders (customer_id, ordered_at, status) VALUES (1, '2026-10-08', 'delivered'); INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (3, 3, 2, 1.60), (3, 8, 2, 1.20); INSERT INTO orders (customer_id, ordered_at, status) VALUES (3, '2026-10-10', 'cancelled'); INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (4, 6, 4, 3.90); INSERT INTO orders (customer_id, ordered_at, status) VALUES (4, '2026-10-15', 'paid'); INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (5, 1, 1, 2.40), (5, 4, 6, 1.30), (5, 7, 1, 1.80); INSERT INTO orders (customer_id, ordered_at, status) VALUES (2, '2026-10-20', 'new'); INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (6, 2, 1, 2.80), (6, 6, 2, 3.90); SELECT status, COUNT(*) AS orders FROM orders GROUP BY status ORDER BY status;

▶ RunRestore the queryFresh database

Order 4 is cancelled, 5 is paid and waits for delivery, and 6 is new. Kalin (customer 5) has not ordered yet.

Exercise

The price from the product

The site does not know the prices by heart. When the customer buys, the price is taken from products at that moment and written into the row. INSERT … SELECT writes the rows that a SELECT returns.

Kalin (customer 5) orders two baguettes (product 3) today. Write the order (it will be number 7, the date is date('now')) and its row, taking unit_price from products with INSERT … SELECT.

SQLempty database

INSERT INTO orders (customer_id, ordered_at) VALUES (5, date('now'));

INSERT INTO orders (customer_id, ordered_at) VALUES (5, date('now'));

Check▶ Run

The idea

The full database

The six orders are in the database. How many rows each table has:

SQLempty database

SELECT (SELECT COUNT(*) FROM categories) AS categories,
       (SELECT COUNT(*) FROM products) AS products,
       (SELECT COUNT(*) FROM customers) AS customers,
       (SELECT COUNT(*) FROM orders) AS orders,
       (SELECT COUNT(*) FROM order_items) AS items;

SELECT (SELECT COUNT(*) FROM categories) AS categories, (SELECT COUNT(*) FROM products) AS products, (SELECT COUNT(*) FROM customers) AS customers, (SELECT COUNT(*) FROM orders) AS orders, (SELECT COUNT(*) FROM order_items) AS items;

▶ RunRestore the queryFresh database

The next stage asks questions about this data: the menu, the bill of every order, who buys what.

Test

Check yourself

1. In INSERT INTO categories (name) VALUES (…) there is no id. Where does it come from?

id INTEGER PRIMARY KEY fills itself with the next numberIt has to be written, otherwise it is an errorIt stays NULL

2. The order went in, and its row stopped with an error. What does the program do?

Deletes the tableROLLBACK: it cancels the order too, so that there is no empty order without rowsNothing, the order is there

3. Why is the price of the new order row taken with SELECT … FROM products and not written as 1.60 by hand?

It is fasterSQLite does not accept numbers in VALUESThe price is kept in one place. A number written by hand goes out of date when the product gets dearer

Check

Start the lesson over

Related lessons

• Builds on: Constraints: rules for the data · Natural keys and denormalization · ACID: all or nothing · BEGIN, COMMIT, ROLLBACK
• Tasks on this topic: Project: the bakery from scratch