SQL Tables
Your Course Tables
The seven tables you will use throughout this Beginner SQL Commands Practice course — a complete business operations database covering users, transactions, products, orders, order items, subscriptions, and support tickets.
What These Tables Are For
- Practice every SQL command covered in the course — SELECT, WHERE, ORDER BY, LIMIT, GROUP BY, and HAVING — against realistic business data that mirrors what analysts work with every day
- Answer real business questions: which users are from Canada, which transactions exceed a threshold, which products are most expensive, which orders are pending or refunded
- Build grouped summaries across categories — users by country, revenue by transaction type, orders by status, subscriptions by plan tier — using aggregation functions on real data
- Trace the complete business operations picture: a customer signs up (users), browses and buys (orders, order_items, products), pays over time (transactions, subscriptions), and contacts support (support_tickets)
The users table stores customer profile information for the eight registered users. Each user has a first and last name, a signup date, a country, and a user_type of either Retail or Business. This table is referenced by transactions, orders, sessions, campaign_events, support_tickets, and subscriptions via user_id — making it the central identity table for the entire database.
SELECT all users, filter by country using WHERE, sort by signup_date using ORDER BY, count users by country with GROUP BY, and show only countries with more than one user using HAVING. All six commands in the course can be practised directly against this table.
| user_id | first_name | last_name | signup_date | country | user_type |
|---|---|---|---|---|---|
| 1 | Ava | Brown | 2024-01-05 | Canada | Retail |
| 2 | Liam | Wilson | 2024-01-12 | Canada | Business |
| 3 | Maya | Singh | 2024-02-03 | United States | Retail |
| 4 | Noah | Chen | 2024-02-18 | Canada | Retail |
| 5 | Sophia | Martin | 2024-03-07 | United Kingdom | Business |
| 6 | Ethan | Davis | 2024-03-25 | Canada | Retail |
| 7 | Isabella | Garcia | 2024-04-02 | United States | Business |
| 8 | Lucas | Taylor | 2024-04-15 | Canada | Retail |
| Column Name | Data Type | Constraints | Explanation |
|---|---|---|---|
| user_id | INT | PRIMARY KEY | Unique identifier (1–8). Referenced as a foreign key by all other tables. |
| first_name | VARCHAR(50) | None | Customer first name. Used in LIKE pattern filtering (e.g. first_name LIKE ‘A%’). |
| last_name | VARCHAR(50) | None | Customer surname. |
| signup_date | DATE | None | Date the account was created. Used for ORDER BY and date-range filtering. |
| country | VARCHAR(50) | None | Canada (5), United States (2), United Kingdom (1). Key GROUP BY field for geographic segmentation. |
| user_type | VARCHAR(50) | None | Retail (5) or Business (3). Used in WHERE filters and GROUP BY summaries. |
The transactions table stores financial transaction activity. Each of the eight users has exactly one transaction in the dataset. Transaction 208 (Lucas Taylor, user 8) is a Refund — the only non-Purchase transaction — making it an important row to isolate with WHERE filters. Amounts range from $29.99 to $149.99, giving a meaningful range for numeric comparisons.
Use WHERE amount > 100 to isolate high-value transactions. Use ROUND(amount, 2) in SELECT to display clean decimal output. Use GROUP BY transaction_type with SUM(amount) to compare total revenue by transaction category. Use HAVING SUM(amount) > 500 to show only high-volume categories.
| transaction_id | user_id | transaction_date | amount | transaction_type |
|---|---|---|---|---|
| 201 | 1 — Ava Brown | 2024-01-10 | $49.99 | Purchase |
| 202 | 2 — Liam Wilson | 2024-01-20 | $129.98 | Purchase |
| 203 | 3 — Maya Singh | 2024-02-10 | $29.99 | Purchase |
| 204 | 4 — Noah Chen | 2024-02-25 | $39.99 | Purchase |
| 205 | 5 — Sophia Martin | 2024-03-12 | $149.99 | Purchase |
| 206 | 6 — Ethan Davis | 2024-03-29 | $79.99 | Purchase |
| 207 | 7 — Isabella Garcia | 2024-04-09 | $99.99 | Purchase |
| 208 | 8 — Lucas Taylor | 2024-04-20 | $39.99 | Refund |
| Column Name | Data Type | Constraints | Explanation |
|---|---|---|---|
| transaction_id | INT | PRIMARY KEY | Unique identifier (201–208). |
| user_id | INT | FOREIGN KEY → users | The customer who generated this transaction. One transaction per user. |
| transaction_date | DATE | None | Date of the transaction. Used in ORDER BY and date-range filtering. Ranges from 2024-01-10 to 2024-04-20. |
| amount | DECIMAL(10,2) | None | Transaction value. Ranges from $29.99 to $149.99. Use ROUND(amount, 2) in SELECT for clean decimal output. |
| transaction_type | VARCHAR(50) | None | Purchase (7) or Refund (1 — transaction 208). Used in WHERE and GROUP BY exercises. |
The products table stores the product catalogue. Six products span four categories: Software (2), Education (2), Service (1), and Report (1). Unit prices range from $29.99 to $149.99. Products link to order_items via product_id, allowing you to calculate total revenue per product by joining these two tables.
Use ORDER BY unit_price ASC with LIMIT to find the cheapest products. Use GROUP BY category with AVG(unit_price) to calculate average price per category. Use HAVING AVG(unit_price) > 50 to identify premium categories.
| product_id | product_name | category | unit_price |
|---|---|---|---|
| 101 | Analytics Starter Kit | Software | $49.99 |
| 102 | Business Dashboard Template | Software | $79.99 |
| 103 | Data Cleaning Workbook | Education | $29.99 |
| 104 | SQL Practice Pack | Education | $39.99 |
| 105 | Premium Support Add-on | Service | $99.99 |
| 106 | Marketing Insights Report | Report | $149.99 |
| Column Name | Data Type | Constraints | Explanation |
|---|---|---|---|
| product_id | INT | PRIMARY KEY | Unique identifier (101–106). Referenced by order_items. |
| product_name | VARCHAR(100) | None | Full product name. Used in SELECT output and LIKE pattern searches. |
| category | VARCHAR(50) | None | Software (2), Education (2), Service (1), Report (1). Primary GROUP BY field for product analysis. |
| unit_price | DECIMAL(10,2) | None | List price per unit. Ranges from $29.99 to $149.99. Use ROUND(unit_price, 2) for clean output. |
The orders table stores customer order activity. Eight orders cover all eight users — one order per user. Six orders are Completed, one is Pending (order 307, Isabella Garcia), and one is Refunded (order 308, Lucas Taylor). The order_status field is the key filtering column for business questions about fulfilment and returns.
Use WHERE order_status = ‘Completed’ to isolate fulfilled orders. Use ORDER BY order_date DESC to see the most recent orders first. Use GROUP BY order_status with COUNT(*) to summarize fulfilment rates. Use HAVING COUNT(*) > 2 to show only statuses with meaningful volume.
| order_id | user_id | order_date | order_status |
|---|---|---|---|
| 301 | 1 — Ava Brown | 2024-01-10 | Completed |
| 302 | 2 — Liam Wilson | 2024-01-20 | Completed |
| 303 | 3 — Maya Singh | 2024-02-10 | Completed |
| 304 | 4 — Noah Chen | 2024-02-25 | Completed |
| 305 | 5 — Sophia Martin | 2024-03-12 | Completed |
| 306 | 6 — Ethan Davis | 2024-03-29 | Completed |
| 307 | 7 — Isabella Garcia | 2024-04-09 | Pending |
| 308 | 8 — Lucas Taylor | 2024-04-20 | Refunded |
| Column Name | Data Type | Constraints | Explanation |
|---|---|---|---|
| order_id | INT | PRIMARY KEY | Unique identifier (301–308). Referenced by order_items. |
| user_id | INT | FOREIGN KEY → users | The customer who placed the order. One order per user in this dataset. |
| order_date | DATE | None | Date the order was placed. Ranges from 2024-01-10 to 2024-04-20. |
| order_status | VARCHAR(50) | None | Completed (6), Pending (1 — order 307), Refunded (1 — order 308). Primary WHERE and GROUP BY field. |
The order_items table stores the individual products within each order. Nine line items span eight orders — order 302 (Liam Wilson) has two line items (products 101 and 102), making it the only multi-product order in the dataset. Each row records the order it belongs to, the product purchased, the quantity, and the pre-calculated line total.
Use GROUP BY product_id with SUM(line_total) to calculate total revenue per product. Use HAVING SUM(line_total) > 100 to identify high-revenue products. Use ORDER BY line_total DESC with LIMIT to find the highest-value line items.
| order_item_id | order_id | product_id | quantity | line_total |
|---|---|---|---|---|
| 401 | 301 | 101 — Analytics Starter Kit | 1 | $49.99 |
| 402 | 302 | 101 — Analytics Starter Kit | 1 | $49.99 |
| 403 | 302 | 102 — Business Dashboard Template | 1 | $79.99 |
| 404 | 303 | 103 — Data Cleaning Workbook | 1 | $29.99 |
| 405 | 304 | 104 — SQL Practice Pack | 1 | $39.99 |
| 406 | 305 | 106 — Marketing Insights Report | 1 | $149.99 |
| 407 | 306 | 102 — Business Dashboard Template | 1 | $79.99 |
| 408 | 307 | 105 — Premium Support Add-on | 1 | $99.99 |
| 409 | 308 | 104 — SQL Practice Pack | 1 | $39.99 |
| Column Name | Data Type | Constraints | Explanation |
|---|---|---|---|
| order_item_id | INT | PRIMARY KEY | Unique identifier (401–409). |
| order_id | INT | FOREIGN KEY → orders | The parent order. Order 302 has two items (402 and 403); all others have one. |
| product_id | INT | FOREIGN KEY → products | The product purchased. Product 101 appears twice; product 102 appears twice; product 104 appears twice. |
| quantity | INT | None | All quantities are 1 in this dataset — one unit per line item. |
| line_total | DECIMAL(10,2) | None | Pre-calculated as unit_price × quantity. The primary aggregation column for product revenue analysis. |
The subscriptions table stores recurring customer subscriptions. Eight subscriptions cover all eight users. Six are Active (end_date IS NULL — still running), and two are Cancelled (subscriptions 903 and 906, with end_date values). Monthly fees are either $19.99 or $49.99, representing two subscription tiers. The NULL end_dates are central to IS NULL practice exercises.
Use WHERE end_date IS NULL to identify active subscriptions. Use WHERE end_date IS NOT NULL to find cancelled ones. Use GROUP BY status with SUM(monthly_fee) to compare revenue by subscription tier. Use HAVING SUM(monthly_fee) > 300 to show only high-value subscription groups.
| subscription_id | user_id | start_date | end_date | monthly_fee | status |
|---|---|---|---|---|---|
| 901 | 1 — Ava Brown | 2024-01-10 | NULL | $19.99 | Active |
| 902 | 2 — Liam Wilson | 2024-01-20 | NULL | $49.99 | Active |
| 903 | 3 — Maya Singh | 2024-02-10 | 2024-04-10 | $19.99 | Cancelled |
| 904 | 4 — Noah Chen | 2024-02-25 | NULL | $19.99 | Active |
| 905 | 5 — Sophia Martin | 2024-03-12 | NULL | $49.99 | Active |
| 906 | 6 — Ethan Davis | 2024-03-29 | 2024-04-29 | $19.99 | Cancelled |
| 907 | 7 — Isabella Garcia | 2024-04-09 | NULL | $49.99 | Active |
| 908 | 8 — Lucas Taylor | 2024-04-20 | NULL | $19.99 | Active |
| Column Name | Data Type | Constraints | Explanation |
|---|---|---|---|
| subscription_id | INT | PRIMARY KEY | Unique identifier (901–908). |
| user_id | INT | FOREIGN KEY → users | The subscribing customer. One subscription per user. |
| start_date | DATE | None | When the subscription began. Ranges from 2024-01-10 to 2024-04-20. |
| end_date | DATE | None (NULLable) | When the subscription ended. NULL (6) = still active. Date value (2) = cancelled. IS NULL is a key practice operator on this column. |
| monthly_fee | DECIMAL(10,2) | None | $19.99 (5 users) or $49.99 (3 users). Used in SUM and AVG aggregation exercises. |
| status | VARCHAR(50) | None | Active (6) or Cancelled (2 — subscriptions 903 and 906). Primary GROUP BY field for subscription analysis. |
The support_tickets table stores customer support interactions. Eight tickets cover all eight users — one ticket per user. Five are Resolved (with a resolved_date), two are Open (resolved_date IS NULL), and one is Pending (resolved_date IS NULL). The NULL resolved_dates represent unresolved cases and are a key field for IS NULL filtering exercises.
Use WHERE ticket_status = ‘Open’ to find unresolved tickets. Use WHERE resolved_date IS NULL to identify all tickets still awaiting resolution. Use GROUP BY ticket_status with COUNT(*) to summarize support workload. Use HAVING COUNT(*) > 1 to show statuses with multiple tickets.
| ticket_id | user_id | created_date | resolved_date | ticket_status |
|---|---|---|---|---|
| 801 | 1 — Ava Brown | 2024-01-11 | 2024-01-12 | Resolved |
| 802 | 2 — Liam Wilson | 2024-01-22 | 2024-01-25 | Resolved |
| 803 | 3 — Maya Singh | 2024-02-12 | 2024-02-13 | Resolved |
| 804 | 4 — Noah Chen | 2024-03-01 | NULL | Open |
| 805 | 5 — Sophia Martin | 2024-03-14 | 2024-03-16 | Resolved |
| 806 | 6 — Ethan Davis | 2024-04-01 | NULL | Open |
| 807 | 7 — Isabella Garcia | 2024-04-10 | 2024-04-12 | Resolved |
| 808 | 8 — Lucas Taylor | 2024-04-21 | NULL | Pending |
| Column Name | Data Type | Constraints | Explanation |
|---|---|---|---|
| ticket_id | INT | PRIMARY KEY | Unique identifier (801–808). |
| user_id | INT | FOREIGN KEY → users | The customer who raised the ticket. One ticket per user. |
| created_date | DATE | None | Date the ticket was raised. Used in ORDER BY sorting exercises. |
| resolved_date | DATE | None (NULLable) | Date resolved. NULL (3 — tickets 804, 806, 808) = still open. Use IS NULL to find unresolved tickets. |
| ticket_status | VARCHAR(50) | None | Resolved (5), Open (2 — tickets 804, 806), Pending (1 — ticket 808). Primary GROUP BY field for support workload analysis. |
Quick Reference — All 7 Tables
The complete sql_challenges_capstone_course database at a glance.
8 rows — Retail/Business, 3 countries. PK: user_id (1–8). Canada has 5 users.
8 rows — 7 Purchase, 1 Refund. PK: transaction_id (201–208). Amounts $29.99–$149.99.
6 rows — 4 categories. PK: product_id (101–106). Prices $29.99–$149.99.
8 rows — 6 Completed, 1 Pending, 1 Refunded. PK: order_id (301–308).
9 rows — bridge table, qty and line total. PK: order_item_id (401–409). Order 302 = 2 items.
8 rows — 6 Active, 2 Cancelled. PK: subscription_id (901–908). NULL end_date = still active.
8 rows — 5 Resolved, 2 Open, 1 Pending. PK: ticket_id (801–808). NULL resolved_date = unresolved.
