All lessons

Module 1: Start here · Lesson 1.2 · about 25 min

Meet Northbay: the practice database

A guided tour of Northbay Retail, the made-up store chain you query in every lesson: ten tables, two years of orders, and a few planted problems to find.

Key points, meanings and formulas on one page: choose "Save as PDF" in the print box.

Module 1 · about 25 minutes · read first: 1.1. You’ll be able to name every Northbay table, say what one row in each means, and find your way around with SELECT * and LIMIT.

Before you query a database, learn its map: which tables exist, what one row in each stands for, and which columns point at other tables.

In everyday terms: starting a new job in a big supermarket. On day one you walk the aisles: where the stockroom is, which shelf holds what, and that the till receipts are kept in the office. Only then can you answer “how many kettles did we sell?”

1. Why this matters

On your first week in any analyst job, before you write a clever query, you have to learn the company’s tables: what they’re called, what one row means, and where the traps are. This lesson does that for Northbay, the store chain behind every exercise in this course. The habits you practise here (peek at each table, count its rows, look for blanks) are exactly what you’ll do with real data.

2. The map

Northbay Retail is made up: eleven stores across Canada plus a website, selling groceries, home goods, toys and electronics, with two years of sales (2024 and 2025). The data is generated in your browser from a fixed seed, so you see exactly the same rows as everyone else, and the answers in the lessons always match.

regions 4 rows

Sales regions.

  • region_id key integer
  • region_name text

stores 12 rows

Every store, plus one row for the online shop (no city).

  • store_id key integer
  • store_name text
  • city text
  • province text
  • region_id → regions integer
  • opened_on text
  • size_sqft integer

categories 10 rows

Product categories and their department.

  • category_id key integer
  • category_name text
  • department text

products 61 rows

What Northbay sells, with today's shelf price and unit cost.

  • product_id key integer
  • product_name text
  • category_id → categories integer
  • brand text
  • unit_price real
  • unit_cost real
  • launched_on text

customers 603 rows

Loyalty members. Some emails and tiers are missing, a few cities have stray spaces, and three people signed up twice.

  • customer_id key integer
  • first_name text
  • last_name text
  • email text
  • city text
  • province text
  • joined_on text
  • loyalty_tier text

employees 38 rows

Staff, each with a manager (for self-joins and org charts).

  • employee_id key integer
  • name text
  • store_id → stores integer
  • role text
  • manager_id → employees integer
  • hired_on text
  • salary integer

promotions 7 rows

Sale periods.

  • promo_id key integer
  • promo_name text
  • start_date text
  • end_date text
  • discount real

orders 4,064 rows

One row per checkout, 2024-2025. customer_id is NULL for guests.

  • order_id key integer
  • customer_id → customers integer
  • store_id → stores integer
  • order_date text
  • channel text
  • status text
  • promo_id → promotions integer

order_items 7,310 rows

One row per product in an order. Line value = quantity × unit_price × (1 − discount).

  • order_id → orders integer
  • product_id → products integer
  • quantity integer
  • unit_price real
  • discount real

returns 204 rows

Products brought back.

  • return_id key integer
  • order_id → orders integer
  • product_id → products integer
  • return_date text
  • reason text
Northbay's tables. key marks the column that names each row once; → marks a column that points at another table's key (a foreign key). Plus two tiny tables for pictures: mini_customers (5 rows) and mini_orders (6 rows).

Two words you’ll meet on the map. A key is a column that names each row uniquely, like store_id. A column marked → points at a row in another table: orders.store_id holds a store number from stores. Lesson 4.1 covers keys properly; for now, read → as “look this up in”.

3. How to peek at a table

Two habits make any new table less scary:

  • SELECT * FROM t LIMIT 5 shows the first five rows, so you see the columns and what the values look like.
  • SELECT COUNT(*) FROM t tells you how many rows there are.

Every stop on the tour below is one of these. Press Run on each; change the numbers and table names as much as you like. If you break something, the Reset database button puts it back.

Table
One kind of thing Northbay records: stores, products, customers, orders.
Grain
The sentence "one row = one ..." for a table. orders: one checkout. order_items: one product line in a checkout.
Key
A column whose value names one row, such as store_id in stores.
→ (points at)
A column holding another table's key, such as orders.store_id. Read it as "look this up in stores".
NULL
An empty cell: no value at all.
LIMIT 5
Show only the first 5 rows, for a quick look.
Physical meaning
A sale is not stored in one place. The checkout lives in orders, each product on it in order_items, and the shop and customer are kept once in stores and customers, linked by id numbers.

“One sale is one row, so orders and order_items have the same number of rows.” One checkout is one row in orders, but it has one row in order_items for every different product on the receipt. Northbay has 4,064 orders and 7,310 order lines. Always ask what one row means before you count anything.

Watch one receipt land in the tables, one step at a time:

The receipt. Order 1010: 1 laundry detergent, 4 packs of bagels, 1 art kit.
orders gets one row.
order_idcustomer_idstore_id
101018
order_items gets one row per product.
order_idproduct_idquantity
1010471
1010114
1010551
The ids point elsewhere. store_id 8 → Northbay Prairie in stores; customer_id 1 → Hiro Khan in customers; product 11 → Bagels 6 pack in products.

Count rows at the right grain:

4. The tour: places

regions (4 rows): Ontario South, Ontario North, Prairies and West Coast.

-- try
SELECT * FROM regions

