MySQL Practice Database: Sample Tables

From this chapter on, many lessons query the same small tables. Learning is easier when you already know the data. This lesson sets them up, so you can copy the script and keep it.

The data is made up. No real people or companies are used.

Products

A small shop catalogue: eight products in three categories.

query.sql

SQL

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price DECIMAL(8,2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    added_on DATE NOT NULL
);

INSERT INTO products (name, category, price, stock, added_on) VALUES
    ('Notebook',      'stationery',  80.00, 120, '2026-01-10'),
    ('Gel Pen',       'stationery',  15.50, 300, '2026-01-12'),
    ('Backpack',      'bags',       999.00,  25, '2026-02-01'),
    ('Marker Set',    'stationery', 145.00,   0, '2026-02-15'),
    ('Desk Lamp',     'home',       650.00,  40, '2026-03-03'),
    ('Water Bottle',  'home',       220.00,  75, '2026-03-20'),
    ('Laptop Sleeve', 'bags',       450.00,  18, '2026-04-02'),
    ('Sticky Notes',  'stationery',  35.00, 210, '2026-04-18');

SELECT * FROM products;

Output

OUTPUT

+----+---------------+------------+--------+-------+------------+
| id | name          | category   | price  | stock | added_on   |
+----+---------------+------------+--------+-------+------------+
|  1 | Notebook      | stationery |  80.00 |   120 | 2026-01-10 |
|  2 | Gel Pen       | stationery |  15.50 |   300 | 2026-01-12 |
|  3 | Backpack      | bags       | 999.00 |    25 | 2026-02-01 |
|  4 | Marker Set    | stationery | 145.00 |     0 | 2026-02-15 |
|  5 | Desk Lamp     | home       | 650.00 |    40 | 2026-03-03 |
|  6 | Water Bottle  | home       | 220.00 |    75 | 2026-03-20 |
|  7 | Laptop Sleeve | bags       | 450.00 |    18 | 2026-04-02 |
|  8 | Sticky Notes  | stationery |  35.00 |   210 | 2026-04-18 |
+----+---------------+------------+--------+-------+------------+
8 rows in set

Columns: id, name, category, price, stock (units in stock) and added_on (the date the product was added). One product, the Marker Set, has stock = 0.

Employees

A tiny company. The manager_id column points to another row of the same table (a self reference). The manager at the top has NULL.

query.sql

SQL

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(50) NOT NULL,
    salary DECIMAL(10,2) NOT NULL,
    manager_id INT NULL,
    hired_on DATE NOT NULL
);

INSERT INTO employees (name, department, salary, manager_id, hired_on) VALUES
    ('Asha Rao',     'management',  150000.00, NULL, '2020-01-15'),
    ('Ben Carter',   'engineering', 120000.00,    1, '2021-03-01'),
    ('Chitra Nair',  'engineering',  95000.00,    2, '2022-07-11'),
    ('Dev Malhotra', 'engineering',  88000.00,    2, '2023-01-09'),
    ('Esha Gupta',   'sales',        70000.00,    1, '2021-09-20'),
    ('Farid Khan',   'sales',        64000.00,    5, '2022-11-02'),
    ('Gita Menon',   'sales',        64000.00,    5, '2024-02-14'),
    ('Hari Iyer',    'support',      52000.00,    1, '2023-05-30');

SELECT id, name, department, salary, manager_id FROM employees;

Output

OUTPUT

+----+--------------+-------------+-----------+------------+
| id | name         | department  | salary    | manager_id |
+----+--------------+-------------+-----------+------------+
|  1 | Asha Rao     | management  | 150000.00 |       NULL |
|  2 | Ben Carter   | engineering | 120000.00 |          1 |
|  3 | Chitra Nair  | engineering |  95000.00 |          2 |
|  4 | Dev Malhotra | engineering |  88000.00 |          2 |
|  5 | Esha Gupta   | sales       |  70000.00 |          1 |
|  6 | Farid Khan   | sales       |  64000.00 |          5 |
|  7 | Gita Menon   | sales       |  64000.00 |          5 |
|  8 | Hari Iyer    | support     |  52000.00 |          1 |
+----+--------------+-------------+-----------+------------+
8 rows in set

Customers and orders

Two related tables: each order belongs to one customer, through customer_id.

query.sql

SQL

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    city VARCHAR(50) NOT NULL
);

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    ordered_on DATE NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) NOT NULL
);

INSERT INTO customers (name, city) VALUES
    ('Maya Shah',    'Pune'),
    ('Rohan Verma',  'Delhi'),
    ('Sana Ali',     'Pune'),
    ('Tara Nair',    'Kochi'),
    ('Uday Singh',   'Delhi');

INSERT INTO orders (customer_id, ordered_on, total, status) VALUES
    (1, '2026-05-02',  680.00, 'delivered'),
    (1, '2026-05-20', 1299.00, 'delivered'),
    (2, '2026-05-21',  145.00, 'shipped'),
    (3, '2026-06-01',  450.00, 'delivered'),
    (3, '2026-06-05',   80.00, 'cancelled'),
    (3, '2026-06-18',  999.00, 'shipped'),
    (2, '2026-06-20',  220.00, 'pending');

SELECT * FROM orders;

Output

OUTPUT

+----+-------------+------------+---------+-----------+
| id | customer_id | ordered_on | total   | status    |
+----+-------------+------------+---------+-----------+
|  1 |           1 | 2026-05-02 |  680.00 | delivered |
|  2 |           1 | 2026-05-20 | 1299.00 | delivered |
|  3 |           2 | 2026-05-21 |  145.00 | shipped   |
|  4 |           3 | 2026-06-01 |  450.00 | delivered |
|  5 |           3 | 2026-06-05 |   80.00 | cancelled |
|  6 |           3 | 2026-06-18 |  999.00 | shipped   |
|  7 |           2 | 2026-06-20 |  220.00 | pending   |
+----+-------------+------------+---------+-----------+
7 rows in set

Note that customer 4 (Tara Nair) and customer 5 (Uday Singh) have no orders. That is deliberate. It helps to show the difference between joins later.

How the lessons use these tables

Every lesson that needs a table includes its setup script at the top, so you can start from any lesson.
Each lesson starts from a clean learn database. If you run lessons one after another in the same database, drop the tables first (DROP TABLE IF EXISTS products;) or use DROP DATABASE learn; CREATE DATABASE learn; USE learn;.
The results you see in the lessons are the real output of a real MySQL server. If your result differs, check your MySQL version and your sql_mode.

A tip before you start

Open a second query tab with this setup script, and keep the tables around while you read. Change a value, add a row, delete a row, and run the query again to see what changes. That is the fastest way to learn SQL.

info

Reset your practice database

DROP DATABASE IF EXISTS learn; CREATE DATABASE learn; USE learn; gives you a clean start. Never run this on a database that holds anything you want to keep.