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

  1. departments (4 rows): Engineering, Sales, Marketing and HR. HR has no employees yet.
  2. 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.
  3. customers (6 rows): Tom signed up twice with the same email. Omar has never ordered. Zoe didn't give a city.
  4. orders (10 rows), from January to March 2024: each has a customer_id. Order 106 was cancelled and has no amount.
  5. products (7 rows): nobody has bought the phone case.
  6. 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

Common mistakes

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.