CREATE TABLE Department(id integer, name text);
CREATE TABLE Personal(id integer, id_head integer, id_dep integer, name text, sal integer);
INSERT INTO Department VALUES(1, 'Финансы');
INSERT INTO Department VALUES(2, 'Риски');
INSERT INTO Department VALUES(3, 'Розница');
INSERT INTO Department VALUES(4, 'Безопасность');
INSERT INTO Department VALUES(1000, 'ДКК');
INSERT INTO Personal VALUES(1, 1, 2, 'Бегинс', 45000);
INSERT INTO Personal VALUES(2, 1, 2, 'Поттер', 80000);
INSERT INTO Personal VALUES(3, 2, 2, 'Чапаева', 100000);
INSERT INTO Personal VALUES(4, 4, 4, 'Шилов', 65000);
INSERT INTO Personal VALUES(10000, 5, 3, 'Наумов', 64500);
SELECT
CASE
WHEN RIGHT(Personal.name, 1) = 'а' THEN Department.name || ' | г-жа ' || Personal.name
ELSE Department.name || ' | г-н ' || Personal.name
END
AS name_and_department
FROM Personal
INNER JOIN Department ON Personal.id_dep = Department.id;
SELECT *
FROM Personal
ORDER BY (
SELECT SUM(
CASE
WHEN LOWER(cur_char) IN ('а', 'е', 'ё', 'и', 'о', 'у', 'ы', 'э', 'ю', 'я') THEN 1
ELSE 0
END
)
FROM unnest(string_to_array(Personal.name, null)) AS cur_char
) ASC;
WITH DepartmentsMaxSalaryCTE AS (
SELECT Department.id AS dep_id, MAX(Personal.sal) AS max_sal
FROM Personal INNER JOIN Department ON Department.id = Personal.id_dep
GROUP BY Department.id
)
SELECT Personal.name, Department.name, Personal.sal
FROM Personal
INNER JOIN Department ON Department.id = Personal.id_dep
INNER JOIN DepartmentsMaxSalaryCTE ON Personal.id_dep = DepartmentsMaxSalaryCTE.dep_id
WHERE Personal.id_dep = DepartmentsMaxSalaryCTE.dep_id AND Personal.sal = DepartmentsMaxSalaryCTE.max_sal
Telegram: DB developers channel