EasyHR10 min

Employees and departments

JOINs SELECT

Challenge

Pair every employee with the department they sit in.

Business context

Finance is mapping headcount to office location for the budget workbook. Every current employee has a department.

Instructions

Return employee_name, title, department_name, and location. Sort by department name, then employee name.

Schema

departments

id INTEGER · primary key
name TEXT
location TEXT

employees

id INTEGER · primary key
name TEXT
email TEXT
department_id INTEGER
manager_id INTEGER · employees.id, nullable
hire_date TEXT
title TEXT

salaries

id INTEGER · primary key
employee_id INTEGER
amount INTEGER · annual salary in dollars
effective_date TEXT

job_history

id INTEGER · primary key
employee_id INTEGER
title TEXT
department_id INTEGER
start_date TEXT
end_date TEXT

Sample rows

departments
id	name	location
1	Engineering	Berlin
2	Sales	London
3	People	New York

employees
id	name	email	department_id	manager_id	hire_date	title
1	Amira Hassan	amira@querylab.example	1	NULL	2021-03-01	Staff Engineer
2	Leo Martins	leo@querylab.example	1	1	2022-06-15	Software Engineer
3	Priya Nair	priya@querylab.example	1	1	2023-01-09	Software Engineer

salaries
id	employee_id	amount	effective_date
1	1	145000	2024-01-01
2	1	132000	2023-01-01
3	2	98000	2024-01-01

job_history
id	employee_id	title	department_id	start_date	end_date
1	2	Junior Engineer	1	2022-06-15	2023-12-31
2	3	Junior Engineer	1	2023-01-09	2024-03-31
3	5	SDR	2	2023-04-17	2024-06-30

Draft saves automatically

Ctrl or Cmd + Enter runs the query. The database resets on every run.

Run the query to see rows from the sample database.

Next exercise: Above-average current salaries