In order to demonstrate all the different ways to query data, multiple sample databases are used throughout the book. Let’s meet the data!
Carwash
carwash is a fictional self-service carwash business. In this databases, users can either pay per washing session or purchase a subscription that allows a number of services for monthly fee. The carwashes themselves are located in the locations table. Users can also create service tickets which can be handled by employees.
erDiagram
users ||--o{ subscriptions : has
users ||--o{ vouchers : holds
users ||--o{ sessions : visits
users ||--o{ requests : raises
plans ||--o{ subscriptions : "is for"
locations ||--o{ sessions : "hosts"
subscriptions ||--o{ sessions : covers
vouchers ||--o{ sessions : "redeemed in"
employees ||--o{ requests : answers
employees ||--o{ employees : manages
users {
INTEGER user_id PK
VARCHAR first_name
VARCHAR last_name
VARCHAR email
DATE signup_date
DATE confirmation_date
DATE activation_date
}
plans {
INTEGER plan_id PK
VARCHAR plan_name
VARCHAR tier
DOUBLE monthly_price
INTEGER washes_included
}
locations {
INTEGER location_id PK
VARCHAR location_name
VARCHAR city
VARCHAR country
INTEGER n_bays
DATE opened_date
}
subscriptions {
INTEGER subscription_id PK
INTEGER user_id FK
INTEGER plan_id FK
DATE date_from
DATE date_to
}
vouchers {
INTEGER voucher_id PK
VARCHAR code
INTEGER user_id FK
INTEGER discount_pct
DATE valid_from
DATE valid_to
BOOLEAN is_redeemed
}
sessions {
INTEGER session_id PK
INTEGER user_id FK
INTEGER location_id FK
INTEGER subscription_id FK
INTEGER voucher_id FK
TIMESTAMP started_at
TIMESTAMP ended_at
INTEGER duration_min
DOUBLE amount_paid
}
employees {
INTEGER employee_id PK
VARCHAR first_name
VARCHAR last_name
VARCHAR email
VARCHAR role
DATE hire_date
DOUBLE salary
BOOLEAN is_active
INTEGER manager_id FK
}
requests {
INTEGER request_id PK
INTEGER user_id FK
INTEGER employee_id FK
VARCHAR category
VARCHAR status
DATE submitted_at
DATE resolved_at
INTEGER satisfaction
}
calendar {
INTEGER date_key PK
DATE date
INTEGER year
INTEGER quarter
INTEGER month
VARCHAR month_name
INTEGER week
VARCHAR day_of_week
INTEGER day_of_month
INTEGER day_of_year
DATE start_of_month
BOOLEAN is_weekend
}
Figure 1: The carwash database schema. calendar is a standalone date dimension, joined on date columns rather than by a foreign key. employees is hierarchical via the self-referencing manager_id.
Try it yourself — the query below runs entirely in your browser. Edit it and press ▶ Run (or Ctrl/Cmd+Enter) to see how users, their subscriptions, and the plans they’re on fit together:
select u.user_id, u.first_name, u.last_name, p.plan_name, s.date_from, s.date_tofrom users ujoin subscriptions s on s.user_id = u.user_idjoin plans p on p.plan_id = s.plan_idorderby u.user_id, s.date_fromlimit10
Stocks
uses a fictional SaaS company database. All chapters query the same set of tables so you can build on what you’ve learned across chapters.
Entity-Relationship Diagram
erDiagram
customers ||--o{ orders : places
customers ||--o{ subscriptions : subscribes
customers ||--o{ support_tickets : opens
products ||--o{ subscriptions : "is for"
products ||--o{ order_items : "is for"
orders ||--o{ order_items : contains
departments ||--o{ employees : employs
employees ||--o{ employees : manages
employees ||--o{ support_tickets : "assigned to"
customers {
INTEGER customer_id PK
VARCHAR first_name
VARCHAR last_name
VARCHAR email
VARCHAR city
VARCHAR country
DATE signup_date
VARCHAR customer_segment
}
orders {
INTEGER order_id PK
INTEGER customer_id FK
DATE order_date
VARCHAR status
}
order_items {
INTEGER order_item_id PK
INTEGER order_id FK
INTEGER product_id FK
INTEGER quantity
DOUBLE unit_price
DOUBLE line_total
}
products {
INTEGER product_id PK
VARCHAR product_name
VARCHAR category
DOUBLE unit_price
BOOLEAN is_recurring
}
subscriptions {
INTEGER subscription_id PK
INTEGER customer_id FK
INTEGER product_id FK
VARCHAR status
DATE valid_from
DATE valid_to
DOUBLE monthly_amount
}
support_tickets {
INTEGER ticket_id PK
INTEGER customer_id FK
INTEGER assigned_to FK
VARCHAR category
VARCHAR priority
VARCHAR status
DATE created_date
DATE resolved_date
INTEGER satisfaction_score
}
employees {
INTEGER employee_id PK
VARCHAR first_name
VARCHAR last_name
VARCHAR email
INTEGER department_id FK
DATE hire_date
DOUBLE salary
VARCHAR city
BOOLEAN is_active
INTEGER manager_id FK
}
departments {
INTEGER department_id PK
VARCHAR department_name
}
calendar {
INTEGER date_key PK
DATE date
DOUBLE year
INTEGER quarter
DOUBLE month
ENUM month_name
DOUBLE week
ENUM day_of_week
INTEGER day_of_month
DOUBLE day_of_year
DATE start_of_month
BOOLEAN is_weekend
DOUBLE fiscal_year
DOUBLE fiscal_quarter
}
Figure 2: The cookbook database schema. calendar is a standalone date dimension, joined on date columns rather than by a foreign key.