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.
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.
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_idkey integer -
region_nametext
stores 12 rows
Every store, plus one row for the online shop (no city).
-
store_idkey integer -
store_nametext -
citytext -
provincetext -
region_id→ regions integer -
opened_ontext -
size_sqftinteger
categories 10 rows
Product categories and their department.
-
category_idkey integer -
category_nametext -
departmenttext
products 61 rows
What Northbay sells, with today's shelf price and unit cost.
-
product_idkey integer -
product_nametext -
category_id→ categories integer -
brandtext -
unit_pricereal -
unit_costreal -
launched_ontext
customers 603 rows
Loyalty members. Some emails and tiers are missing, a few cities have stray spaces, and three people signed up twice.
-
customer_idkey integer -
first_nametext -
last_nametext -
emailtext -
citytext -
provincetext -
joined_ontext -
loyalty_tiertext
employees 38 rows
Staff, each with a manager (for self-joins and org charts).
-
employee_idkey integer -
nametext -
store_id→ stores integer -
roletext -
manager_id→ employees integer -
hired_ontext -
salaryinteger
promotions 7 rows
Sale periods.
-
promo_idkey integer -
promo_nametext -
start_datetext -
end_datetext -
discountreal
orders 4,064 rows
One row per checkout, 2024-2025. customer_id is NULL for guests.
-
order_idkey integer -
customer_id→ customers integer -
store_id→ stores integer -
order_datetext -
channeltext -
statustext -
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 -
quantityinteger -
unit_pricereal -
discountreal
returns 204 rows
Products brought back.
-
return_idkey integer -
order_id→ orders integer -
product_id→ products integer -
return_datetext -
reasontext
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 5shows the first five rows, so you see the columns and what the values look like.SELECT COUNT(*) FROM ttells 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:
| order_id | customer_id | store_id |
|---|---|---|
| 1010 | 1 | 8 |
| order_id | product_id | quantity |
|---|---|---|
| 1010 | 47 | 1 |
| 1010 | 11 | 4 |
| 1010 | 55 | 1 |
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?
- Find the order:
SELECT * FROM orders WHERE order_id = 1010gives customer 1, store 8, channel store, status completed. - Store 8 in
storesis Northbay Prairie in Winnipeg. - Customer 1 in
customersis Hiro Khan. order_itemsfor 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
Sources
- SQLite documentation, SELECT (LIMIT clause) https://www.sqlite.org/lang_select.html
- Kimball Group, Dimensional Modeling Techniques: Grain https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/grain/
- Kimball, R. and Ross, M. (2013), The Data Warehouse Toolkit, 3rd edition, Wiley, chapter 3 (Retail Sales)
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.