SQL: зарплаты сотрудников и изменения справочника DEPT

Условие

-- Даны две таблицы EMP - список сотрудников и зарплат, DEPT - список подразделений -- 1) Написать запрос, возвращающий имя (ENAME), подразделение (DNAME), должность (JOB) -- и на сколько зарплата работника (SAL) отличается от средней для его должности. -- 2) Отсортировать полученный результат так, чтобы сотрудник с именем JAMES был на первой строчке, -- FORD на последней, а все остальные сотрудники отсортированы по имени в обратном алфавитном порядке.

select em.ename, d.dname, em.job, em.sal - avg(e.sal) over(partition by e.job) as avg_salary

from emp em join dept d on d.deptno = em.deptno order by case when em.name in ('JAMES') then 0 else 1 end, case when em.name in ('FORD') then 1 else 0 end, --em.name desc

Задание 2

-- Даны таблицы подразделений DEPT и DEPT2 с одинаковыми данными и уникальным ключем (DEPTNO). -- В таблице DEPT2 удалили, изменили и добавили несколько строк. -- Необходимо получить в одном запросе все номера подразделений (DEPTNO), по которым были изменения. -- Вывести номер подразделения и код операции ('I' - добавили, 'D' - удалили, 'U' - изменили).

select * from dept1 union all select from dept2

select dept1.deptno from dept1

select * from dept1 join dept2 on dept2.deptno = dept1.deptno where not exist (select deptno from dept2)

Ответ

Для отклонения зарплаты используйте AVG по должности, для особой сортировки — CASE. Различия DEPT и DEPT2 выявляются FULL JOIN и сравнением атрибутов с учётом NULL.

Сотрудники

SELECT em.ename, d.dname, em.job,
       em.sal - AVG(em.sal) OVER (PARTITION BY em.job) AS salary_diff
FROM emp em
JOIN dept d ON d.deptno = em.deptno
ORDER BY CASE em.ename WHEN 'JAMES' THEN 0 WHEN 'FORD' THEN 2 ELSE 1 END,
         em.ename DESC;

В исходнике нужно исправить e на em, а name на ename. Ключ подразделения предполагается уникальным.

Изменения справочника

Вариант PostgreSQL для непустого уникального deptno и изменяемых полей dname, loc:

SELECT COALESCE(n.deptno, o.deptno) AS deptno,
       CASE WHEN o.deptno IS NULL THEN 'I'
            WHEN n.deptno IS NULL THEN 'D'
            ELSE 'U' END AS operation
FROM dept o
FULL JOIN dept2 n ON n.deptno = o.deptno
WHERE o.deptno IS NULL OR n.deptno IS NULL
   OR o.dname IS DISTINCT FROM n.dname
   OR o.loc IS DISTINCT FROM n.loc;

Добавьте сравнение остальных атрибутов, если они есть. UNION ALL лишь объединяет строки и сам по себе не определяет тип изменения.