CREATE TABLE employees ( employee_id serial PRIMARY KEY, first_name varchar(20) NOT NULL, last_name varchar(25) NOT NULL,
job_id varchar(10) NOT NULL REFERENCES jobs (job_id), salary integer,
manager_id integer,
department_id integer NOT NULL REFERENCES departments (department_id)
);
-- Заполнение таблицы employees
INSERT INTO employees (first_name, last_name, job_id, salary, manager_id, department_id)
VALUES
('Steven', 'King', 'AD_PRES', 24000, NULL, 90), ('Neena', 'Kochhar', 'AD_VP', 17000, 1, 90), ('Lex', 'De Haan', 'AD_VP', 17000, 1, 90), ('Alexander', 'Hunold', 'IT_PROG', 9000, 3, 60), ('Bruce', 'Ernst', 'IT_PROG', 6000, 4, 60), ('David', 'Austin', 'IT_PROG', 4800, 4, 60), ('Valli', 'Pataballa', 'IT_PROG', 4800, 4, 60), ('Diana', 'Lorentz', 'IT_PROG', 4200, 4, 60), ('Nancy', 'Greenberg', 'FI_MGR', 12008, 2, 100), ('Daniel', 'Faviet', 'FI_ACCOUNT', 9000, 9, 100), ('John', 'Chen', 'FI_ACCOUNT', 8200, 9, 100), ('Ismael', 'Sciarra', 'FI_ACCOUNT', 7700, 9, 100), ('Jose Manuel', 'Urman', 'FI_ACCOUNT', 7800, 9,
100),
('Luis', 'Popp', 'FI_ACCOUNT', 6900, 9, 100),
11
('Den', 'Raphaely', 'PU_MAN', 11000, 1, 30), ('Alexander', 'Khoo', 'PU_CLERK', 3100, 15, 30), ('Shelli', 'Baida', 'PU_CLERK', 2900, 15, 30), ('Sigal', 'Tobias', 'PU_CLERK', 2800, 15, 30), ('Guy', 'Himuro', 'PU_CLERK', 2600, 15, 30), ('Karen', 'Colmenares', 'PU_CLERK', 2500, 15, 30), ('Matthew', 'Weiss', 'ST_MAN', 8000, 1, 50), ('Adam', 'Fripp', 'ST_MAN', 8200, 1, 50), ('Payam', 'Kaufling', 'ST_MAN', 7900, 1, 50), ('Shanta', 'Vollman', 'ST_MAN', 6500, 1, 50), ('Kevin', 'Mourgos', 'ST_MAN', 5800, 1, 50), ('Julia', 'Nayer', 'ST_CLERK', 3200, 21, 50), ('Irene', 'Mikkilineni', 'ST_CLERK', 2700, 21, 50), ('James', 'Landry', 'ST_CLERK', 2400, 21, 50), ('Steven', 'Markle', 'ST_CLERK', 2200, 21, 50), ('Laura', 'Bissot', 'ST_CLERK', 3300, 22, 50), ('Mozhe', 'Atkinson', 'ST_CLERK', 2800, 22, 50), ('James', 'Marlow', 'ST_CLERK', 2500, 22, 50), ('TJ', 'Olson', 'ST_CLERK', 2100, 22, 50), ('Jason', 'Mallin', 'ST_CLERK', 3300, 23, 50), ('Michael', 'Rogers', 'ST_CLERK', 2900, 23, 50), ('Ki', 'Gee', 'ST_CLERK', 2400, 23, 50),
('Hazel', 'Philtanker', 'ST_CLERK', 2200, 23, 50), ('Renske', 'Ladwig', 'ST_CLERK', 3600, 24, 50), ('Stephen', 'Stiles', 'ST_CLERK', 3200, 24, 50), ('John', 'Seo', 'ST_CLERK', 2700, 24, 50), ('Joshua', 'Patel', 'ST_CLERK', 2500, 24, 50), ('Trenna', 'Rajs', 'ST_CLERK', 3500, 25, 50), ('Curtis', 'Davies', 'ST_CLERK', 3100, 25, 50), ('Randall', 'Matos', 'ST_CLERK', 2600, 25, 50),
12
('Peter', 'Vargas', 'ST_CLERK', 2500, 25, 50), ('John', 'Russell', 'SA_MAN', 14000, 1, 80), ('Karen', 'Partners', 'SA_MAN', 13500, 1, 80), ('Alberto', 'Errazuriz', 'SA_MAN', 12000, 1, 80), ('Gerald', 'Cambrault', 'SA_MAN', 11000, 1, 80), ('Eleni', 'Zlotkey', 'SA_MAN', 10500, 1, 80), ('Jennifer', 'Whalen', 'AD_ASST', 4400, 2, 10), ('Michael', 'Hartstein', 'MK_MAN', 13000, 1, 20), ('Susan', 'Mavris', 'HR_REP', 6500, 2, 40);
--Добавление внешнего ключа таблицы departments ALTER TABLE departments
ADD CONSTRAINT mgr_emp_fkey FOREIGN KEY (manager_id) REFERENCES employees (employee_id);
--Запрос Найти сотрудника, фамилия которого состоит более чем из одного слова
select *
from employees e
where e.first_name like '% %';
-- Запрос Найти минимальные зарплаты по отделам, но вывести только те, которые больше 10000
select d.department_name, min(e.salary) from jobs j
join employees e using (job_id)
join departments d using (department_id) where
e.salary > 10000 group by d.department_id;
13
--Запрос «Бонусный» найти количество менеджеров,
менеджеров отделов и остальных сотрудников.
select
'Dpt_managers' EMP
,count(distinct d.manager_id) CNT from departments d
union all select
'Managers' EMP
,count(distinct e.manager_id) CNT from employees e
where e.manager_id not in (select distinct d.manager_id from departments d)
union all select
'Workers' EMP
,count(e.employee_id) CNT
from employees e
where e.employee_id not in
(select distinct d.manager_id id from departments d where d.manager_id is not null
union
select distinct e.manager_id id from employees e where e.manager_id is not null);
14