Meet the practice database
Six small tables — departments, employees, customers, orders, products and order_items — that every example and exercise in this course uses.
Why it matters
Learning SQL on a different made-up table in every lesson means learning new data each time. With one small database you get to know the rows by heart, so you can check any result by eye. That habit is what catches wrong queries, at work and in interviews.
The idea
There are two small worlds. The company: departments, and employees who belong to a department and report to a manager. The shop: customers place orders, each order contains products, and order_items records which products are in which order, and how many.
A few awkward cases are planted on purpose, because real data always has them. There is an employee with no department, a customer with no orders, two people on the same salary, and an order with no amount.
A training kitchen with a fixed pantry. Every recipe (query) uses the same ingredients, so you learn how each one behaves, and you notice straight away when a dish comes out wrong.
Real databases have hundreds of tables and millions of rows, so you can't check results by eye there. That is exactly why it pays to practise on data small enough that you can, and to build the habit of testing a query on a few known rows.
How it works
- departments (4 rows): Engineering, Sales, Marketing and HR. HR has no employees yet.
- employees (8 rows): each has a dept_id that points at departments, and a manager_id that points at another employee. Asha runs the company, so her manager_id is NULL. Gus has no department.
- customers (6 rows): Tom signed up twice with the same email. Omar has never ordered. Zoe didn't give a city.
- orders (10 rows), from January to March 2024: each has a customer_id. Order 106 was cancelled and has no amount.
- products (7 rows): nobody has bought the phone case.
- order_items (14 rows): one row per product in an order, with the quantity. An order's amount is the sum of quantity × price over its items.
Key points
- employees.dept_id points at departments.dept_id; employees.manager_id points at employees.emp_id — the same table.
- orders.customer_id points at customers.customer_id: it says which customer placed each order.
- order_items links orders and products: one row per product in an order.
- Dates are stored as text in YYYY-MM-DD form, so sorting them as text also sorts them by date.
- Amounts and prices are whole pounds, to keep the sums easy to check by hand.
Common mistakes
- Assuming every employee has a department, or every customer has an order. NULLs and missing rows are where most wrong answers come from.
- Assuming ids are in date order. Here they happen to be; in real data, always sort by the date column.
- Writing a query before knowing the grain of each table — what one row means.
Not true: In the practice database, every employee belongs to a department and every customer has an order.
In fact: Some don't: an employee can have no department, and a customer no orders. Those NULLs and missing rows are where most wrong answers come from.
In the interview
Before writing any SQL in an interview, I look at the tables: what one row means in each, which columns are keys, and how the tables join. Then I ask about the awkward cases: can this be NULL, can there be duplicates, can a customer have no orders? Those decide whether I need a LEFT JOIN, a DISTINCT or a COALESCE. Two minutes of that saves a wrong answer.
Part of Unit 0: Start here: databases, tables and SQL in the free SQL course.