stores (12 rows): one row per store, with its city, province, region, opening date and size. Look at the last row: store 12 is Northbay Online, the website. It has no city, province, region or size, so those cells are blank. A blank cell in a database is called NULL (Lesson 2.4).

-- try
SELECT store_id, store_name, city, province, region_id
FROM stores

5. The tour: what Northbay sells

categories (10 rows): each category belongs to one department: Grocery, Home, Kids or Tech.

-- try
SELECT * FROM categories

products (61 rows): unit_price is today’s shelf price and unit_cost is what Northbay pays the supplier, so the difference is the profit on one unit. Here are the Electronics products (category 10):

-- try
SELECT product_name, brand, unit_price, unit_cost
FROM products
WHERE category_id = 10

promotions (7 rows): sale periods with a fraction off, so 0.25 means 25% off.

-- try
SELECT * FROM promotions

6. The tour: people

customers (603 rows): loyalty members. This table is deliberately a little messy, like real data: some emails and loyalty tiers are NULL, a few cities have a stray space at the end, and three people signed up twice. Lessons 2.4, 3.4 and 7.4 hunt these down.

-- try
SELECT * FROM customers LIMIT 5

Look at customer 3: the city looks like “Winnipeg”, but it is really “Winnipeg ” with a trailing space. You can’t see it, which is exactly why it causes trouble later.

employees (38 rows): staff, each with a manager_id pointing at another employee. The CEO’s manager is NULL. Head office staff have no store_id.

-- try
SELECT employee_id, name, store_id, role, manager_id
FROM employees
LIMIT 8

7. The tour: sales

orders (4,064 rows): one row per checkout from 1 January 2024 to 31 December 2025. channel is store or online; status is completed or cancelled; promo_id says which sale was running that day, if any. A NULL customer_id means a guest who bought without a loyalty account (482 orders).

-- try
SELECT * FROM orders LIMIT 5

order_items (7,310 rows): one row per product in an order. An order of bagels and detergent has two rows here. The money is worked out per line as quantity × unit_price × (1 − discount). Note that unit_price here is the price paid on the day, which can differ from today’s shelf price in products.

-- try
SELECT * FROM order_items WHERE order_id = 1010

returns (204 rows): products brought back, with a date and a reason.

-- try
SELECT * FROM returns LIMIT 5

8. The tiny tables

mini_customers (5 rows) and mini_orders (6 rows) are so small you can see every row at once. Lessons use them whenever you should watch a query run step by step. Order 106 belongs to customer 9, who doesn’t exist, on purpose.

-- try
SELECT * FROM mini_orders
Maths and principle

Principle: every table has a grain: the one sentence that says what a single row stands for. “One row per checkout” (orders) and “one row per product in a checkout” (order_items) are different grains, even though both are about sales.

Equation: the number of rows is the cardinality of the table, written |orders| = 4,064. Because one order has one or more lines, |order_items| ≥ |orders|: here 7,310 ≥ 4,064, about 1.8 lines per order.

Analogy: a shop receipt. The receipt is one row in orders; each printed line on it is one row in order_items. It breaks down a little for returns, which point at a single line of a receipt rather than the whole receipt.

Try it: a customer buys 3 kettles in one checkout. How many rows in orders, and how many in order_items? (One and one: the 3 goes in quantity.)

9. Worked example

Question: order 1010 was placed on 3 January 2024. Who bought it, where, and what was in it?

  1. Find the order: SELECT * FROM orders WHERE order_id = 1010 gives customer 1, store 8, channel store, status completed.
  2. Store 8 in stores is Northbay Prairie in Winnipeg.
  3. Customer 1 in customers is Hiro Khan.
  4. order_items for 1010 has 3 lines: products 47, 11 and 55 (Laundry detergent, Bagels 6 pack, Art kit).

You looked up four tables to answer one question. In Module 4 a join will do those look-ups in a single query. For now, try steps 2 and 3 yourself:

-- try
SELECT store_name, city FROM stores WHERE store_id = 8

10. Practice

-- exercise: How many products does Northbay sell? Return one number.
-- hint: COUNT(*) counts the rows of a table.
SELECT * FROM products LIMIT 5
-- answer:
SELECT COUNT(*) FROM products
-- exercise: Show the first 3 rows of the returns table.
-- hint: SELECT * ... LIMIT 3
SELECT * FROM returns
-- answer:
SELECT * FROM returns LIMIT 3
-- exercise: Find the name and city of customer number 3.
-- hint: WHERE customer_id = 3
SELECT first_name, last_name, city
FROM customers
-- answer:
SELECT first_name, last_name, city
FROM customers
WHERE customer_id = 3
  • Northbay has places (regions, stores), things it sells (categories, products, promotions), people (customers, employees) and sales (orders, order_items, returns).
  • Learn every table’s grain: orders is one row per checkout, order_items one row per product in a checkout.
  • A column marked → holds another table’s key; you look it up there.
  • Peek with SELECT * … LIMIT 5 and count with SELECT COUNT(*).
  • NULL means an empty cell: guests have no customer_id; the online store has no city.

New words: key, NULL, grain, cardinality, department, guest order.

Check yourself

1Which table has one row per product inside an order?
2What does a NULL customer_id in orders mean?
3Which store has no city?

Sources

Your ticks stay in this browser. Save them as a file on the course home.

Updated 2026-11-22. Northbay Retail is a made-up company; its data is generated in your browser. Queries run on your device and nothing you type is sent anywhere.