64. Найти отделы со средней зарплатой выше 90
Условие задачи:
Даны таблицы department и employee.
Необходимо вывести отделы, в которых средняя зарплата сотрудников больше 90.
Для каждого такого отдела нужно показать:
название отдела;
количество сотрудников.
Результат отсортировать по названию отдела в алфавитном порядке.
Код:
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_id.💡 Сгруппируй данные по отделу.
💡 Для количества сотрудников используй
COUNT().💡 Условие на среднюю зарплату после группировки задаётся через
HAVING.💡 Для алфавитной сортировки используй
ORDER BY d.name.Решение
SELECT
d.name AS department_name,
COUNT(e.id) AS employee_count
FROM department d
JOIN employee e
ON e.department_id = d.id
GROUP BY
d.id,
d.name
HAVING AVG(e.salary) > 90
ORDER BY d.name;
Сначала сотрудники соединяются со своими отделами:
JOIN employee e
ON e.department_id = d.id
Затем строки группируются по отделам:
GROUP BY
d.id,
d.name
Условие:
HAVING AVG(e.salary) > 90
оставляет только те отделы, где средняя зарплата сотрудников больше 90.
После этого:
ORDER BY d.name
сортирует результат по названию отдела в алфавитном порядке.
Обычный JOIN здесь подходит, поскольку отдел без сотрудников не имеет средней зарплаты и не может удовлетворить условию AVG(salary) > 90.