Отделы с числом сотрудников и сотрудники с окладом выше руководителя

32. Получить отделы с числом сотрудников и сотрудников с зарплатой выше руководителя

Условие задачи:
Даны таблицы department и employee.

Необходимо:

вывести все отделы в алфавитном порядке и количество сотрудников в каждом отделе;
вывести сотрудников, зарплата которых выше зарплаты их непосредственного руководителя.

Код:

CREATE TABLE department (
    id INTEGER NOT NULL,
    name VARCHAR(128) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE employee (
    id INTEGER NOT NULL,
    department_id INTEGER NOT NULL,
    manager_id INTEGER,
    name VARCHAR(128) NOT NULL,
    salary DECIMAL NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (department_id) REFERENCES department(id),
    FOREIGN KEY (manager_id) REFERENCES employee(id)
);

Спойлеры к решению

Подсказки

💡 Для подсчёта сотрудников соедини department и employee через LEFT JOIN.
💡 Используй COUNT(employee.id), чтобы отдел без сотрудников получил значение 0.
💡 Для сравнения сотрудника с руководителем сделай self join таблицы employee.
💡 Связь с руководителем задаётся через employee.manager_id = manager.id.

Решение

Отделы и количество сотрудников:

SELECT
    d.name AS department_name,
    COUNT(e.id) AS employee_count
FROM department d
LEFT JOIN employee e
    ON e.department_id = d.id
GROUP BY d.id, d.name
ORDER BY d.name;

LEFT JOIN позволяет вывести даже отделы, в которых нет сотрудников.

Сотрудники с зарплатой выше зарплаты руководителя:

SELECT
    e.name AS employee_name,
    e.salary AS employee_salary,
    m.name AS manager_name,
    m.salary AS manager_salary
FROM employee e
JOIN employee m
    ON e.manager_id = m.id
WHERE e.salary > m.salary;

Здесь таблица employee соединяется сама с собой:

e — сотрудник
m — его непосредственный руководитель

У сотрудников без руководителя manager_id равен NULL, поэтому они не попадут во второй результат.