task.sql

task.sql

--- 1
SELECT d.name AS department_name,
       e.name AS employee_name
FROM public.table_department AS d
JOIN public.table_employee AS e ON e.department_id = d.id
JOIN (
    SELECT department_id, MAX(id) AS max_id
    FROM public.table_employee
    GROUP BY department_id
) employee_max
    ON employee_max.department_id = e.department_id
    AND employee_max.max_id = e.id;

--- 2
SELECT d.name AS department_name
FROM public.table_department AS d
WHERE NOT EXISTS (
    SELECT 1
    FROM public.table_employee AS e
    WHERE e.department_id = d.id
      AND LOWER(e.name) LIKE '%z%'
      AND LOWER(e.name) LIKE '%w%'
);

--- 3
SELECT name AS employee_name,
       next_name,
       next_salary
FROM (
    SELECT e.*,
           LEAD(name)   OVER (PARTITION BY department_id ORDER BY salary ASC, id DESC) AS next_name,
           LEAD(salary) OVER (PARTITION BY department_id ORDER BY salary ASC, id DESC) AS next_salary
    FROM public.table_employee AS e
) t
WHERE next_name IS NOT NULL --- при желании можно добавить\убрать прочий критерий
  AND next_salary IS NOT NULL;

--- 4
SELECT
d.NAME AS department_name,
COALESCE(SUM(e.SALARY), 0) AS total_salary,  --- в случае если нет сотрудников в департаменте
SUM(COALESCE(SUM(e.SALARY), 0)) OVER (ORDER BY COALESCE(SUM(e.SALARY), 0)) AS cumulative_salary
FROM public.table_department AS d
    LEFT JOIN public.table_employee AS e ON d.ID = e.DEPARTMENT_ID
GROUP BY d.ID, d.NAME
ORDER BY total_salary;

--- 5
WITH dept_stats AS (
    SELECT department_id,
           SUM(salary) AS total_salary,
           COUNT(*) AS emp_count
    FROM public.table_employee
    GROUP BY department_id
),
target AS (
    SELECT MAX(total_salary * 1.0 / emp_count) AS target_avg  --- максимальная средняя зарплата по всем департаментам
    FROM dept_stats
)
INSERT INTO public.table_employee (department_id, name, salary)
       SELECT d.department_id,
              'Новый сотрудник' AS name,
              CAST(t.target_avg * (d.emp_count + 1) - d.total_salary AS DECIMAL(10,2)) AS salary  --- вычисляем зарплату, которую нужно назначить новому сотруднику, чтобы средняя в этом отделе стала равна t.target_avg
       FROM dept_stats d
           CROSS JOIN target t
       WHERE (
           t.target_avg * (d.emp_count + 1) - d.total_salary
           ) > 0;

--- 6
--- Для какой конкретно СУБД надо написать запросы - не указано в задании
DELETE FROM public.table_employee e1
       USING public.table_employee e2 --- pg надо USING если DELETE c JOIN используем
       WHERE e1.DEPARTMENT_ID = e2.DEPARTMENT_ID
         AND e1.CHIEF_ID = e2.CHIEF_ID
         AND e1.NAME = e2.NAME
         AND e1.SALARY = e2.SALARY
         AND e1.ID > e2.ID;

--- вариант для MySQL с подзапросом
DELETE FROM public.table_employee
WHERE ID IN (
SELECT e1.ID
FROM public.table_employee e1
INNER JOIN public.table_employee e2 ON (
    e1.DEPARTMENT_ID = e2.DEPARTMENT_ID
        AND e1.CHIEF_ID = e2.CHIEF_ID
        AND e1.NAME = e2.NAME
        AND e1.SALARY = e2.SALARY
        AND e1.ID > e2.ID
    )
);

--- 7
--- Здесь необходимо использовать рекурсивное СТЕ
WITH RECURSIVE subordinate_hierarchy AS (
SELECT id, department_id, chief_id, name, salary
FROM public.table_employee
WHERE id = 1
UNION ALL
SELECT e.id, e.department_id, e.chief_id, e.name, e.salary
FROM public.table_employee e
    INNER JOIN subordinate_hierarchy sh ON e.chief_id = sh.id
)
UPDATE public.table_employee
SET department_id = 3
WHERE id IN (
SELECT id FROM subordinate_hierarchy
);