The practice database

Every example and exercise on this site uses one small database. It belongs to a made-up shop that sells groceries, stationery, electronics and things for the home. This page shows what is in it, so that you can check a table or column name while you write a query.

All the people, products, prices and orders are invented for teaching.

The five tables at a glance

How the tables are linked

The tables point to one another through id numbers. These links are what you write in the ON part of a join.

• orders.customer_id holds the id of a row in customers. Each order belongs to one customer. A customer can have many orders, or none.
• order_items.order_id holds the id of a row in orders. Each order has one or more lines.
• order_items.product_id holds the id of a row in products. A product can appear in many orders, or in none.
• employees.manager_id holds the id of another row in employees: the person's manager. It is empty for the head of the shop.

So the path from a customer to the products they bought runs through four tables: customers, then orders, then order_items, then products. The lesson on joining many tables follows that path.

Things planted in the data on purpose

Real data is untidy, and the lessons need examples of that. So the practice database has a few deliberate features.

• Three customers have no email address. The column holds NULL for them, which the lesson on NULL uses.
• Three customers have never placed an order, and one product has never been ordered. They are what a LEFT JOIN is for.
• Two products are out of stock, with a stock of 0.
• Two employees earn exactly the same salary, which matters for ranking and for "top N" questions.
• Orders have four statuses: delivered, shipped, pending and cancelled.
• Three cities have two customers each, and several customers share a state.

Notes on the columns

• Prices and salaries are whole rupees. Salaries are per month.
• Dates are stored as text in year-month-day form, such as 2026-03-15. In this form they sort and compare correctly. The lesson on dates explains how to work with them.
• The price of an order line is not stored. It is the quantity in order_items times the price in products. An order's total is the sum over its lines.
• Every table except order_items has an id column that is its primary key.

How to see this from SQL

You do not need this page to find out what a database contains. In SQLite, which the practice box runs, this lists the tables:

SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name;

And this describes the columns of one table:

PRAGMA table_info(customers);

In MySQL the same jobs are done by SHOW TABLES and DESCRIBE customers. In PostgreSQL and SQL Server, the standard information_schema views hold the lists.

The statements that create and fill the database are in one plain file, which the practice box loads: seed.sql. You can read it to see CREATE TABLE and INSERT used for real, or run it in SQLite on your own computer to get the same database there.

The tables in full

customers (12 rows)

The people who buy from the shop. One row per customer.

Columns: id integer, name text, city text, state text, joined text, email text

12 rows

Open customers in the practice box

products (16 rows)

What the shop sells. One row per product, with its price in rupees and the number in stock.

Columns: id integer, name text, category text, price integer, stock integer

16 rows

Showing the first 12 of 16 rows.

Open products in the practice box

orders (20 rows)

One row for each order placed. customer_id says who placed it.

Columns: id integer, customer_id integer, order_date text, status text

20 rows

Showing the first 12 of 20 rows.

Open orders in the practice box

order_items (39 rows)

The lines of each order: which product and how many. An order has one or more lines.

Columns: order_id integer, product_id integer, quantity integer

39 rows

Showing the first 12 of 39 rows.

Open order_items in the practice box

employees (8 rows)

The staff of the shop. manager_id points to another row of the same table.

Columns: id integer, name text, department text, salary integer, manager_id integer, hired text

8 rows

Open employees in the practice box

Examples on this page were run with SQLite 3.50.4 on 12 November 2026. The results shown are the real results.

This is an independent learning site. It is not connected with the makers of any database product. MySQL, PostgreSQL, SQL Server, Oracle and SQLite are named only so that you know where a piece of SQL works. The shop, its customers and its staff in the practice database are made up.