Lab: explore a shop database

Open a made-up shop's database in Database Manager and use everything from this track on it: browse, query, join, make a mistake inside a transaction and undo it, speed up a lookup with an index, and take a backup you have tested.

Level: beginner · Time: 45 minutes · Before this: lessons 1 to 4; lesson 5 helps with part 6

What you need

• Database Manager installed. See Install.
• The practice database:

• shop.sqlite — a made-up stationery shop, as one SQLite file (48 KB)

Everything in it is invented: the names are made up and every email address is at example.com. You can break it as much as you like and download a fresh copy at any time.

Table	Rows	Holds
customers	30	Name, email, city, date joined
products	20	Name, category, price in cents, stock
orders	120	Which customer, when, and the status
order_items	305	Which products are in each order, how many, at what price
store_credit	5	A balance for customers 1 to 5

Part 1: open the database

1. Download shop.sqlite and move it to a folder of its own, for example db-lab in your home folder.

2. Open Database Manager and select New connection.

3. At the top of the dialog, make sure SQLite is selected. In Connection name, type Shop lab.

4. Select Browse and pick shop.sqlite. Its path appears in SQLite file. Leave the password field empty: this file is not encrypted.

5. Select Save, then select Shop lab in the list.

   Expected: the workspace opens. The table list on the left shows customers, order_items, orders, products and store_credit. The first table is selected and the toolbar reads customers · 30 rows.

The steps match the Beginner guide of Database Manager; look there if anything on screen is unclear.

Part 2: browse and find rows

1. Select each table in turn and compare the row count in the toolbar with the table above.

   Expected: 30, 305, 120, 20 and 5 rows. orders shows ids from 1001 up; order_items shows pairs of order_id and product_id.

2. Select products. In the toolbar, select the funnel button (Find rows without writing a query).

3. Select Add a condition (e.g. "name contains john"). Choose stock, then is less than, and type 10.

4. In the Sort by list, pick stock. Leave the direction at Smallest first. Select Find rows.

   Expected: the toolbar reads products · 5 rows (filtered). The grid shows, from the top: Eraser, pack of 3 (stock 0), Dot grid journal (3), Fountain pen (3), Stapler (6) and Gel pen set, 10 colours (8).

5. Select Show the query this runs.

   Expected: the SQL behind your search, the same WHERE … ORDER BY … LIMIT you met in lesson 2:

   SELECT * FROM "products"
   WHERE "stock" < 10
   ORDER BY "stock"
   LIMIT 100;

   Double quotes around a name mark it as a table or column name; single quotes mark text values.

6. Select Clear to see every product again.

Part 3: run queries

Select the Query tab. For each query below, type it in the box (or copy it), select Run (Ctrl+Enter), and compare the grid with the expected result. The count to the right of the button, such as 6 rows · 2 ms, should match the number of rows shown; the time will differ.

1. Customers per city, biggest first:

   SELECT city, COUNT(*) AS customers FROM customers
   GROUP BY city
   ORDER BY customers DESC, city;

   Expected: 6 rows.

   city	customers
   Chennai	6
   Bristol	5
   Glasgow	5
   Kochi	5
   Leeds	5
   Pune	4

2. Orders by status:

   SELECT status, COUNT(*) AS orders FROM orders
   GROUP BY status
   ORDER BY orders DESC;

   Expected: delivered 74, shipped 18, placed 17, cancelled 11.

3. What is in order 1001? A join with a calculated column:

   SELECT p.name, oi.quantity, oi.unit_price_cents,
          oi.quantity * oi.unit_price_cents AS line_cents
   FROM order_items AS oi
   JOIN products AS p ON p.id = oi.product_id
   WHERE oi.order_id = 1001;

   Expected: 2 rows.

   name	quantity	unit_price_cents	line_cents
   Pencil sharpener	2	149	298
   Metal ruler 30 cm	1	299	299

