SQL Subqueries SQL Topic exerciseMediumVerified answerSQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

Find Employees Above Their Department Average

Use a correlated scalar subquery that references the outer employee department.

  • Joins
  • Subqueries
  • Aggregation
  • Filtering
  • Sorting

Exercise brief

Understand the request

Compensation partner Managers need employees paid strictly above the average within their own department.

Find employees whose salary is strictly greater than the AVERAGE salary of their OWN department. The inner query references the outer row's department_id (correlated). Return employee_id, first_name, last_name, department_name, salary — ordered by department_name, salary DESC, employee_id.

Return

  • Return employee_id, first_name, last_name, department_name, salary in this exact left-to-right order.

Constraints

  • Correlate the inner employees alias to the outer department_id.
  • Do not compare with the company-wide average.

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

employees

  • employee_idINTEGER
  • first_nameVARCHAR(50)
  • last_nameVARCHAR(50)
  • salaryINTEGER
  • department_idINTEGER

departments

  • department_idINTEGER
  • department_nameVARCHAR(50)

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

Correlated = inner query references a column from the outer row (here: e.department_id).

Hint 2

Logically the inner query runs ONCE PER outer row; modern optimizers usually rewrite it as a join.

Hint 3

A window-function alternative: `AVG(salary) OVER (PARTITION BY department_id)` is often faster.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
SELECT e.employee_id, e.first_name, e.last_name, d.department_name, e.salary FROM employees e INNER JOIN departments d ON e.department_id = d.department_id WHERE e.salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department_id = e.department_id) ORDER BY d.department_name, e.salary DESC, e.employee_id;

Why this works

Correlated subqueries are how you compare a row to its own group. The pattern shows up everywhere — 'above-team-average performer', 'most expensive item per category', etc. Window functions are usually faster, but the correlated form is portable to ancient engines.

Success check

Each employee is compared only with peers in the same department and qualifying rows appear once.

Expected result

Use this output to verify values, aliases, ordering, and row count.

employee_idfirst_namelast_namedepartment_namesalary
104DavidBrownFinance70000
103CarolDavisHR60000
100JohnSmithIT120000
105EmmaTaylorMarketing95000

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Build the next SQL skill

SQL Practice Online →

Open the interactive workspace and practice across SQL topics.