The Practice Database

Every example in this course runs against one small database called shop. It belongs to an imaginary online store that sells computer accessories, audio gear, storage and books to customers across India. It also stores the store's own staff.

Spend ten minutes on this page now. When you know the tables and a few of their rows by heart, the results of later queries will make sense at a glance, and you will spot mistakes in your own queries much faster.

The five tables

You can download the file that creates all of this from shop.sql and load it into your own MySQL server, as shown in Install MySQL & Run Queries.

customers

Primary key means the value is unique in every row and never empty. Required (NOT NULL in SQL) means the column must always have a value. VARCHAR(40) is text of up to 40 characters. NULL means "no value", and you will learn to handle it in NULL Values.

products

Here is the whole table. Run it and keep it open while you read the next lessons.

SELECT * FROM products;

product_id | product_name                | category    | price | stock
-----------+-----------------------------+-------------+-------+------
1          | Wireless Mouse              | Accessories | 799   | 120
2          | Mechanical Keyboard         | Accessories | 3499  | 45
3          | 27-inch Monitor             | Displays    | 15999 | 18
4          | USB-C Hub                   | Accessories | 1899  | 60
5          | Laptop Stand                | Accessories | 1299  | 75
6          | Noise-Cancelling Headphones | Audio       | 8999  | 25
7          | Bluetooth Speaker           | Audio       | 2499  | 40
8          | HD Webcam                   | Accessories | 2199  | 0
9          | 1TB External SSD            | Storage     | 6499  | 30
10         | 64GB Pen Drive              | Storage     | 549   | 200
11         | Python Basics (Book)        | Books       | 450   | 80
12         | Graphics Tablet             | Accessories | 4599  | 12
(12 rows)

The * means "every column". You will learn more about it in SELECT.

orders

Customer 1, Aarav from Rohtak, is the store's most frequent buyer:

SELECT * FROM orders WHERE customer_id = 1;

order_id | customer_id | order_date | status
---------+-------------+------------+----------
101      | 1           | 2025-01-05 | delivered
104      | 1           | 2025-02-02 | delivered
115      | 1           | 2025-05-20 | pending
(3 rows)

order_items

An order can contain several products, so each product in an order gets its own row here. Order 101 has two lines: two wireless mice and one pen drive.

SELECT * FROM order_items WHERE order_id = 101;

item_id | order_id | product_id | quantity | unit_price
--------+----------+------------+----------+-----------
1       | 101      | 1          | 2        | 799
2       | 101      | 10         | 1        | 549
(2 rows)

Why store unit_price when products already has a price? Because prices change. If the mouse costs more next year, old orders must still show what the customer actually paid.

employees

How the tables link

The tables are connected by matching ID values. A column that points to the primary key of another table is called a foreign key.

• orders.customer_id points to customers.customer_id. One customer can have many orders.
• order_items.order_id points to orders.order_id. One order can have many items.
• order_items.product_id points to products.product_id. One product can appear in many orders.
• employees.manager_id points to employees.employee_id in the same table. This builds the reporting chain: Rajesh (1) manages Neha (2), Simran (6) and Deepak (9).

So order_items is the bridge between orders and products. To answer "which products did Priya buy?", you go from customers to orders to order_items to products. You will do exactly that in INNER JOIN. The employees table is not linked to orders; it is used for the self-join and salary examples.

A few rows are special, and later lessons rely on them:

The browser runs SQLite, so numbers look slightly different

The Try-it editor runs SQLite, not MySQL. The queries are the same, but SQLite prints some numbers differently. Prices are stored in whole rupees, so the browser shows 799 where MySQL shows 799.00. Averages show more decimal places too:

SELECT AVG(price) FROM products;

AVG(price)
-----------------
4107.416666666667
(1 row)

MySQL gives 4107.416667 for the same query. The value is the same; only the number of decimals shown is different. Everywhere else in the course, the results are identical.

Key points

• The shop database has five tables: customers, products, orders, order_items and employees.
• Each table has an integer primary key that is unique in every row.
• Foreign keys link the tables: customers to orders, orders to order items, order items to products.
• employees.manager_id points back into the same table to show who reports to whom.
• Customers 4 and 9 have no email, customer 12 has no orders, product 12 has never been ordered.
• The browser shows 799 where MySQL shows 799.00, and averages show more decimals.

SELECT * FROM order_items WHERE order_id = 110;