Sort Employees by Joined Department Name
Join employees to departments and return first_name, last_name, salary, and department_name, ordered by department_name ascending and salary descending.
- Joins
- Sorting
Exercise brief
Understand the request
HR reporting analyst A directory displays department names from a lookup table and sorts by that business label.
List employees sorted by department_name (the human-readable name from departments) ascending, then salary descending. Show first_name, last_name, salary, department_name.
Return
- Return one row per employee with its department name.
- Sort by the joined label, then salary.
Constraints
- Use an explicit join on department_id.
- Qualify the joined sort column.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
departments
department_idINTEGERdepartment_nameTEXT
employees
first_nameTEXTlast_nameTEXTsalaryDECIMALdepartment_idINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Sorting by `department_id` (a number) is fast but produces a meaningless order to humans. Joining to departments lets you sort by `department_name` (text).
Hint 2
Qualify columns with the table alias (`d.department_name`) when more than one table is in scope.
Hint 3
ORDER BY d.department_name, e.salary DESC
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT e.first_name, e.last_name, e.salary, d.department_name FROM employees e INNER JOIN departments d ON e.department_id = d.department_id ORDER BY d.department_name, e.salary DESC;Why this works
A frequent reporting pattern: store FKs (`department_id`) for normalisation, but sort and display the joined-in human name. The cost is one JOIN per query — usually cheap thanks to PK/FK indexes.
Success check
Employees are grouped by department name and salary-ranked inside each group.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| first_name | last_name | salary | department_name |
|---|---|---|---|
| Lisa | Davis | 58000 | Human Resources |
| Jane | Smith | 55000 | Human Resources |
| Emma | Thomas | 52000 | Human Resources |
| Mike | Johnson | 80000 | Information Technology |
| Amy | Taylor | 78000 | Information Technology |
| John | Doe | 75000 | Information Technology |
| Alex | Miller | 72000 | Information Technology |
| David | Brown | 70000 | Information Technology |
| Chris | Anderson | 68000 | Sales |
| Sarah | Williams | 65000 | Sales |
Previewing 10 of 12 expected rows. Run the query in the editor to inspect the full result.
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
LIMIT & OFFSET
Practice deterministic top-N, cutoff ties, offset pagination, composite keyset cursors, and resumable bounded batches.
Ranking & NTH Value
Solve deterministic ranking, top-N, distribution, positional-frame, and rolling-window problems.
SELECT Statements
Select columns, filter rows, remove duplicates, and order query results.
Open the interactive workspace and practice across SQL topics.