Meet the practice database

Step 1 Understand it

Why you need it. 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.

Watch it happen

Picture it

erDiagram
  DEPARTMENTS ||--o{ EMPLOYEES : "has"
  EMPLOYEES |o--o{ EMPLOYEES : "manages"
  CUSTOMERS ||--o{ ORDERS : "places"
  ORDERS ||--o{ ORDER_ITEMS : "contains"
  PRODUCTS ||--o{ ORDER_ITEMS : "is in"
  DEPARTMENTS {
    int dept_id PK
    text dept_name
    text location
  }
  EMPLOYEES {
    int emp_id PK
    text name
    int dept_id FK
    int manager_id FK
    int salary
    text hire_date
  }
  CUSTOMERS {
    int customer_id PK
    text name
    text email
    text city
    text signup_date
  }
  ORDERS {
    int order_id PK
    int customer_id FK
    text order_date
    int amount
    text status
  }
  PRODUCTS {
    int product_id PK
    text product_name
    text category
    int price
  }
  ORDER_ITEMS {
    int order_id PK, FK
    int product_id PK, FK
    int quantity
  }

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: an employee with no department, a customer with no orders, two people on the same salary, and an order with no amount.

How it works, step by step

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.

Analogy

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.

Where the analogy breaks

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.

Worked examples

SELECT *
FROM departments
ORDER BY dept_id;

SELECT *
FROM employees
ORDER BY emp_id;

SELECT *
FROM orders
ORDER BY order_id;

Must-know 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.
• 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.

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Watch

  CS50's Introduction to Databases with SQL — Lecture 0: Querying

  Harvard, free

• Practice

  SQLBolt — Lesson 1: SELECT queries 101

  interactive exercises in the browser, free

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Without looking, say what one row of order_items means, and how it connects orders to products.

   Show answer

   One row of order_items is one line on a receipt: "order X contains this many of product Y". For example, (102, 4, 2) means order 102 contains 2 of product 4, the desk lamp.

   order_id points to a row in orders (who bought it, when, the total). product_id points to a row in products (its name and price). So order_items is the link between the two: one order can hold many products, and one product can appear in many orders.

2. Writefrom a blank page, then run it

   Write a query that shows every column of the customers table, sorted by customer_id.

   Show answer

   SELECT * means every column. ORDER BY customer_id puts them in id order, so you can see the two Tom rows (2 and 6) and Zoe's missing city.

   sqlCopy

   SELECT *
   FROM customers
   ORDER BY customer_id;

   Try it

   6 rows

   customer_id	name	email	city	signup_date
   1	Priya	priya@mail.com	London	2024-01-02
   2	Tom	tom@mail.com	Leeds	2024-01-20
   3	Lena	lena@mail.com	London	2024-01-30
   4	Omar	omar@mail.com	Bristol	2024-02-14
   5	Zoe	zoe@mail.com	NULL	2024-03-10
   6	Tom	tom@mail.com	Leeds	2024-03-20

3. Predictsay it before you run it

   How many rows does SELECT * FROM order_items WHERE order_id = 102; return, and which products are they?

   Show answer

   2 rows: product 4 (the desk lamp, 2 of them) and product 5 (the backpack, 1).

   Check it against orders: 2 × £25 + 1 × £30 = £80, which is exactly order 102's amount. (ORDER BY product_id is added below so the order is fixed.)

   sqlCopy

   SELECT *
   FROM order_items
   WHERE order_id = 102
   ORDER BY product_id;

   Try it

   2 rows

   order_id	product_id	quantity
   102	4	2
   102	5	1

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   List the planted cases from memory: who has no manager, who has no department, who has no orders, which product never sold, and which order has no amount.

   Show answer

   • No manager: Asha (manager_id is NULL; she is the boss).
   • No department: Gus (dept_id is NULL).
   • No orders: Omar. (The second Tom sign-up, customer 6, has none either; Tom's orders are all under id 2.)
   • Never sold: the phone case (product 7).
   • No amount: order 106, which was cancelled.

   The others worth knowing: HR has no employees, Ben and Chen earn the same, Hana earns more than her manager Ben, Tom signed up twice with the same email, Zoe has no city, and Priya ordered three days in a row.

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

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 — because those decide whether I need a LEFT JOIN, a DISTINCT or a COALESCE. Two minutes of that saves a wrong answer.

Follow-up questions

Tick each check when you can do it without notes