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

Find Departments Where Every Employee Clears the Pay Floor

Express “every employee earns above $60,000” with an existence guard and a correlated NOT EXISTS violation check.

  • Subqueries
  • Filtering
  • Sorting

Exercise brief

Understand the request

Compensation compliance lead A compliance report needs populated departments where no employee earns $60,000 or less.

Find departments where every employee earns strictly more than $60,000 — and which have at least one employee (so empty depts are excluded). Use EXISTS to require ≥1 employee AND NOT EXISTS to forbid any low earner. Return department_id, department_name — ordered by department_id.

Return

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

Constraints

  • Exclude empty departments explicitly.
  • Use NOT EXISTS to search for a counterexample at or below the pay floor.

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

'For ALL X in S, P(X)' = 'No X in S violates P'. Translate universal quantifiers into NOT EXISTS of the negation.

Hint 2

Add an EXISTS guard so empty departments (no employees at all) do NOT vacuously qualify.

Hint 3

Equivalent: `... AND NOT EXISTS (... salary <= 60000)` is exactly `MIN(salary) > 60000` over the dept.

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 NOT EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.department_id AND e.salary <= 60000) ORDER BY d.department_id;

Why this works

Universal quantification — 'every X has property P' — is encoded in SQL as 'no X violates P' via NOT EXISTS. The trick is forgetting the EXISTS guard: an empty subset trivially satisfies the universal, which is rarely what users want.

Success check

Only populated departments with zero violating employees appear.

Expected result

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

department_iddepartment_name
10IT
30Finance
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.