Earning more than the boss

Compare each row with another row of the same table.

What to build

A self-join that finds every employee who earns more than their own manager.

Requirements

• Pair each employee with their manager, using two aliases for the same table.
• Keep only employees whose salary is strictly higher than their manager's.
• Return the employee's name and salary, and the manager's name and salary, sorted by the employee's 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,
  salary INTEGER NOT NULL,
  manager_id INTEGER REFERENCES employees(id)
);

Output

Columns employee, salary, manager and manager_salary, in alphabetical order of employee. Your result is compared with the expected one, column names included, so name each column exactly as asked (use AS).

Constraints

The top boss has no manager. Every other manager_id is another employee's id.

Example

When a test starts with:

INSERT INTO employees VALUES (1, 'Farhana', 90000, NULL), (2, 'Imran', 95000, 1), (3, 'Joya', 60000, 1), (4, 'Kamal', 99000, 2);

your SQL should print:

employee  salary  manager  manager_salary
--------  ------  -------  --------------
Imran     95000   Farhana  90000
Kamal     99000   Imran    95000
(2 rows)

Why it works

The two aliases let one query talk about two different rows of the same table at once. The top boss has no matching manager row, so an inner join leaves them out, which is what is wanted here.

Examples

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  salary INTEGER NOT NULL,
  manager_id INTEGER REFERENCES employees(id)
);
INSERT INTO employees VALUES (1, 'Farhana', 90000, NULL), (2, 'Imran', 95000, 1), (3, 'Joya', 60000, 1), (4, 'Kamal', 99000, 2);

employee  salary  manager  manager_salary
--------  ------  -------  --------------
Imran     95000   Farhana  90000
Kamal     99000   Imran    95000
(2 rows)

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  salary INTEGER NOT NULL,
  manager_id INTEGER REFERENCES employees(id)
);
INSERT INTO employees VALUES (1, 'Boss', 100, NULL), (2, 'Worker', 50, 1);

employee  salary  manager  manager_salary
--------  ------  -------  --------------
(0 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 AS employee, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON m.id = e.manager_id
WHERE e.salary > m.salary
ORDER BY employee;

Lesson: Joining tables →