SQL · practice
The practice database
Every SQL lesson and interview problem uses these six small tables. The awkward cases are planted on purpose: NULLs, ties, duplicates, and a customer with no orders. Get to know the rows, and you can check any answer by eye.
Run any query
This box runs on a fresh copy of the database every time, so INSERT, UPDATE and DELETE are safe to try: nothing you do here can break a lesson.
Run any query on the practice database
Your queries in this box
How the tables connect
erDiagram
DEPARTMENTS ||--o{ EMPLOYEES : "has"
EMPLOYEES |o--o{ EMPLOYEES : "manages"
CUSTOMERS ||--o{ ORDERS : "places"
ORDERS ||--o{ ORDER_ITEMS : "contains"
PRODUCTS ||--o{ ORDER_ITEMS : "is in"
DEPARTMENTS {
int dept_id PK
text dept_name
text location
}
EMPLOYEES {
int emp_id PK
text name
int dept_id FK
int manager_id FK
int salary
text hire_date
}
CUSTOMERS {
int customer_id PK
text name
text email
text city
text signup_date
}
ORDERS {
int order_id PK
int customer_id FK
text order_date
int amount
text status
}
PRODUCTS {
int product_id PK
text product_name
text category
int price
}
ORDER_ITEMS {
int order_id PK, FK
int product_id PK, FK
int quantity
}The six tables
departments 4 rows
One row per department. HR has nobody in it yet.
| dept_id PK | dept_name | location |
|---|---|---|
| 1 | Engineering | London |
| 2 | Sales | Manchester |
| 3 | Marketing | London |
| 4 | HR | Leeds |
What each column means
dept_idINTEGER PRIMARY KEY: The department's id.dept_nameTEXT NOT NULL: Its name.locationTEXT: The city it is based in.
employees 8 rows
One row per employee. manager_id points at another row in this same table.
| emp_id PK | name | dept_id FK | manager_id FK | salary | hire_date |
|---|---|---|---|---|---|
| 1 | Asha | 1 | NULL | 95000 | 2019-03-01 |
| 2 | Ben | 1 | 1 | 70000 | 2020-06-15 |
| 3 | Chen | 1 | 1 | 70000 | 2021-01-10 |
| 4 | Dara | 2 | 1 | 60000 | 2020-02-20 |
| 5 | Eli | 2 | 4 | 45000 | 2022-07-01 |
| 6 | Fatima | 3 | 1 | 52000 | 2021-09-12 |
| 7 | Gus | NULL | 4 | 38000 | 2023-04-03 |
| 8 | Hana | 1 | 2 | 80000 | 2022-11-30 |
What each column means
emp_idINTEGER PRIMARY KEY: The employee's id.nameTEXT NOT NULL: First name.dept_idINTEGER REFERENCES departments(dept_id): Their department. NULL for Gus.manager_idINTEGER REFERENCES employees(emp_id): The emp_id of their manager. NULL for Asha, who has no manager.salaryINTEGER NOT NULL: Yearly salary in pounds.hire_dateTEXT NOT NULL: The day they joined (YYYY-MM-DD).
customers 6 rows
One row per sign-up. Tom signed up twice with the same email.
| customer_id PK | name | city | signup_date | |
|---|---|---|---|---|
| 1 | Priya | priya@mail.com | London | 2024-01-02 |
| 2 | Tom | tom@mail.com | Leeds | 2024-01-20 |
| 3 | Lena | lena@mail.com | London | 2024-01-30 |
| 4 | Omar | omar@mail.com | Bristol | 2024-02-14 |
| 5 | Zoe | zoe@mail.com | NULL | 2024-03-10 |
| 6 | Tom | tom@mail.com | Leeds | 2024-03-20 |
What each column means
customer_idINTEGER PRIMARY KEY: The customer's id.nameTEXT NOT NULL: First name.emailTEXT NOT NULL: Email address. Not unique, so duplicates can get in.cityTEXT: Home city. NULL for Zoe, who didn't say.signup_dateTEXT NOT NULL: The day they signed up.
orders 10 rows
One row per order. Order 106 was cancelled before an amount was recorded.
| order_id PK | customer_id FK | order_date | amount | status |
|---|---|---|---|---|
| 101 | 1 | 2024-01-06 | 120 | delivered |
| 102 | 1 | 2024-01-07 | 80 | delivered |
| 103 | 1 | 2024-01-08 | 45 | delivered |
| 104 | 2 | 2024-01-21 | 200 | delivered |
| 105 | 1 | 2024-02-02 | 150 | shipped |
| 106 | 3 | 2024-02-12 | NULL | cancelled |
| 107 | 3 | 2024-02-20 | 60 | delivered |
| 108 | 2 | 2024-03-01 | 75 | delivered |
| 109 | 1 | 2024-03-10 | 30 | delivered |
| 110 | 5 | 2024-03-16 | 33 | pending |
What each column means
order_idINTEGER PRIMARY KEY: The order's id.customer_idINTEGER NOT NULL REFERENCES customers(customer_id): Who placed it.order_dateTEXT NOT NULL: The day it was placed.amountINTEGER: The total in pounds. NULL for order 106.statusTEXT NOT NULL: delivered, shipped, cancelled or pending.
products 7 rows
One row per product. Nobody has bought the phone case.
| product_id PK | product_name | category | price |
|---|---|---|---|
| 1 | Pen | Stationery | 2 |
| 2 | Notebook | Stationery | 5 |
| 3 | Mug | Kitchen | 8 |
| 4 | Desk lamp | Home | 25 |
| 5 | Backpack | Bags | 30 |
| 6 | Headphones | Electronics | 40 |
| 7 | Phone case | Electronics | 15 |
What each column means
product_idINTEGER PRIMARY KEY: The product's id.product_nameTEXT NOT NULL: Its name.categoryTEXT NOT NULL: The shop section it belongs to.priceINTEGER NOT NULL: Price in pounds.
order_items 14 rows
Which products are in which order, and how many. It links orders and products (many-to-many). Order 106 has no items.
| order_id FK | product_id FK | quantity |
|---|---|---|
| 101 | 6 | 3 |
| 102 | 4 | 2 |
| 102 | 5 | 1 |
| 103 | 3 | 5 |
| 103 | 2 | 1 |
| 104 | 6 | 5 |
| 105 | 6 | 3 |
| 105 | 5 | 1 |
| 107 | 5 | 2 |
| 108 | 4 | 3 |
| 109 | 1 | 5 |
| 109 | 2 | 4 |
| 110 | 4 | 1 |
| 110 | 3 | 1 |
What each column means
order_idINTEGER NOT NULL REFERENCES orders(order_id): The order.product_idINTEGER NOT NULL REFERENCES products(product_id): The product.quantityINTEGER NOT NULL: How many were bought.
My saved queries
Every query you change and run in any SQL lesson is saved in this browser, under the topic and the box you ran it in. Download my progress backs them up.
No saved queries yet.