SQLize | PHPize | SQLtest

-- Создание таблицы DEPARTMENT CREATE TABLE department ( ID NUMBER PRIMARY KEY, name VARCHAR2(100) ); -- Вставка данных INSERT INTO department (id, name) VALUES (1, 'pos'); INSERT INTO department (id, name) VALUES (2, 'auto'); INSERT INTO department (id, name) VALUES (3, 'card'); -- Создание таблицы EMPLOYEE CREATE TABLE employee ( ID NUMBER PRIMARY KEY, DEPARTMENT_ID NUMBER, CHIEF_ID NUMBER, NAME VARCHAR2(100), SALARY NUMBER, FOREIGN KEY (DEPARTMENT_ID) REFERENCES department(ID), FOREIGN KEY (CHIEF_ID) REFERENCES employee(ID) ); -- Вставка данных INSERT INTO employee (ID, DEPARTMENT_ID, CHIEF_ID, NAME, SALARY) VALUES (1, 1, NULL, 'John', 150000); INSERT INTO employee (ID, DEPARTMENT_ID, CHIEF_ID, NAME, SALARY) VALUES (2, 2, NULL, 'Max', 145000); INSERT INTO employee (ID, DEPARTMENT_ID, CHIEF_ID, NAME, SALARY) VALUES (3, 3, NULL, 'Bob', 160000); INSERT INTO employee (ID, DEPARTMENT_ID, CHIEF_ID, NAME, SALARY) VALUES (4, 1, 1, 'Tom', 60000); INSERT INTO employee (ID, DEPARTMENT_ID, CHIEF_ID, NAME, SALARY) VALUES (5, 2, 2, 'Sam', 60000); INSERT INTO employee (ID, DEPARTMENT_ID, CHIEF_ID, NAME, SALARY) VALUES (6, 3, 3, 'Fedor', 50000); INSERT INTO employee (ID, DEPARTMENT_ID, CHIEF_ID, NAME, SALARY) VALUES (7, 1, 1, 'Sasha', 60000); INSERT INTO employee (ID, DEPARTMENT_ID, CHIEF_ID, NAME, SALARY) VALUES (8, 2, 2, 'Alex', 40000); INSERT INTO employee (ID, DEPARTMENT_ID, CHIEF_ID, NAME, SALARY) VALUES (9, 3, 3, 'Doka', 45000); INSERT INTO employee (ID, DEPARTMENT_ID, CHIEF_ID, NAME, SALARY) VALUES (10, 3, 3, 'Doka', 45000); INSERT INTO employee (ID, DEPARTMENT_ID, CHIEF_ID, NAME, SALARY) VALUES (11, 1, 1, 'Zoro', 50000); INSERT INTO employee (ID, DEPARTMENT_ID, CHIEF_ID, NAME, SALARY) VALUES (12, 2, 2, 'Awon', 35000); INSERT INTO employee (ID, DEPARTMENT_ID, CHIEF_ID, NAME, SALARY) VALUES (13, 3, 3, 'Zow', 70000); INSERT INTO employee (ID, DEPARTMENT_ID, CHIEF_ID, NAME, SALARY) VALUES (14, 3, 3, 'Zow', 70000); INSERT INTO employee (ID, DEPARTMENT_ID, CHIEF_ID, NAME, SALARY) VALUES (15, 1, 1, 'Sasha', 60000); INSERT INTO employee (ID, DEPARTMENT_ID, CHIEF_ID, NAME, SALARY) VALUES (16, 2, 2, 'Sam', 60000); -- Выборка данных SELECT * FROM department; SELECT * FROM employee; --Задание 1 SELECT department_name, name FROM ( SELECT a.department_id, b.name as department_name, a.id, a.name, ROW_NUMBER() OVER (PARTITION BY a.department_id order by a.id DESC) as MARK FROM employee a LEFT JOIN department b ON a.department_id = b.id ) where mark = 1 ; --Задание 2 SELECT department_name FROM ( SELECT department_name, sum(ZW_FLG) as zw_sum_flg FROM ( SELECT b.name as department_name, CASE WHEN lower(a.name) LIKE '%z%' and lower(a.name) LIKE '%w%' then 1 else 0 end as ZW_FLG FROM employee a LEFT JOIN department b ON a.department_id = b.id ) GROUP BY DEPARTMENT_NAME ) WHERE zw_sum_flg = 0 ; --Задание 3 WITH A AS ( SELECT id, name, salary, department_id, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary, id DESC) AS MARK FROM employee ) SELECT a.name, b.name AS next_name, b.salary AS next_salary FROM A LEFT JOIN A B ON a.department_id = b.department_id AND a.MARK + 1 = b.MARK ORDER BY a.name ; --Задание 4 SELECT name, total, SUM(total) OVER (ORDER BY TOTAL) as cum_total FROM ( SELECT b.name, SUM(salary) as total FROM employee a LEFT JOIN department b ON a.department_id = b.id GROUP BY b.name ) ; --Задание 6 DELETE FROM employee WHERE (name, chief_id, salary, department_id, id) NOT IN ( SELECT name, chief_id, salary, department_id, min(id) FROM employee GROUP BY name, chief_id, salary, department_id ); SELECT * FROM employee; --Задание 5 WITH A AS (SELECT department_id, AVG(salary) as average FROM employee GROUP BY department_id) SELECT * FROM A SELECT MAX(average) from A