The sample shop database

Every lesson uses this web-shop database, with customers, a product catalogue, orders, and staff data. Its roughly 50,000 rows are enough to show how grouping and indexes behave with realistic data.

Use this page to check table and column names while working through the examples or writing queries of your own.

The information below is read from the sample file, so it matches the database you open in the app.

Column names in monospace are the ones you type. The notes column marks what the database knows about each one:

primary key — identifies the row. Never repeated, never empty.
required — the column cannot be left empty (NOT NULL). Anything without this mark can hold NULL, which is what the filtering lesson is about.
→ table.column — a foreign key: this column holds a value from that other table. Joins follow these.
unique — no two rows may share this value. An upsert detects a conflict on this.
indexed — the database keeps a sorted copy of this column, so filtering and sorting on it is fast. The last lesson explains what that means.

Catalogue

`categories` 33 rows

Column	Type	Notes
`id`	INTEGER	primary key
`name`	TEXT	required, unique
`description`	TEXT
`parent_id`	INTEGER	→ categories.id, indexed
`sort_order`	INTEGER	required, default 0
`is_active`	INTEGER	required, default 1

`products` 400 rows

Column	Type	Notes
`id`	INTEGER	primary key
`category_id`	INTEGER	→ categories.id, required, indexed
`name`	TEXT	required
`description`	TEXT
`sku`	TEXT	required, unique
`price`	REAL	required, indexed, default 0
`cost`	REAL	required, default 0
`stock_quantity`	INTEGER	required, default 0
`weight_kg`	REAL
`attributes`	TEXT
`is_active`	INTEGER	required, default 1
`created_at`	TEXT	required
`updated_at`	TEXT	required

`product_images` 1,024 rows

Column	Type	Notes
`id`	INTEGER	primary key
`product_id`	INTEGER	→ products.id, required, indexed
`url`	TEXT	required
`alt_text`	TEXT
`sort_order`	INTEGER	required, default 0
`is_primary`	INTEGER	required, default 0

`tags` 15 rows

Column	Type	Notes
`id`	INTEGER	primary key
`name`	TEXT	required, unique

`product_tags` 624 rows

Column	Type	Notes
`product_id`	INTEGER	primary key, → products.id
`tag_id`	INTEGER	primary key, → tags.id

`suppliers` 12 rows

Column	Type	Notes
`id`	INTEGER	primary key
`name`	TEXT	required
`contact_name`	TEXT
`email`	TEXT
`phone`	TEXT
`country`	TEXT
`rating`	REAL
`is_active`	INTEGER	required, default 1

`product_suppliers` 579 rows

Column	Type	Notes
`product_id`	INTEGER	primary key, → products.id
`supplier_id`	INTEGER	primary key, → suppliers.id
`supplier_sku`	TEXT
`unit_cost`	REAL	required
`lead_time_days`	INTEGER	required, default 7

`inventory_logs` 11,547 rows

Column	Type	Notes
`id`	INTEGER	primary key
`product_id`	INTEGER	→ products.id, required, indexed
`change_qty`	INTEGER	required
`reason`	TEXT	required
`reference`	TEXT
`employee_id`	INTEGER	→ employees.id
`logged_at`	TEXT	required, indexed

Customers

`customers` 1,500 rows

Column	Type	Notes
`id`	INTEGER	primary key
`first_name`	TEXT	required, indexed
`last_name`	TEXT	required, indexed
`email`	TEXT	required, unique
`phone`	TEXT
`birth_date`	TEXT
`country`	TEXT	required, indexed, default 'US'
`marketing_opt_in`	INTEGER	required, default 0
`loyalty_points`	INTEGER	required, default 0
`created_at`	TEXT	required
`updated_at`	TEXT	required

`addresses` 1,959 rows

Column	Type	Notes
`id`	INTEGER	primary key
`customer_id`	INTEGER	→ customers.id, required, indexed
`label`	TEXT	required, default 'home'
`line1`	TEXT	required
`line2`	TEXT
`city`	TEXT	required
`postal_code`	TEXT
`country`	TEXT	required
`is_default`	INTEGER	required, default 0

`wishlists` 2,493 rows

Column	Type	Notes
`customer_id`	INTEGER	primary key, → customers.id
`product_id`	INTEGER	primary key, → products.id
`added_at`	TEXT	required

`reviews` 2,992 rows

Column	Type	Notes
`id`	INTEGER	primary key
`product_id`	INTEGER	→ products.id, required, unique
`customer_id`	INTEGER	→ customers.id, required, unique
`rating`	INTEGER	required
`title`	TEXT
`body`	TEXT
`is_verified`	INTEGER	required, default 0
`helpful_votes`	INTEGER	required, default 0
`created_at`	TEXT	required

`notifications` 3,000 rows

Column	Type	Notes
`id`	INTEGER	primary key
`customer_id`	INTEGER	→ customers.id, required, indexed
`type`	TEXT	required
`title`	TEXT	required
`body`	TEXT
`is_read`	INTEGER	required, indexed, default 0
`created_at`	TEXT	required

Orders

`orders` 4,000 rows

