You can read about SQL forever, but the only way to learn it is to type queries and watch them run. This lesson sets up a database you can practise on for the rest of the course — pick whichever option fits your environment, install it once, and load the e-commerce schema we introduced last lesson. Twenty minutes from now you'll have a working database with five tables, real-looking data, and the ability to run any query you read in this course.
Three options — pick whichever is least painful
You don't need a fancy setup. The course uses standard SQL that works on every database, so the choice is mostly about how much you want to install.
Step 1 of 5
Pick a tool
Online sandbox (zero install) or local SQLite/MySQL/PostgreSQL.
Option 1 — Online (no install, fastest start)
Open one of these in your browser, paste the schema and data below, and you're done:
- sqliteonline.com — runs SQL in the browser. Free, no account needed. Defaults to SQLite.
- db-fiddle.com — supports MySQL, PostgreSQL, and SQLite. Generates shareable links if you want to send a query to a colleague.
- sqlfiddle.com — another popular browser-based option.
Use this if you want to skip installation or you're on a restricted machine. The downside is your data isn't persistent — close the tab and the database is gone — so you'll re-run the setup script each session.
Option 2 — SQLite (simplest local install)
SQLite is a file-based database — there's no server to start, no port to open, no password to remember. The whole database is one file, and the SQLite tool reads and writes it.
- macOS: SQLite ships with the OS. Run
sqlite3 ecommerce.dbin your terminal — it creates the file if it doesn't exist. - Windows / Linux: download the precompiled binary from sqlite.org or install via your package manager (
apt install sqlite3,choco install sqlite). - VS Code: install the "SQLite Viewer" or "SQLite" extension to run queries directly inside your editor instead of in a terminal.
Once installed:
sqlite3 ecommerce.dbYou're now at the sqlite> prompt. Paste the schema and data below, and any query you write in this course will work.
Option 3 — MySQL or PostgreSQL (more like production)
If you want a setup closer to what you'll meet at work, run a real database server. Both have free, easy options:
- MySQL — download from mysql.com or run via Docker:
docker run -e MYSQL_ROOT_PASSWORD=test -p 3306:3306 -d mysql:8 - PostgreSQL — download from postgresql.org or run via Docker:
docker run -e POSTGRES_PASSWORD=test -p 5432:5432 -d postgres:16
Pair either with a free GUI tool — DBeaver (works with all databases), MySQL Workbench, or pgAdmin. The GUI gives you a query editor, a results grid, and an explorer for browsing tables — much friendlier than a bare terminal.
Creating the schema
Whichever option you picked, run these five CREATE TABLE statements once. They define the e-commerce schema we'll use throughout the course.
CREATE TABLE categories (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(100) NOT NULL
);
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
role VARCHAR(20) DEFAULT 'tester',
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE products (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(200) NOT NULL,
price DECIMAL(10,2) NOT NULL,
category_id INTEGER REFERENCES categories(id),
in_stock BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER REFERENCES users(id),
total DECIMAL(10,2) NOT NULL,
status VARCHAR(20) DEFAULT 'pending',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE order_items (
id INTEGER PRIMARY KEY AUTOINCREMENT,
order_id INTEGER REFERENCES orders(id),
product_id INTEGER REFERENCES products(id),
quantity INTEGER NOT NULL,
price DECIMAL(10,2) NOT NULL
);A small dialect note: MySQL uses AUTO_INCREMENT (no underscore inside, all caps), PostgreSQL uses SERIAL or GENERATED ALWAYS AS IDENTITY, SQLite uses AUTOINCREMENT as written above. If you're on MySQL or PostgreSQL and the script complains, swap the keyword — the rest of the schema is portable.
Loading sample data
Now insert enough data to make queries interesting — five users, three categories, ten products, five orders, and fifteen line items:
INSERT INTO categories (name) VALUES
('Electronics'),
('Books'),
('Home');
INSERT INTO users (name, email, role, is_active) VALUES
('Alice Khan', 'alice@example.com', 'admin', TRUE),
('Bob Patel', 'bob@example.com', 'tester', TRUE),
('Carol Singh', 'carol@example.com', 'tester', FALSE),
('Dan Müller', 'dan@example.com', 'viewer', TRUE),
('Esha Roy', 'esha+qa@test.com', 'tester', TRUE);
INSERT INTO products (name, price, category_id, in_stock) VALUES
('Wireless Mouse', 25.00, 1, TRUE),
('Mechanical Keyboard Pro', 120.00, 1, TRUE),
('USB-C Hub', 45.00, 1, FALSE),
('27-inch Monitor', 320.00, 1, TRUE),
('SQL for Testers (book)', 18.50, 2, TRUE),
('Clean Code (book)', 30.00, 2, TRUE),
('Desk Lamp', 42.00, 3, TRUE),
('Standing Desk Mat', 55.00, 3, TRUE),
('Cable Organiser', 8.00, 3, TRUE),
('Webcam HD', 75.00, 1, FALSE);
INSERT INTO orders (user_id, total, status) VALUES
(1, 145.00, 'completed'),
(2, 48.50, 'completed'),
(2, 320.00, 'shipped'),
(4, 63.00, 'pending'),
(5, 25.00, 'cancelled');
INSERT INTO order_items (order_id, product_id, quantity, price) VALUES
(1, 1, 1, 25.00),
(1, 2, 1, 120.00),
(2, 5, 1, 18.50),
(2, 7, 1, 42.00), -- recalculate: this row + above = 60.50, but order total is 48.50
(3, 4, 1, 320.00),
(4, 6, 1, 30.00),
(4, 9, 1, 8.00),
(4, 7, 1, 42.00), -- 30+8+42 = 80, order says 63 — deliberate inconsistency for Chapter 5
(5, 1, 1, 25.00),
(1, 3, 1, 45.00), -- this product is out_of_stock — interesting!
(3, 8, 2, 55.00),
(4, 5, 1, 18.50),
(5, 9, 3, 8.00),
(2, 6, 1, 30.00),
(3, 1, 1, 25.00);Notice the deliberate inconsistencies — order totals that don't match the sum of their items, an out-of-stock product appearing in an order, an inactive user. These aren't typos; they're realistic data integrity issues you'll learn to detect in Chapter 5. Good test data has bugs in it.
Verify the setup
One last sanity check. Run these three queries:
SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM products;
SELECT COUNT(*) FROM orders;Expected output:
| COUNT(*) |
|----------|
| 5 |
| COUNT(*) |
|----------|
| 10 |
| COUNT(*) |
|----------|
| 5 |
If you got 5, 10, and 5, you're ready. If you got an error or different numbers, re-run the failing INSERTs — usually a typo in a column name, or running the script twice (which fails because the tables already exist).
⚠️ Common Mistakes
- Running CREATE TABLE twice. The second run errors out because the table already exists. If you need to start over, run
DROP TABLE order_items, orders, products, users, categories;first — and yes, the order matters (drop children before parents to satisfy foreign keys). - Mixing dialects in the same script.
AUTOINCREMENT(SQLite) andAUTO_INCREMENT(MySQL) are spelled differently. If you switch tools later, expect to tweak the schema slightly. - Practising on a shared/staging database that someone else is using. Always use a dedicated local database for learning. The whole point of a sandbox is that you can drop tables, insert garbage, and reset without affecting anyone.
🎯 Practice Task
20 minutes — get the database running and confirm you can query it.
- Pick one option (online, SQLite, or MySQL/PostgreSQL) and complete the install.
- Run the five CREATE TABLE statements. Run the five INSERT blocks.
- Run the three verification COUNT queries above. Confirm
5,10,5. - Run one more query of your own — anything. Try
SELECT name, email FROM users;and look at the output. - Stretch: add one more user, one more product, and one more order with INSERT. Re-run the COUNTs to confirm the numbers went up. (We'll cover INSERT formally in Chapter 4 — this is a preview.)
You now have a working SQL playground. From here on, every example in the course is a query you can paste and run. Chapter 2 starts with the most important command in SQL: SELECT.