The bakery, stage 5: the database works on its own in SQL

Problem

The things you have to remember

In stage 4 the stock was lowered by a query written by hand, and the sum of an order was worked out with three tables every time. The bakery grows: the orders become thousands, and more people write queries. Things that depend on someone remembering get forgotten sooner or later.

This stage hands the work over to the database: an index for fast searching, a view for the sums, a trigger for the store and a new column for the phone.

The idea

How the database searches

The page "My orders" asks for the orders of one customer. EXPLAIN QUERY PLAN shows how the database will do it:

SQLempty database

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 2;

SCAN orders: the database reads every order and looks at the customer. With six orders it does not matter, with a million it is slow.

Try it

The index

Make an index orders_customer on the column customer_id of orders.

SQLempty database

SELECT * FROM orders WHERE customer_id = 2;

Solution

CREATE INDEX orders_customer ON orders (customer_id);

The same query with the index:

SQLempty database

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 2;

SEARCH … USING INDEX: the database goes straight to the orders of customer 2.

More precisely, if you are curious

SQLite does not make an index for a foreign key on its own, nor does PostgreSQL. A column that points to another table almost always wants an index: tables are joined and searched by it. The index is not free, though: every INSERT and UPDATE updates it.

Try it

The view

The sum of an order is three tables and a GROUP BY every time. A view is a query with a name: you read from it like from a table.

Make a view order_totals with the number of the order (id), the customer (customer_id), the status and the sum (total, rounded to 2 places) of every order.

SQLempty database

CREATE VIEW order_totals AS
SELECT id, customer_id, status
FROM orders;

Solution

CREATE VIEW order_totals AS
SELECT o.id, o.customer_id, o.status,
       ROUND(SUM(i.quantity * i.unit_price), 2) AS total
FROM orders o
JOIN order_items i ON i.order_id = o.id
GROUP BY o.id;

See it

The view as a table

How much every customer has spent, without the cancelled orders. The sum with the three tables is hidden in the view:

SQLempty database

SELECT c.name, SUM(t.total) AS spent
FROM order_totals t
JOIN customers c ON c.id = t.customer_id
WHERE t.status <> 'cancelled'
GROUP BY c.id
ORDER BY spent DESC;

The view does not keep data, it keeps its query. A new order shows up in it at once:

SQLempty database

INSERT INTO orders (id, customer_id, ordered_at) VALUES (7, 5, '2026-10-25');
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (7, 3, 2, 1.60);
SELECT * FROM order_totals WHERE id = 7;

Try it

The trigger

A trigger is code that the database runs on its own on an INSERT, UPDATE or DELETE in a table. In its body new is the new row.

Make a trigger take_stock: after every new row in order_items the stock of the product in the row goes down by its pieces.

SQLempty database

CREATE TRIGGER take_stock
AFTER INSERT ON order_items
BEGIN
  SELECT 1;
END;

Solution

CREATE TRIGGER take_stock
AFTER INSERT ON order_items
BEGIN
  UPDATE products SET stock = stock - new.quantity
  WHERE id = new.product_id;
END;

See it

The trigger and the rules

With the trigger Kalin orders 20 kozunaks, and there are 10:

SQLempty database

INSERT INTO orders (id, customer_id, ordered_at) VALUES (7, 5, '2026-10-25');
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (7, 5, 20, 6.50);

The UPDATE in the trigger breaks CHECK (stock >= 0). The trigger is part of the INSERT, so the row of the order does not go in either: the database has no row saying "20 kozunaks sold" when they are not there.

More precisely, if you are curious

Triggers keep a rule in one place, but they are hidden: whoever reads the program's code does not see them. That is why they are used sparingly, for things that must always happen, whatever writes to the database. The stock here is such a thing, and sending an e-mail is not.

Try it

A new column

The bakery wants to call the customers when the order is ready. The table has data, so it is not made again: ALTER TABLE adds a column to it.

Add to customers a column phone of type TEXT that may be missing.

SQLempty database

SELECT * FROM customers;

Solution

ALTER TABLE customers ADD COLUMN phone TEXT;

With NOT NULL it does not work:

SQLempty database

ALTER TABLE customers ADD COLUMN phone TEXT NOT NULL;

More precisely, if you are curious

ALTER TABLE in SQLite can add, rename and (since 3.35) drop columns. It cannot add a constraint like CHECK or a foreign key to an existing column: then the table is made again (a new table, INSERT … SELECT, DROP of the old one and a rename). In PostgreSQL ALTER TABLE … ADD CONSTRAINT does it directly.

The idea

What comes next

The database is full, fast and keeps its rules on its own. The last stage is the end: a report, a copy of the database and DROP of everything in the right order.

Test

Check yourself

1. Does the view order_totals keep the sums of the orders?No, it keeps the query. The sums are worked out every time someone reads from the viewYes, like a tableIt keeps them until a restart

2. The trigger lowers the stock, and the product runs out. What happens to the row in order_items?It is not written: the trigger is part of the INSERT and the error in it cancels the row tooIt is written with NULLIt is written, and the stock stays 0

3. Why does ALTER TABLE customers ADD COLUMN phone TEXT NOT NULL give an error?The customers who already exist would have an empty phone, and NOT NULL does not allow itALTER TABLE does not exist in SQLiteTEXT cannot be NOT NULL

The bakery, stage 5: the database works on its own

Cookies