Column	Type	Notes
`id`	INTEGER	primary key
`order_number`	TEXT	required, unique
`customer_id`	INTEGER	→ customers.id, required, indexed
`address_id`	INTEGER	→ addresses.id
`shipping_method_id`	INTEGER	→ shipping_methods.id
`coupon_id`	INTEGER	→ coupons.id
`status`	TEXT	required, indexed
`subtotal`	REAL	required, default 0
`discount`	REAL	required, default 0
`shipping_cost`	REAL	required, default 0
`total`	REAL	required, default 0
`notes`	TEXT
`ordered_at`	TEXT	required, indexed
`updated_at`	TEXT	required

`order_items` 12,091 rows

Column	Type	Notes
`id`	INTEGER	primary key
`order_id`	INTEGER	→ orders.id, required, unique
`product_id`	INTEGER	→ products.id, required, unique
`quantity`	INTEGER	required
`unit_price`	REAL	required

`payments` 3,651 rows

Column	Type	Notes
`id`	INTEGER	primary key
`order_id`	INTEGER	→ orders.id, required, indexed
`method`	TEXT	required
`amount`	REAL	required
`currency`	TEXT	required, default 'USD'
`status`	TEXT	required
`transaction_ref`	TEXT
`paid_at`	TEXT

`shipments` 2,319 rows

Column	Type	Notes
`id`	INTEGER	primary key
`order_id`	INTEGER	→ orders.id, required, indexed
`carrier`	TEXT	required
`tracking_number`	TEXT
`shipped_at`	TEXT
`delivered_at`	TEXT

`shipping_methods` 5 rows

Column	Type	Notes
`id`	INTEGER	primary key
`name`	TEXT	required, unique
`base_cost`	REAL	required
`est_days`	INTEGER	required
`is_active`	INTEGER	required, default 1

`coupons` 10 rows

Column	Type	Notes
`id`	INTEGER	primary key
`code`	TEXT	required, unique
`discount_type`	TEXT	required
`discount_value`	REAL	required
`min_order`	REAL	required, default 0
`starts_at`	TEXT	required
`ends_at`	TEXT	required
`max_uses`	INTEGER
`used_count`	INTEGER	required, default 0

Staff

`departments` 10 rows

Column	Type	Notes
`id`	INTEGER	primary key
`name`	TEXT	required, unique
`description`	TEXT
`created_at`	TEXT	required

`employees` 120 rows

Column	Type	Notes
`id`	INTEGER	primary key
`department_id`	INTEGER	→ departments.id, required, indexed
`manager_id`	INTEGER	→ employees.id, indexed
`first_name`	TEXT	required
`last_name`	TEXT	required
`email`	TEXT	required, unique
`phone`	TEXT
`hire_date`	TEXT	required
`salary`	REAL	required, default 0
`position`	TEXT
`is_active`	INTEGER	required, default 1

`audit_logs` 2,000 rows

Column	Type	Notes
`id`	INTEGER	primary key
`employee_id`	INTEGER	→ employees.id
`action`	TEXT	required
`table_name`	TEXT	required, indexed
`record_id`	INTEGER	indexed
`old_values`	TEXT
`new_values`	TEXT
`ip_address`	TEXT
`created_at`	TEXT	required

Settings

`settings` 8 rows

Column	Type	Notes
`key`	TEXT	primary key
`value`	TEXT	required
`description`	TEXT
`updated_at`	TEXT	required

Views

A view is a saved query that behaves like a table: you can SELECT from it, but there are no rows of its own stored anywhere. These four summarize the tables above.

`v_customer_lifetime` 1,500 rows

Column	Type	Notes
`customer_id`	INTEGER
`customer`	TEXT
`country`	TEXT
`orders`	TEXT
`revenue`	TEXT
`first_order`	TEXT
`last_order`	TEXT

`v_low_stock` 44 rows

Column	Type	Notes
`id`	INTEGER
`sku`	TEXT
`name`	TEXT
`stock_quantity`	INTEGER
`category`	TEXT

`v_order_summary` 4,000 rows

Column	Type	Notes
`order_id`	INTEGER
`order_number`	TEXT
`status`	TEXT
`ordered_at`	TEXT
`customer`	TEXT
`country`	TEXT
`items`	TEXT
`total`	REAL

`v_product_sales` 400 rows

Column	Type	Notes
`product_id`	INTEGER
`name`	TEXT
`sku`	TEXT
`category`	TEXT
`units_sold`	TEXT
`revenue`	TEXT
`avg_rating`	TEXT
`review_count`	TEXT

The sample shop database

Catalogue

categories 33 rows

products 400 rows

product_images 1,024 rows

tags 15 rows

product_tags 624 rows

suppliers 12 rows

product_suppliers 579 rows

inventory_logs 11,547 rows

Customers

customers 1,500 rows

addresses 1,959 rows

wishlists 2,493 rows

reviews 2,992 rows

notifications 3,000 rows

Orders

orders 4,000 rows

order_items 12,091 rows

payments 3,651 rows

shipments 2,319 rows

shipping_methods 5 rows

coupons 10 rows