4. The five best customers, not counting cancelled orders. Three tables joined:

   SELECT c.name, SUM(oi.quantity * oi.unit_price_cents) AS spent_cents
   FROM customers AS c
   JOIN orders AS o ON o.customer_id = c.id
   JOIN order_items AS oi ON oi.order_id = o.id
   WHERE o.status <> 'cancelled'
   GROUP BY c.id
   ORDER BY spent_cents DESC
   LIMIT 5;

   Expected:

   name	spent_cents
   Farid Martin	44319
   Dev Byrne	34395
   Wen Carter	33993
   Isla Nair	30685
   Asha Raman	27365

5. Customers who have never ordered, with a LEFT JOIN:

   SELECT c.name, c.city
   FROM customers AS c
   LEFT JOIN orders AS o ON o.customer_id = c.id
   WHERE o.id IS NULL;

   Expected: Bea Mehta (Pune), Carlos Okafor (Bristol), Dina Nair (Kochi) and Eli Raman (Glasgow).

6. Sales by category:

   SELECT p.category, SUM(oi.quantity * oi.unit_price_cents) AS revenue_cents
   FROM order_items AS oi
   JOIN products AS p ON p.id = oi.product_id
   GROUP BY p.category
   ORDER BY revenue_cents DESC;

   Expected: Desk 157564, Pens 122787, Art 77544, Pencils 62870, Paper 53674.

7. Save that result: select Export CSV next to Run and save query_result.csv in your db-lab folder.

   Expected: a message confirms the export. Open the file in a text editor: the first line is category,revenue_cents, then one line per category.

If a result differs, check the query for a typing mistake first, then download a fresh shop.sqlite: an earlier experiment may have changed the data.

Part 4: a mistake you can undo

Now delete every order item on purpose, inside a transaction, and take it back. Run each statement on its own, one per Run.

1. Start a transaction:

   BEGIN;

   Expected: a row(s) affected line. For BEGIN, ROLLBACK and CREATE INDEX the number is left over from an earlier statement; ignore it.

2. Make the mistake:

   DELETE FROM order_items;

   Expected: 305 row(s) affected.

3. Look at the damage:

   SELECT COUNT(*) AS items FROM order_items;

   Expected: 0. Select order_items in the table list: the toolbar reads order_items · 0 rows. Inside your transaction, the rows are gone. Select the Query tab again; your text is still there.

4. Undo it:

   ROLLBACK;

5. Check:

   SELECT COUNT(*) AS items FROM order_items;

   Expected: 305. Every item is back, as if the DELETE never happened.

Without the BEGIN, step 2 would have been permanent the moment you selected Run. That is why Edit data safely asks for a backup first.

Part 5: add an index and compare the plan

1. Ask SQLite how it would find customer 2's orders:

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

   Expected: one row. Its last column, detail, reads SCAN orders: SQLite would read all 120 orders.

2. Create an index on the foreign key:

   CREATE INDEX idx_orders_customer ON orders(customer_id);

3. Run the EXPLAIN QUERY PLAN from step 1 again.

   Expected: detail now reads SEARCH orders USING INDEX idx_orders_customer (customer_id=?). SQLite jumps straight to customer 2's orders.

4. List the indexes in the database:

   SELECT name, tbl_name FROM sqlite_master WHERE type = 'index';

   Expected: 3 rows: sqlite_autoindex_customers_1 (made for the UNIQUE email), sqlite_autoindex_order_items_1 (made for the two-column primary key) and your idx_orders_customer. Indexes do not appear in the table list on the left.

With 120 rows you will not feel the difference. On a table with millions of rows, this is what turns a slow page into a fast one. See Joins and keys.

Part 6: back up, then prove the backup works

1. Select Backup in the title bar and choose your db-lab folder.

   Expected: a message Backup created: with the path of a file named like shop_backup_20261109_142530.db, with today's date and time.

2. Select the back arrow in the title bar to close the database.

3. Select New connection, keep SQLite, name it Shop backup, select Browse and pick the new shop_backup_…db file. Select Save, then open Shop backup.

