Employees and their departments
Easy · Company database · JOIN, ORDER BY
Each employee's department_id points at a row in departments. Employees who do not belong to a department are left out.
Return the employee's name and the department's name, in that order, sorted by department name A to Z, then employee name A to Z.
Expected result
| employee | department |
|---|---|
| Ana Reyes | Customer Support |
| Carlos Mendoza | Customer Support |
| Joseph Mwangi | Customer Support |
| Arjun Patel | Engineering |
| Daniel Okoye | Engineering |
| Ethan Brooks | Engineering |
| Fatima Rahman | Engineering |
| Laura Chen | Engineering |
| Maria Gonzalez | Engineering |
| Vikram Nair | Engineering |
| Zara Ahmed | Engineering |
| Grace Liu | Finance |
| Mohammed Hassan | Finance |
| Rahul Bansal | Finance |
| Hannah Schmidt | Human Resources |
| Ines Moreau | Human Resources |
| Nadia Petrova | Marketing |
| Samuel Adeyemi | Marketing |
| Thandiwe Nkosi | Marketing |
| Eva Novak | Research |
| Kenji Watanabe | Research |
| Ravi Shankar | Research |
| Lucas Ferreira | Sales |
| Oliver Hughes | Sales |
| Priyanka Iyer | Sales |
| Sophie Laurent | Sales |
The company tables
assignments: 30 rows, first 5 shown
| id | employee_id | project_id | hours |
|---|---|---|---|
| 1 | 8 | 1 | 320 |
| 2 | 9 | 1 | 410 |
| 3 | 11 | 1 | 380 |
| 4 | 12 | 2 | 290 |
| 5 | 10 | 2 | 150 |
departments: 8 rows, first 5 shown
| id | name | location |
|---|---|---|
| 1 | Engineering | Bengaluru |
| 2 | Sales | London |
| 3 | Marketing | London |
| 4 | Finance | Singapore |
| 5 | Human Resources | Bengaluru |
employees: 27 rows, first 5 shown
| id | name | department_id | manager_id | job_title | salary | hire_date |
|---|---|---|---|---|---|---|
| 1 | Meera Krishnan | NULL | NULL | Chief Executive Officer | 250000 | 2015-03-01 |
| 2 | Daniel Okoye | 1 | 1 | Chief Technology Officer | 250000 | 2015-03-01 |
| 3 | Sophie Laurent | 2 | 1 | Head of Sales | 150000 | 2017-01-09 |
| 4 | Rahul Bansal | 4 | 1 | Finance Director | 166000 | 2016-11-21 |
| 5 | Hannah Schmidt | 5 | 1 | Human Resources Manager | 110000 | 2018-04-02 |
projects: 10 rows, first 5 shown
| id | name | department_id | budget | start_date | end_date |
|---|---|---|---|---|---|
| 1 | Payments Platform | 1 | 450000 | 2025-01-06 | 2025-09-30 |
| 2 | Mobile App Redesign | 1 | 300000 | 2025-03-03 | NULL |
| 3 | Data Warehouse | 1 | 380000 | 2025-06-02 | 2026-03-31 |
| 4 | Spring Campaign | 3 | 90000 | 2025-02-10 | 2025-05-30 |
| 5 | Partner Portal | 2 | 150000 | 2025-04-14 | 2025-12-19 |
-- Write your SQL here