SQLize | PHPize | SQLtest

CREATE TABLE dep_n1 (id serial primary key); INSERT INTO dep_n1 VALUES (1), (2); CREATE TABLE post_1 (id serial primary key); CREATE TABLE emp_1 ( id serial, manager_id int, name varchar, dep_id int, post_id int, date_work_begin date, salary int ); INSERT INTO emp_1 VALUES (9, null, 'СОБОЛЕВ', null, null, '2021-01-01', 10000), (10, 9, 'CTO', 1, null, '2020-01-01', 7500), (14, 9, 'CSO', 2, null, '2020-01-01', 7500), (11, 10, 'Dev 1', 1, null, '1990-01-01', 6000), (20, 10, 'Dev 2', 1, null, '1991-01-01', 8000); SELECT '1. Создать таблицы emp_2 и emp_3, используя данные из таблицы emp_1.' task; CREATE TABLE emp_2 AS (SELECT * FROM emp_1); CREATE TABLE emp_3 AS (SELECT * FROM emp_1); SELECT '1.1. Установить первичный и внешние ключи.' task; ALTER TABLE emp_2 ADD PRIMARY KEY(id); ALTER TABLE emp_3 ADD PRIMARY KEY(id); ALTER TABLE emp_2 ADD CONSTRAINT fk_dep_id FOREIGN KEY(dep_id) REFERENCES dep_n1(id) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE emp_2 ADD CONSTRAINT fk_post_id FOREIGN KEY(post_id) REFERENCES post_1(id) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE emp_3 ADD CONSTRAINT fk_dep_id FOREIGN KEY(dep_id) REFERENCES dep_n1(id) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE emp_3 ADD CONSTRAINT fk_post_id FOREIGN KEY(post_id) REFERENCES post_1(id) ON DELETE CASCADE ON UPDATE CASCADE; SELECT '2. Из таблицы emp_2 удалить сотрудников, код которых заканчивается на на 0, а из таблицы emp_3 тех, кто был принят на работу до 1993 года.' task; DELETE FROM emp_2 WHERE CAST(id AS text) LIKE '%0'; DELETE FROM emp_3 WHERE date_work_begin < '1993-01-01'; SELECT '3. Используя данные таблиц emp_1, emp_2, emp_3 и инструкции сочетания запросов вывести список удаленных в п.2 записей 2-мя вариантами построения запроса.' task; SELECT * FROM emp_1 EXCEPT ( SELECT * FROM emp_2 INTERSECT SELECT * FROM emp_3 ); (SELECT * FROM emp_1 EXCEPT SELECT * FROM emp_2) UNION (SELECT * FROM emp_1 EXCEPT SELECT * FROM emp_3); SELECT '4. Используя данные таблиц emp_1, emp_2, emp_3 и инструкции сочетания запросов вывести только одинаковые записи, которые были удалены из таблиц emp_2 и emp_3 (если такие записи были) также 2-мя вариантами построения запроса.' task; (SELECT * FROM emp_1 EXCEPT SELECT * FROM emp_2) INTERSECT (SELECT * FROM emp_1 EXCEPT SELECT * FROM emp_3); SELECT * FROM emp_1 EXCEPT (SELECT * FROM emp_2 UNION SELECT * FROM emp_3); SELECT '5. Вывести список сотрудников из таблицы emp_1, получающих заработную плату большую чем у непосредственного руководителя. (без использования подзапросов, инструкции WITH и оконных функций)' task; SELECT emp_1.* FROM emp_1 JOIN emp_1 manager ON manager.id = emp_1.manager_id WHERE manager.salary < emp_1.salary; SELECT '6. Вывести все пары сотрудников, имеющих одинаковый оклад.' task; SELECT e1.name, e2.name, e1.salary FROM emp_1 e1 JOIN emp_1 e2 ON e1.salary = e2.salary AND e1.id != e2.id; SELECT '7. Составьте список всех сотрудников, зарабатывающих больше среднего оклада по отделу, в котором они работают. (подзапрос используется только в секции JOIN)' task; SELECT * FROM emp_1 JOIN ( SELECT dep_id, AVG(salary) salary FROM emp_1 GROUP BY dep_id ) average ON emp_1.dep_id = average.dep_id WHERE emp_1.salary > average.salary; SELECT '8. Выберите сведения о сотрудниках, имеющих оклады, начиная со 2-го ранга по окладу и кончая 5-ым. Ранг 1 имеет сотрудник или сотрудники с наибольшим окладом, то есть находящиеся на первом месте по величине оклада. Данные должны быть упорядочены по рангу оклада и по названию отдела в возрастающем порядке.' task; WITH ranks AS ( SELECT * , RANK() OVER (ORDER BY salary DESC) FROM emp_1 ) SELECT * FROM ranks WHERE rank BETWEEN 2 AND 5; SELECT '9. Отобразите фамилии всех сотрудников, находящихся в подчинении директора по фамилии СОБОЛЕВ. С' task; WITH RECURSIVE h AS ( SELECT id, name, 0 l FROM emp_1 WHERE name = 'СОБОЛЕВ' UNION ALL SELECT emp_1.id, emp_1.name, l+1 l FROM emp_1 JOIN h ON manager_id = h.id ) SELECT lpad('', l * 2, '.') || name FROM h; SELECT '10.Предыдущий запрос постройте таким образом, чтобы сначала отображалась фамилия директора, затем фамилии сотрудников второго уровня подчинения, за ними – третьего уровня и т.д.' task; WITH RECURSIVE h AS ( SELECT id, name, 0 l FROM emp_1 WHERE name = 'СОБОЛЕВ' UNION ALL SELECT emp_1.id, emp_1.name, l+1 l FROM emp_1 JOIN h ON manager_id = h.id ) SELECT lpad('', l * 2, '.') || STRING_AGG(name, ', ' ORDER BY name) FROM h GROUP BY l; SELECT '11.Выберите не более 3-х различных окладов среди самых новых сотрудников (используйте столбец ПРИНЯТ НА РАБОТУ), анализируя не более 5 сотрудников.' task; WITH RECURSIVE s AS ( SELECT salary, 0 l FROM emp_1 -- ORDER BY date_work_begin DESC LIMIT 1 UNION ALL SELECT emp_1.salary, l+1 l FROM emp_1 JOIN s ON emp_1.salary != s.salary WHERE l < 5 ) SELECT * FROM s;