Task Livecoding SQL Departments Employees Aggregation

58. Количество сотрудников по отделам и отделы с >3 сотрудниками с ЗП > 100к #

Условие задачи:
📌 Даны таблицы departments и employees. Нужно:

  1. Написать SQL-запрос, который посчитает количество работников в каждом подразделении.

  2. Написать SQL-запрос, который найдёт отделы, где работает больше трёх сотрудников с зарплатой более 100000.

Код (схема и данные):

CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department_id INT REFERENCES departments(id),
    salary INT NOT NULL
);

-- Заполнение таблицы отделов
INSERT INTO departments (id, name) VALUES
(1, 'Отдел ИТ'),
(2, 'Отдел ИБ'),
(3, 'HR');

-- Заполнение таблицы сотрудников
INSERT INTO employees (id, name, department_id, salary) VALUES
(1, 'Иван Петров', 1, 120000),
(2, 'Елена Сидорова', 1, 110000),
(3, 'Алексей Иванов', 1, 105000),
(4, 'Мария Кузнецова', 1, 95000),
(5, 'Дмитрий Смирнов', 2, 150000),
(6, 'Ольга Васильева', 2, 130000),
(7, 'Сергей Миронов', 2, 80000),
(8, 'Анна Жукова', 3, 115000),
(9, 'Павел Белов', 3, 125000),
(10, 'Наталья Воробьева', 3, 140000),
(11, 'Артем Громов', 3, 98000),
(12, 'Виктория Орлова', 1, 108000);
Спойлеры к решению
Подсказки

💡 Для задачи 1:

  • Нужно сгруппировать сотрудников по отделам: GROUP BY department_id или по departments.id.

  • Если нужно показать отделы без сотрудников — используй LEFT JOIN.

💡 Для задачи 2:

  • Сначала отфильтровать сотрудников с salary > 100000.

  • Потом сгруппировать по отделам.

  • И через HAVING COUNT(*) > 3 оставить только отделы, где таких сотрудников больше трёх.

Решение

1. Количество работников в каждом подразделении #

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

LEFT JOIN гарантирует, что отделы без сотрудников тоже попадут в результат (с employee_count = 0).

2. Отделы, где работает больше трёх сотрудников с зарплатой > 100000 #

SELECT 
    d.id   AS department_id,
    d.name AS department_name,
    COUNT(e.id) AS high_paid_count
FROM departments d
JOIN employees e
    ON e.department_id = d.id
WHERE e.salary > 100000
GROUP BY d.id, d.name
HAVING COUNT(e.id) > 3;

  • WHERE e.salary > 100000 — оставляем только сотрудников с ЗП > 100к.

  • GROUP BY d.id, d.name — группируем их по отделам.

  • HAVING COUNT(e.id) > 3 — оставляем только те отделы, где строго больше трёх таких сотрудников.

Edit this page