Write a query to find employees earning more than the company average salary?
Interview preparation resource from Gate Smashers.
Compute the company average with a scalar subquery and return employees whose salary exceeds it: SELECT employee_name, salary FROM Employees WHERE salary > ( SELECT AVG(salary) FROM Employees );
| employee_id | employee_name | department | salary | manager_id |
|---|---|---|---|---|
| 101 | Aarav | Engineering | 95000 | 105 |
| 102 | Meera | Engineering | 72000 | 105 |
| 103 | Kabir | Sales | 65000 | 106 |
| 104 | Isha | Sales | 92000 | 106 |
| 105 | Rohan | Engineering | 90000 | NULL |
| 106 | Neha | Sales | 88000 | NULL |
SELECT employee_name, salary
FROM Employees
WHERE salary > (
SELECT AVG(salary)
FROM Employees
);Direct answer (query)
Use a scalar subquery to calculate the average salary for the whole table, then filter employees whose salary is greater than that average. The exact query is provided in the code block below.
How it works
The query uses an uncorrelated scalar subquery that returns a single AVG(salary) value for the entire Employees table. The outer WHERE clause compares each employee's salary to that single average value and returns only those exceeding it.
- Subquery: Returns one AVG(salary) value for the complete table.
- Outer filter: Compares each employee's salary with the subquery result and keeps those greater than the average.
NULL salary behavior
AVG ignores NULL salaries by default. If your business rules require treating NULL as zero or handling them differently, explicitly apply COALESCE or other logic before averaging; otherwise, do not assume NULL represents zero.
Sample data reference
A sample Employees table is provided below to illustrate the dataset the query runs against. The code block contains the exact SQL statement shown earlier.