EMP и DEPT: SQL-задачи и поиск дублей в Python

Условие

-- Даны две таблицы 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 dept2 full join dept2 on dept2.deptno = dept1.deptno

Напишите код, который найдет список уникальных значений и список дубликатов для следующего списка значений: [56, 23, 67, 45, 67, 2, 47, 158, 31, 34, 78, 23, 78, 23, 89, 23, 36]

Ответ

Используйте оконное AVG для зарплаты, CASE для сортировки, FULL JOIN для сравнения версий подразделений и Counter для частот элементов списка.

Зарплата и сортировка

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. Предполагается уникальность dept.deptno.

Изменения подразделений

Пример для PostgreSQL: сравниваем dname и loc, остальные изменяемые поля при наличии добавляют в условие. Ключ deptno — NOT NULL.

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;

IS DISTINCT FROM корректно сравнивает и NULL.

Значения списка

from collections import Counter
values = [56, 23, 67, 45, 67, 2, 47, 158, 31, 34, 78, 23, 78, 23, 89, 23, 36]
counts = Counter(values)
distinct = list(counts)
duplicates = [x for x, count in counts.items() if count > 1]
singletons = [x for x, count in counts.items() if count == 1]

duplicates равен [23, 67, 78]. distinct содержит все разные значения; singletons — только встречающиеся один раз. Это два возможных смысла слова «уникальные».