Set Up a Free Practice Database with SQLite

You learn SQL by running it. In this lesson you set up a free practice database on your own computer. It takes about fifteen minutes and you only do it once. Every later lesson uses the same small shop database, so you can run each example and see the same answers shown in the lesson.

Choose your tool

SQLite is a tiny database engine that stores everything in one file. There is no server to install and no password. Choose one of these free ways to use it. Any of them works for the whole course.

• DB Browser for SQLite: a free desktop app with a window where you type SQL and see results in a table. This is the easiest for most beginners. Search for “DB Browser for SQLite” and download it from its official site.
• The sqlite3 command line: a small program. It is already installed on many Mac and Linux computers. On Windows you can download the “sqlite-tools” package from the official SQLite website.
• An online SQLite playground: several free websites let you run SQLite in the browser with nothing to install. They come and go, so search for “online SQLite” and pick a well-known one. Do not paste private or work data into a website.

If you use the command line, open a terminal and type sqlite3 shop.db. This creates a file called shop.db in the current folder and opens a prompt. If you use DB Browser, choose New Database, save it as shop.db, and close the dialog that asks you to create a table. Then open the Execute SQL tab, which is where you will paste and run the script below.

Create the shop database

The script below does two things. First it creates four tables: customers, products, orders and order_items. Then it fills them with a small amount of data. You do not need to understand every word yet. By lesson 12 you will be able to read it line by line.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  joined DATE
);
CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  category TEXT,
  price INTEGER NOT NULL
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  order_date DATE,
  status TEXT,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);
CREATE TABLE order_items (
  id INTEGER PRIMARY KEY,
  order_id INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  quantity INTEGER NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

INSERT INTO customers (id, name, city, joined) VALUES
 (1, 'Asha Rao', 'Bengaluru', '2024-01-15'),
 (2, 'Ben Carter', 'London', '2024-02-03'),
 (3, 'Chidi Okafor', 'Lagos', '2024-02-20'),
 (4, 'Divya Nair', 'Chennai', '2024-03-11'),
 (5, 'Emma Wilson', 'Toronto', '2024-04-02'),
 (6, 'Farid Hassan', 'Nairobi', '2024-04-18'),
 (7, 'Grace Mwangi', 'Nairobi', '2024-05-09'),
 (8, 'Hari Singh', NULL, '2024-06-01');

INSERT INTO products (id, name, category, price) VALUES
 (1, 'Notebook', 'Stationery', 120),
 (2, 'Pen pack', 'Stationery', 80),
 (3, 'Desk lamp', 'Home', 650),
 (4, 'Backpack', 'Bags', 1400),
 (5, 'Water bottle', 'Home', 300),
 (6, 'Laptop stand', 'Office', 1800),
 (7, 'Mouse', 'Office', 700),
 (8, 'Sticky notes', 'Stationery', 60);

INSERT INTO orders (id, customer_id, order_date, status) VALUES
 (1, 1, '2024-06-02', 'delivered'),
 (2, 1, '2024-06-20', 'delivered'),
 (3, 2, '2024-06-21', 'delivered'),
 (4, 3, '2024-07-01', 'cancelled'),
 (5, 4, '2024-07-04', 'delivered'),
 (6, 5, '2024-07-09', 'shipped'),
 (7, 2, '2024-07-15', 'delivered'),
 (8, 6, '2024-07-18', 'delivered'),
 (9, 1, '2024-08-02', 'shipped'),
 (10, 7, '2024-08-05', 'pending');

INSERT INTO order_items (id, order_id, product_id, quantity) VALUES
 (1, 1, 1, 3),
 (2, 1, 2, 2),
 (3, 2, 4, 1),
 (4, 3, 6, 1),
 (5, 3, 7, 1),
 (6, 4, 3, 2),
 (7, 5, 5, 2),
 (8, 5, 1, 5),
 (9, 6, 7, 1),
 (10, 7, 8, 10),
 (11, 7, 1, 2),
 (12, 8, 3, 1),
 (13, 9, 6, 1),
 (14, 9, 5, 1),
 (15, 10, 2, 4);

Copy the whole script, paste it into your tool and run it. In DB Browser, press the play button or the keyboard shortcut shown on the tab. In the command line, paste it and press Enter, or save it as setup.sql and type .read setup.sql. If you run it twice you will see an error saying a table already exists. That is fine. Delete the shop.db file and start again, or run DROP TABLE commands first as you will learn later.

Run your first query

Now ask the database to show you everything in the customers table. The star * means “all columns”.

SELECT * FROM customers;

You should see eight rows. The last one, Hari Singh, has an empty city. Databases call an empty value NULL, meaning “not known”. NULL is important and has its own lesson. For now just notice it.

id | name         | city      | joined
1  | Asha Rao     | Bengaluru | 2024-01-15
2  | Ben Carter   | London    | 2024-02-03
3  | Chidi Okafor | Lagos     | 2024-02-20
4  | Divya Nair   | Chennai   | 2024-03-11
5  | Emma Wilson  | Toronto   | 2024-04-02
6  | Farid Hassan | Nairobi   | 2024-04-18
7  | Grace Mwangi | Nairobi   | 2024-05-09
8  | Hari Singh   | NULL      | 2024-06-01

A map of the shop database

Keep this picture in your head, because every lesson uses it.

• customers has 8 rows. Each customer has an id, a name, a city and the date they joined.
• products has 8 rows. Each product has an id, a name, a category and a price as a whole number.
• orders has 10 rows. Each order has an id, a customer_id that points to a customer, an order_date and a status such as delivered, shipped, pending or cancelled.
• order_items has 15 rows. Each one says which product was in which order and the quantity. One order can have several items.

Prices are plain numbers like 120 or 1800 so the sums are easy to check in your head. Think of them as any currency you like. Dates are written as year-month-day, for example 2024-06-02. This format is the safest in databases because sorting the text also sorts the dates correctly.

The basic rules of writing SQL

• A statement ends with a semicolon ;. Some tools forgive a missing one, but write it anyway.
• SQL keywords like SELECT and FROM are not case sensitive. Writing them in capitals is a habit that makes queries easier to read.
• Text values go in single quotes: 'Nairobi'. Numbers do not: 500.
• You can split a statement over many lines. Spaces and line breaks do not change its meaning.
• Comments start with two dashes: -- this is a note. The database ignores them.

If something goes wrong

Errors are normal and the messages are helpful. “no such table” means you spelled a table name wrong, or the setup script did not run. “no such column” means a column name is misspelled. “near ...: syntax error” means the database got confused near the word it shows, so look for a missing comma, a missing quote or an extra comma just before a FROM. Read the message, look at the word it points to, and fix one thing at a time.

Your task

1. Install one of the tools above and create shop.db.
2. Run the setup script. Then run SELECT * FROM products; and count the rows. You should see 8.
3. Run SELECT * FROM orders; and SELECT * FROM order_items;. Note the row counts (10 and 15) and look at how customer_id and order_id link rows to other tables.
4. Add a comment line above a query, run it again, and confirm nothing changes.

How to check it worked

• SELECT * FROM customers; returns 8 rows and Hari Singh has no city.
• You can say which table holds the quantity of items in an order (order_items).
• You know how to open a fresh start: delete the file and run the script again.