Build a Multi-Rule Employee Directory
Return the employee directory using the requested IT, HR, Sales priority; senior-before-2020 placement; salary descending; and last_name ascending.
- CASE expressions
- Sorting
Exercise brief
Understand the request
People operations lead An executive directory combines custom department priority, seniority, compensation, and alphabetical fallback rules.
Build an employee directory sorted as follows: 1. Department in custom order (IT first, then HR, then Sales) 2. Senior employees first within each dept (hired BEFORE 2020-01-01) 3. Then salary descending 4. Then last_name ascending Show first_name, last_name, hire_date, salary, department_id, dept_name.
Return
- Return first_name, last_name, hire_date, salary, department_id, dept_name in this exact left-to-right order.
Constraints
- Use CASE for the custom department and seniority ranks.
- Keep salary and last_name as later tie-breakers.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
employees
first_nameTEXTlast_nameTEXThire_dateDATEsalaryDECIMALdepartment_idINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Tackle one sort key at a time. Translate each English bullet into either a column or a CASE expression.
Hint 2
Ranking groups (IT/HR/Sales) → CASE returning 1/2/3. Boolean test (senior?) → CASE returning 0/1. Numeric/text columns → as-is with ASC or DESC.
Hint 3
ORDER BY CASE dept-rank, CASE senior-flag, salary DESC, last_name
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT first_name, last_name, hire_date, salary, department_id, CASE department_id WHEN 1 THEN 'IT' WHEN 2 THEN 'HR' ELSE 'Sales' END AS dept_name FROM employees ORDER BY CASE department_id WHEN 1 THEN 1 WHEN 2 THEN 2 ELSE 3 END, CASE WHEN hire_date < '2020-01-01' THEN 0 ELSE 1 END, salary DESC, last_name;Why this works
Real-world ORDER BY clauses chain a custom group rank, a boolean flag, then conventional sorts. The explicit CASE form (rather than `hire_date < '2020-01-01' DESC`) is portable to SQL Server, where boolean-as-sort-key is rejected.
Success check
Every employee appears once and the final sequence satisfies all four business rules.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| first_name | last_name | hire_date | salary | department_id | dept_name |
|---|---|---|---|---|---|
| Alex | Miller | 2019-08-14 | 72000 | 1 | IT |
| Mike | Johnson | 2021-06-10 | 80000 | 1 | IT |
| Amy | Taylor | 2021-12-03 | 78000 | 1 | IT |
| John | Doe | 2020-01-15 | 75000 | 1 | IT |
| David | Brown | 2022-02-28 | 70000 | 1 | IT |
| Jane | Smith | 2019-03-22 | 55000 | 2 | HR |
| Lisa | Davis | 2020-09-12 | 58000 | 2 | HR |
| Emma | Thomas | 2023-01-10 | 52000 | 2 | HR |
| Sarah | Williams | 2018-11-05 | 65000 | 3 | Sales |
| Tom | Wilson | 2019-07-18 | 62000 | 3 | 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.