4. In the Query tab, check the file is healthy:

   PRAGMA integrity_check;

   Expected: one row: ok.

5. Check it holds your latest change, the index from part 5:

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

   Expected: SEARCH orders USING INDEX idx_orders_customer (customer_id=?). The backup is a complete copy, including the index.

6. Close Shop backup with the back arrow and open Shop lab again.

You now have a backup you have actually opened and checked, which is the only kind worth having. See Export and back up.

Break it, try it

Each experiment shows a safeguard from the track at work. Use the Shop lab connection, and run the statements one at a time, as in part 4.

A transfer that must fail

Move 20.00 of store credit from customer 5, who has 7.50, to customer 4. One statement per run:

BEGIN;
UPDATE store_credit SET balance_cents = balance_cents + 2000 WHERE customer_id = 4;
UPDATE store_credit SET balance_cents = balance_cents - 2000 WHERE customer_id = 5;

The third statement fails with a red error that includes CHECK constraint failed: balance_cents >= 0. Now run:

SELECT * FROM store_credit WHERE customer_id IN (4, 5);

Customer 4 shows 4500, customer 5 still 750: the first update is still in the open transaction. Run ROLLBACK; and the same SELECT again: 2500 and 750. Nothing happened. This is lesson 4 in action.

Back up in the middle of a transaction

Run BEGIN;, then select Backup. It fails: the message starts with Backup failed: and mentions cannot VACUUM from within a transaction. SQLite cannot copy the database while your unfinished change is open. Run ROLLBACK;, and Backup works again.

An order for a customer who does not exist

INSERT INTO orders (customer_id, ordered_at, status) VALUES (999, '2026-07-01 10:00', 'placed');

It works: 1 row(s) affected. There is no customer 999, but SQLite does not check foreign keys unless asked. Find the orphan and remove it:

SELECT id, customer_id, status FROM orders WHERE customer_id = 999;
DELETE FROM orders WHERE customer_id = 999;

The SELECT shows one row, order 1121. Now turn the checks on and try again:

PRAGMA foreign_keys = ON;
INSERT INTO orders (customer_id, ordered_at, status) VALUES (999, '2026-07-01 10:00', 'placed');

This time the insert fails with FOREIGN KEY constraint failed. The setting lasts until you close the database; reopen it and the checks are off again. See lesson 3.

A duplicate email

INSERT INTO customers (name, email, city, joined)
VALUES ('Test Person', 'ben.carter@example.com', 'Leeds', '2026-11-09');

It fails with UNIQUE constraint failed: customers.email, because Ben Carter already has that address.

Your own questions

Try writing these yourself. Use a SELECT only; check your answer against the hint.

• Which products are out of stock? (One: Eraser, pack of 3.)
• How many orders has Asha Raman placed? (11.)
• Which products has nobody ever ordered? (Two: Brush set and Washi tape, 5 rolls. Use a LEFT JOIN from products to order_items.)

Clean up

1. Select the back arrow to close the database. Any transaction you left open is rolled back.

2. On the start screen, open the menu (three dots) of Shop lab and select Delete. Do the same for Shop backup. This removes only the saved connections, not the files.

3. Delete the db-lab folder with shop.sqlite, the backup and query_result.csv, or keep them for more practice.

Check yourself

QuizLab: explore a shop database

Question 1 of 6

In the find-rows panel you choose stock, is less than, 10, and sort by stock. Which SQL does Show the query this runs show?

Score: 0

What you learned

• Database Manager opens a SQLite file through a saved connection, browses its tables and shows the SQL behind a search.
• Joins and GROUP BY answer real questions in one query.
• A BEGIN before a risky statement means ROLLBACK can undo it.
• EXPLAIN QUERY PLAN shows whether a query scans the table or uses an index.
• A backup counts only once you have opened it and checked it.

Next steps

• Safety and backups, if you have not read it yet
• Edit data safely before you open a database that matters
• Connect to a database for MySQL, PostgreSQL, MongoDB and LMDB
• Learning tracks: pick your next field