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

Find Departments with an Above-Average Earner

Use correlated EXISTS for department membership and a scalar subquery for the company average.

  • Joins
  • Subqueries
  • Aggregation
  • Filtering
  • Sorting

Exercise brief

Understand the request

People analytics lead Leadership needs a department-level list where at least one employee earns above the company average.

Find every department that contains at least one employee earning more than the COMPANY-WIDE average salary. Use EXISTS with the company-avg as a scalar subquery. Return department_id, department_name — ordered by department_id.

Return

  • Return department_id, department_name in this exact left-to-right order.

Constraints

  • Use EXISTS rather than a join followed by DISTINCT.
  • Return one row per qualifying department.

Data you will use

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

departments

  • department_idINTEGER
  • department_nameVARCHAR(50)

employees

  • employee_idINTEGER
  • salaryINTEGER
  • department_idINTEGER

Hints, when you need them

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

Hint 1

EXISTS short-circuits — finding ONE match is enough.

Hint 2

Equivalent JOIN+DISTINCT works but is generally slower (full join, then dedupe).

Hint 3

The inner scalar subquery (AVG) is uncorrelated — engine evaluates it once.

Verified SQL answer

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

Reveal solution and explanation
SELECT d.department_id, d.department_name FROM departments d WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.department_id AND e.salary > (SELECT AVG(salary) FROM employees)) ORDER BY d.department_id;

Why this works

Correlated EXISTS is the canonical 'does at least one X satisfy Y' filter. It outperforms JOIN+DISTINCT in most engines because the executor can stop after the first match per outer row.

Success check

A department appears once when at least one correlated employee clears the computed company average.

Expected result

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

department_iddepartment_name
10IT
40Marketing

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.