SQLize Online / PHPize Online  /  SQLtest Online

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