One database for the whole course
Learning SQL from different tiny examples each time is confusing. Instead, every lesson here uses the same small online-shop database, called shop. You will meet the same customers and products again and again, so you can focus on the new idea instead of on new data.
The setup is one script. It is long because it creates seven tables and fills them, but it is plain SQL that you will understand fully by the end of the course. For now just run it.
Create the database
Copy the whole script, paste it into the mysql client (or save it as shop.sql and run source shop.sql). It creates the database, the tables and the rows.
-- Sample "shop" database for the MySQL course.
DROP DATABASE IF EXISTS shop;
CREATE DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE shop;
CREATE TABLE categories (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL
);
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
category_id INT NOT NULL,
price DECIMAL(10,2) NOT NULL,
stock INT NOT NULL,
FOREIGN KEY (category_id) REFERENCES categories (id)
);
CREATE TABLE customers (
id INT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
phone VARCHAR(30),
city VARCHAR(50) NOT NULL,
country VARCHAR(50) NOT NULL,
signup_date DATE NOT NULL
);
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
status ENUM('pending','shipped','delivered','cancelled') NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers (id)
);
CREATE TABLE order_items (
id INT PRIMARY KEY AUTO_INCREMENT,
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
unit_price DECIMAL(10,2) NOT NULL,
FOREIGN KEY (order_id) REFERENCES orders (id),
FOREIGN KEY (product_id) REFERENCES products (id)
);
CREATE TABLE departments (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
location VARCHAR(50) NOT NULL
);
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(80) NOT NULL,
job_title VARCHAR(60) NOT NULL,
manager_id INT,
department_id INT,
salary DECIMAL(10,2) NOT NULL,
hire_date DATE NOT NULL
);
INSERT INTO categories (id, name) VALUES
(1, 'Electronics'), (2, 'Books'), (3, 'Home'), (4, 'Sports'), (5, 'Toys');
INSERT INTO products (id, name, category_id, price, stock) VALUES
(1, 'Wireless Mouse', 1, 24.99, 150),
(2, 'Mechanical Keyboard', 1, 89.50, 60),
(3, 'USB-C Hub', 1, 39.00, 80),
(4, 'Noise-Cancelling Headphones', 1, 199.99, 25),
(5, 'SQL Basics Handbook', 2, 29.95, 200),
(6, 'Web Design Patterns', 2, 34.50, 120),
(7, 'Data Structures Illustrated', 2, 45.00, 90),
(8, 'Desk Lamp', 3, 32.00, 70),
(9, 'Ceramic Mug Set', 3, 18.75, 140),
(10, 'Standing Desk Mat', 3, 55.00, 40),
(11, 'Yoga Mat', 4, 27.50, 110),
(12, 'Resistance Bands', 4, 14.99, 300),
(13, 'Water Bottle', 4, 12.00, 250),
(14, 'Building Blocks Set', 5, 49.99, 35),
(15, 'Puzzle 1000 Pieces', 5, 19.99, 0);
INSERT INTO customers (id, first_name, last_name, email, phone, city, country, signup_date) VALUES
(1, 'Asha', 'Verma', 'asha.verma@example.com', '+91-98100-00001', 'Delhi', 'India', '2023-01-15'),
(2, 'Ben', 'Carter', 'ben.carter@example.com', NULL, 'London', 'UK', '2023-02-03'),
(3, 'Chloe', 'Dubois', 'chloe.dubois@example.com', '+33-1-5555-0003', 'Paris', 'France', '2023-02-20'),
(4, 'Daniel', 'Kim', 'daniel.kim@example.com', '+82-2-5555-0004', 'Seoul', 'South Korea', '2023-03-11'),
(5, 'Elena', 'Rossi', 'elena.rossi@example.com', NULL, 'Rome', 'Italy', '2023-04-02'),
(6, 'Farid', 'Khan', 'farid.khan@example.com', '+91-98100-00006', 'Mumbai', 'India', '2023-04-18'),
(7, 'Grace', 'Okafor', 'grace.okafor@example.com', '+234-1-555-0007', 'Lagos', 'Nigeria', '2023-05-09'),
(8, 'Hiro', 'Tanaka', 'hiro.tanaka@example.com', '+81-3-5555-0008', 'Tokyo', 'Japan', '2023-06-01'),
(9, 'Isabel', 'Santos', 'isabel.santos@example.com', NULL, 'Lisbon', 'Portugal', '2023-06-25'),
(10, 'Jack', 'Miller', 'jack.miller@example.com', '+1-212-555-0010', 'New York', 'USA', '2023-07-14'),
(11, 'Kavya', 'Nair', 'kavya.nair@example.com', '+91-98100-00011', 'Bengaluru', 'India', '2023-08-30'),
(12, 'Liam', 'O''Brien', 'liam.obrien@example.com', NULL, 'Dublin', 'Ireland', '2023-09-12');
INSERT INTO orders (id, customer_id, order_date, status) VALUES
(1, 1, '2024-01-05', 'delivered'),
(2, 1, '2024-02-14', 'delivered'),
(3, 2, '2024-01-20', 'delivered'),
(4, 3, '2024-02-02', 'shipped'),
(5, 4, '2024-02-11', 'delivered'),
(6, 5, '2024-03-01', 'cancelled'),
(7, 6, '2024-03-09', 'delivered'),
(8, 6, '2024-03-22', 'shipped'),
(9, 7, '2024-03-30', 'pending'),
(10, 8, '2024-04-04', 'delivered'),
(11, 1, '2024-04-12', 'delivered'),
(12, 10, '2024-04-18', 'shipped'),
(13, 3, '2024-05-02', 'delivered'),
(14, 2, '2024-05-15', 'pending'),
(15, 11, '2024-05-21', 'delivered'),
(16, 4, '2024-06-03', 'delivered'),
(17, 8, '2024-06-10', 'cancelled'),
(18, 10, '2024-06-25', 'delivered'),
(19, 6, '2024-07-07', 'pending'),
(20, 1, '2024-07-19', 'shipped');
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
(1, 1, 1, 24.99), (1, 5, 2, 29.95),
(2, 2, 1, 89.50),
(3, 6, 1, 34.50), (3, 9, 2, 18.75),
(4, 4, 1, 199.99),
(5, 3, 2, 39.00), (5, 1, 1, 24.99),
(6, 11, 1, 27.50),
(7, 7, 1, 45.00), (7, 8, 1, 32.00),
(8, 12, 3, 14.99), (8, 13, 2, 12.00),
(9, 10, 1, 55.00),
(10, 2, 1, 89.50), (10, 3, 1, 39.00),
(11, 5, 1, 29.95), (11, 6, 1, 34.50), (11, 7, 1, 45.00),
(12, 4, 1, 199.99), (12, 1, 2, 24.99),
(13, 9, 4, 18.75),
(14, 8, 2, 32.00),
(15, 11, 2, 27.50), (15, 12, 2, 14.99),
(16, 5, 3, 29.95),
(17, 14, 1, 49.99),
(18, 2, 1, 89.50), (18, 13, 1, 12.00),
(19, 1, 1, 24.99), (19, 3, 1, 39.00), (19, 13, 3, 12.00),
(20, 4, 1, 199.99), (20, 9, 1, 18.75);
INSERT INTO departments (id, name, location) VALUES
(1, 'Engineering', 'Bengaluru'), (2, 'Sales', 'Delhi'), (3, 'HR', 'Delhi'), (4, 'Finance', 'Mumbai');
INSERT INTO employees (id, name, job_title, manager_id, department_id, salary, hire_date) VALUES
(1, 'Meera Iyer', 'Chief Executive', NULL, 1, 150000.00, '2018-03-01'),
(2, 'Tom Hughes', 'Head of Sales', 1, 2, 110000.00, '2019-06-15'),
(3, 'Nina Petrov', 'Engineering Manager', 1, 1, 125000.00, '2019-01-10'),
(4, 'Omar Haddad', 'Senior Developer', 3, 1, 98000.00, '2020-09-01'),
(5, 'Paula Gomez', 'Developer', 3, 1, 82000.00, '2021-04-12'),
(6, 'Quinn Baker', 'Sales Executive', 2, 2, 64000.00, '2021-08-23'),
(7, 'Rina Das', 'Sales Executive', 2, 2, 66000.00, '2022-02-14'),
(8, 'Sam Wright', 'Developer', 3, 1, 79000.00, '2023-05-02'),
(9, 'Tara Singh', 'HR Manager', 1, 3, 72000.00, '2020-11-30'),
(10, 'Uma Reddy', 'Junior Developer', 4, 1, 48000.00, '2024-01-08'),
(11, 'Victor Lang', 'Consultant', 1, NULL, 90000.00, '2022-10-10');The script is safe to run again. The first line, DROP DATABASE IF EXISTS shop;, deletes any earlier copy, so you can always start over if you break something. Because of that, never run it on a server that already has a real database called shop.
Check that it loaded
Count the rows in each main table. If the numbers match, your copy is identical to the one used in this course:
SELECT
(SELECT COUNT(*) FROM customers) AS customers,
(SELECT COUNT(*) FROM products) AS products,
(SELECT COUNT(*) FROM orders) AS orders,
(SELECT COUNT(*) FROM order_items) AS order_items,
(SELECT COUNT(*) FROM employees) AS employees;+-----------+----------+--------+-------------+-----------+
| customers | products | orders | order_items | employees |
+-----------+----------+--------+-------------+-----------+
| 12 | 15 | 20 | 34 | 11 |
+-----------+----------+--------+-------------+-----------+How the tables relate
Think of the shop in two halves.
Selling. A customer places many orders. Each order has several lines in order_items, and each line refers to one product. Products are grouped into categories.
categories 1 --- * products 1 --- * order_items * --- 1 orders * --- 1 customersRead 1 --- * as "one to many": one category has many products, one order has many order lines, one customer has many orders.
Staff. departments and employees are separate and are used in the later lessons on self joins, grouping and window functions. In employees, the column manager_id holds the id of another employee. That is how a hierarchy is stored in a flat table.
The data has gaps on purpose
Real data is untidy, so this data is too:
- Customer 9 (Isabel) and customer 12 (Liam) have never placed an order.
- Product 15 (the puzzle) was never ordered and has no stock.
- Some customers have no phone number. Those cells hold
NULL. - One employee (Victor) has no department, and the chief executive has no manager.
- Two orders are
cancelled, three arepending.
Each gap exists to teach something later: LEFT JOIN, IS NULL, HAVING, and the difference between "no rows" and "a row with an unknown value".
Peek at the data
Look at a few rows from each side of the shop. You do not need to understand every keyword yet; the next section explains them one at a time.
SELECT id, first_name, last_name, city, country FROM customers WHERE id <= 4;+----+------------+-----------+--------+-------------+
| id | first_name | last_name | city | country |
+----+------------+-----------+--------+-------------+
| 1 | Asha | Verma | Delhi | India |
| 2 | Ben | Carter | London | UK |
| 3 | Chloe | Dubois | Paris | France |
| 4 | Daniel | Kim | Seoul | South Korea |
+----+------------+-----------+--------+-------------+SELECT id, customer_id, order_date, status FROM orders WHERE id <= 4;+----+-------------+------------+-----------+
| id | customer_id | order_date | status |
+----+-------------+------------+-----------+
| 1 | 1 | 2024-01-05 | delivered |
| 2 | 1 | 2024-02-14 | delivered |
| 3 | 2 | 2024-01-20 | delivered |
| 4 | 3 | 2024-02-02 | shipped |
+----+-------------+------------+-----------+Notice that order 3 belongs to customer 2 (Ben Carter from London). The only thing connecting the two tables is the number 2. Following that kind of number from table to table is the heart of working with a relational database.
Common mistakes
- Running only part of the script. The tables need the data, and the data needs the tables in the right order. Run it from top to bottom in one go.
- Pasting into the wrong tool window. Make sure the text is sent to the MySQL connection, not to a notes file.
- Not selecting the database. The script ends up in
shopbecause it containsUSE shop;. In later lessons, start new sessions withUSE shop;before querying. - Editing the sample data and then comparing results. If your numbers differ from the lesson, re-run the script first.
Exercise
After running the script, run SHOW TABLES; and then count how many rows order_items has. Then find out which table you would look at to learn which department Victor Lang works in.
Show one possible answer
SELECT COUNT(*) AS lines_in_orders FROM order_items;+-----------------+
| lines_in_orders |
+-----------------+
| 34 |
+-----------------+The answer is 34. Victor's row is in employees, and his department_id is NULL, so he is not assigned to any department yet. You will see how to find rows like him with IS NULL shortly.
Key points
- Every example in this course uses the same shop database, so you can compare results with the output shown.
- The tables link by ids: orders point to customers, order_items point to orders and products, and products point to categories.
- The data deliberately includes gaps (a customer with no orders, a product never ordered, missing phone numbers) to make joins and NULL handling interesting.
- If you ever damage your copy, run the setup script again. It drops and recreates the database from scratch.