Above their department's average

Find the employees who earn more than the average salary of their own department.

What to build

A query that compares each employee with the average of their own department.

Requirements

• Use a correlated subquery that works out the average of the current employee's department.
• Keep the employees whose salary is strictly above that average.
• Sort by department, then salary from highest, then name.

Tables

Each test fills these tables with its own rows, then runs your SQL:

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL,
  salary INTEGER NOT NULL
);

Output

Columns name, dept and salary, sorted by dept (alphabetically), then by salary from highest to lowest, then by name. Your result is compared with the expected one, column names included, so name each column exactly as asked (use AS).

Constraints

The table may have any number of employees, in any number of departments.

Example

When a test starts with:

INSERT INTO employees VALUES
  (1, 'Farhana', 'Sales', 40000),
  (2, 'Imran', 'IT', 65000),
  (3, 'Joya', 'Sales', 38000),
  (4, 'Kamal', 'IT', 72000),
  (5, 'Lima', 'HR', 45000);

your SQL should print:

name     dept   salary
-------  -----  ------
Kamal    IT     72000
Farhana  Sales  40000
(2 rows)

Why it works

The subquery mentions the outer row's department, so it is worked out again for each employee, giving the right average every time.

Examples

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL,
  salary INTEGER NOT NULL
);
INSERT INTO employees VALUES
  (1, 'Farhana', 'Sales', 40000),
  (2, 'Imran', 'IT', 65000),
  (3, 'Joya', 'Sales', 38000),
  (4, 'Kamal', 'IT', 72000),
  (5, 'Lima', 'HR', 45000);

name     dept   salary
-------  -----  ------
Kamal    IT     72000
Farhana  Sales  40000
(2 rows)

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL,
  salary INTEGER NOT NULL
);
INSERT INTO employees VALUES
  (1, 'Mitu', 'IT', 50000),
  (2, 'Nayeem', 'IT', 50000),
  (3, 'Orin', 'IT', 51000),
  (4, 'Pavel', 'Sales', 90000),
  (5, 'Ruma', 'Sales', 10000);

name   dept   salary
-----  -----  ------
Orin   IT     51000
Pavel  Sales  90000
(2 rows)

Each test makes its own tables, runs your SQL on them, and compares what your queries return. Changes never carry over from one test to the next.

Hints

Solution

SELECT e.name, e.dept, e.salary
FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE dept = e.dept)
ORDER BY e.dept, e.salary DESC, e.name;

Lesson: Subqueries and WITH →