Find Employees Above the Company Average
Use a scalar subquery in WHERE to return employee_id, first_name, last_name, and salary.
- Subqueries
- Aggregation
- Filtering
- Sorting
Exercise brief
Understand the request
Compensation analyst A pay review needs the employees whose salary is strictly above the current company-wide average.
Find every employee whose salary is strictly greater than the company-wide average salary. Use a scalar subquery in the WHERE clause. Return employee_id, first_name, last_name, salary — ordered by salary DESC, employee_id.
Return
- Return only the requested columns.
- Use the stated aliases and deterministic ORDER BY keys.
Constraints
- Calculate the average from employees inside a scalar subquery.
- Do not hard-code the average or qualifying employee IDs.
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)salaryINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
A scalar subquery returns exactly one row × one column. The optimizer evaluates it once.
Hint 2
Wrap the inner SELECT in parentheses — required syntax everywhere a single value is expected.
Hint 3
AVG(salary) ignores NULL salaries automatically (just like every other aggregate).
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 AVG(salary) FROM employees) ORDER BY salary DESC, employee_id;Why this works
Scalar subqueries are the simplest form: one value, used like a constant. Engines evaluate them once (uncorrelated), so performance is excellent. Compare a row to a global aggregate without writing the average twice.
Success check
Every salary strictly above the computed company average appears once in descending salary order.
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 |
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.