Pagila — the course database

Every runnable code box in this course queries pagila, a sample database for a DVD-rental business: films, the actors in them, stores with inventory, customers, rentals, and payments. It loads into the in-browser engine automatically — every run starts from this exact data, so you can never break it.

How the tables relate

country ─< city ─< address ─< customer ─────────┐
                      │                          │
                      ├──< store ─< inventory >──┼──< rental >── payment
                      │       │        │         │      │
                      └──< staff       │         │      │
                                       │         │      │
language ─< film ──────────────────────┘         │      │
             │                        (customer ─┘      └─ staff)
             ├──< film_actor >── actor
             └──< film_category >── category

  A ─< B  means "one A has many B"  ·  >── means the join goes through that table

The busiest join path, worth memorizing: film → inventory → rental → payment — a film is stocked as inventory copies, a copy gets rented, a rental gets paid for.

Tables

actor · 200 rows

Column	Type	Nullable	Key
`actor_id`	integer	no	PK
`first_name`	text	no
`last_name`	text	no
`last_update`	timestamp with time zone	no

address · 603 rows

Column	Type	Nullable	Key
`address_id`	integer	no	PK
`address`	text	no
`address2`	text	yes
`district`	text	no
`city_id`	integer	no	FK → city.city_id
`postal_code`	text	yes
`phone`	text	no
`last_update`	timestamp with time zone	no

category · 16 rows

Column	Type	Nullable	Key
`category_id`	integer	no	PK
`name`	text	no
`last_update`	timestamp with time zone	no

city · 600 rows

Column	Type	Nullable	Key
`city_id`	integer	no	PK
`city`	text	no
`country_id`	integer	no	FK → country.country_id
`last_update`	timestamp with time zone	no

country · 109 rows

Column	Type	Nullable	Key
`country_id`	integer	no	PK
`country`	text	no
`last_update`	timestamp with time zone	no

customer · 599 rows

Column	Type	Nullable	Key
`customer_id`	integer	no	PK
`store_id`	integer	no	FK → store.store_id
`first_name`	text	no
`last_name`	text	no
`email`	text	yes
`address_id`	integer	no	FK → address.address_id
`activebool`	boolean	no
`create_date`	date	no
`last_update`	timestamp with time zone	yes
`active`	integer	yes

film · 1,000 rows

Column	Type	Nullable	Key
`film_id`	integer	no	PK
`title`	text	no
`description`	text	yes
`release_year`	integer	yes
`language_id`	integer	no	FK → language.language_id
`original_language_id`	integer	yes	FK → language.language_id
`rental_duration`	smallint	no
`rental_rate`	numeric	no
`length`	smallint	yes
`replacement_cost`	numeric	no
`rating`	USER-DEFINED	yes
`last_update`	timestamp with time zone	no
`special_features`	ARRAY	yes

film_actor · 5,462 rows

Column	Type	Nullable	Key
`actor_id`	integer	no	PK · FK → actor.actor_id
`film_id`	integer	no	PK · FK → film.film_id
`last_update`	timestamp with time zone	no

film_category · 2,367 rows

Column	Type	Nullable	Key
`film_id`	integer	no	PK · FK → film.film_id
`category_id`	integer	no	PK · FK → category.category_id
`last_update`	timestamp with time zone	no

inventory · 4,581 rows

Column	Type	Nullable	Key
`inventory_id`	integer	no	PK
`film_id`	integer	no	FK → film.film_id
`store_id`	integer	no	FK → store.store_id
`last_update`	timestamp with time zone	no

language · 6 rows

Column	Type	Nullable	Key
`language_id`	integer	no	PK
`name`	character(20)	no
`last_update`	timestamp with time zone	no

payment · 16,049 rows

Column	Type	Nullable	Key
`payment_id`	integer	no	PK
`customer_id`	integer	no
`staff_id`	integer	no
`rental_id`	integer	no
`amount`	numeric	no
`payment_date`	timestamp with time zone	no	PK

rental · 16,044 rows

Column	Type	Nullable	Key
`rental_id`	integer	no	PK
`rental_date`	timestamp with time zone	no
`inventory_id`	integer	no	FK → inventory.inventory_id
`customer_id`	integer	no	FK → customer.customer_id
`return_date`	timestamp with time zone	yes
`staff_id`	integer	no	FK → staff.staff_id
`last_update`	timestamp with time zone	no

staff · 1,500 rows

Column	Type	Nullable	Key
`staff_id`	integer	no	PK
`first_name`	text	no
`last_name`	text	no
`address_id`	integer	no	FK → address.address_id
`email`	text	yes
`store_id`	integer	no	FK → store.store_id
`active`	boolean	no
`username`	text	no
`last_update`	timestamp with time zone	no

store · 500 rows

Column	Type	Nullable	Key
`store_id`	integer	no	PK
`manager_staff_id`	integer	no
`address_id`	integer	no	FK → address.address_id
`last_update`	timestamp with time zone	no