Find Salaries Higher Than All HR Salaries
Apply ALL semantics to the HR salary subquery and return the qualifying employees.
- Subqueries
- Filtering
- Sorting
Exercise brief
Understand the request
Compensation benchmarking analyst A stricter benchmark report needs employees paid above every salary in HR.
Find every employee whose salary exceeds the salary of EVERY HR (department_id = 20) employee. `> ALL` is equivalent to `> MAX(...)`. Return employee_id, first_name, last_name, salary — ordered by salary DESC, employee_id.
Return
- Return employee_id, first_name, last_name, salary in this exact left-to-right order.
Constraints
- Use > ALL (SELECT ...) on engines that support quantified predicates.
- On Core SQL, use the equivalent > (SELECT MAX(...)) form.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
employees
employee_idINTEGERfirst_nameVARCHAR(50)last_nameVARCHAR(50)salaryINTEGERdepartment_idINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
`> ALL (subquery)` ≡ `> MAX(subquery)`. Every element must be beaten.
Hint 2
Empty subquery: `> ALL (∅)` is TRUE — vacuously true. (Mirrors first-order logic.)
Hint 3
Watch out: `<> ALL (set)` ≡ `NOT IN (set)` — and inherits the NULL pitfall (Q15).
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT employee_id, first_name, last_name, salary FROM employees WHERE salary > (SELECT MAX(salary) FROM employees WHERE department_id = 20) ORDER BY salary DESC, employee_id;Why this works
ALL is the universal quantifier in SQL. The empty-set edge case (vacuously true) trips people up — guard with EXISTS if needed. ALL with NULLs in the subquery has the same three-valued-logic issues as NOT IN. SQLite has no ALL keyword — use > MAX(...) instead.
Success check
An employee qualifies only when their salary is greater than every HR salary.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| employee_id | first_name | last_name | salary |
|---|---|---|---|
| 100 | John | Smith | 120000 |
| 105 | Emma | Taylor | 95000 |
| 107 | Grace | White | 90000 |
| 101 | Alice | Johnson | 85000 |
| 102 | Bob | Wilson | 80000 |
| 104 | David | Brown | 70000 |
| 109 | Ivy | Martinez | 68000 |
| 106 | Frank | Green | 65000 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
CTEs & Window Functions
Practice modular CTE pipelines, deterministic window analytics, period comparisons, deduplication, frames, and gaps-and-islands.
SQL Joins
Practice reliable INNER, LEFT, FULL, CROSS, self, semi, anti, range, temporal, and many-to-many join patterns.
SQL Aggregations
Build reliable SQL metrics from aggregate functions through grain, fan-out, weighted ratios, rollups, percentiles, and approximate counts.
Open the interactive workspace and practice across SQL topics.