ALTER TABLE Employee MODIFY (EMP_ID NULL)

ALTER TABLE department ADD CONSTRAINT dep_chief_dep_id_FK Foreign KEY (dep_chief_dep_id) references department (dep_id)

ALTER TABLE employee ADD CONSTRAINT dep_id_FK Foreign KEY (emp_dep_id) references department (dep_id)

ALTER TABLE department ADD CONSTRAINT emp_manager_id_FK Foreign KEY (dep_manager_id) references employee (emp_id)

ALTER TABLE employee ADD CONSTRAINT salary_ck1 CHECK (EMP_SALARY > 14000)

DESC employee
EMP_ID, EMP_FIRST_NAME, EMP_LAST_NAME,  EMP_SALARY, EMP_POSITION, EMP_BIRTH_DATE, EMP_JOIN_DATE, EMP_DEP_ID
DESC department

CREATE TABLE department
(dep_id NUMBER PRIMARY KEY,
dep_name VARCHAR2(50) NOT NULL,
dep_manager_id NUMBER,
dep_chief_dep_id NUMBER
references department (dep_id)
)
SELECT constraint_name, table_name FROM user_constraints WHERE table_name = 'EMPLOYEE' OR table_name = 'DEPARTMENT'

CREATE TABLE Employee
(
emp_id NUMBER PRIMARY KEY,
emp_first_name VARCHAR2(50) NOT NULL,
emp_last_name VARCHAR2(50) NOT NULL,
emp_salary NUMBER,
emp_position VARCHAR2(50),
emp_birth_date DATE NOT NULL,
emp_join_date DATE,
emp_dep_id NUMBER,
CONSTRAINT salary_ck1
CHECK (emp_salary>14000)
)


CREATE TABLE Project
(
prj_id NUMBER PRIMARY KEY,
prj_name VARCHAR2(50) NOT NULL,
prj_start_date DATE,
prj_finish_date DATE,
prj_manager_id NUMBER
references Employee (emp_id)
)
ALTER TABLE Project MODIFY (PRJ_NAME VARCHAR2(50))

CREATE TABLE rel_prj_emp
(
rel_emp_id NUMBER references Employee (emp_id),
rel_prj_id NUMBER references Project (prj_id),
PRIMARY KEY (rel_emp_id, rel_prj_id)
)

DESC rel_prj_emp
DESC project
PRJ_ID, PRJ_NAME, PRJ_START_DATE, PRJ_FINISH_DATE, PRJ_MANAGER_ID

INSERT INTO Department(dep_id, dep_name, dep_manager_id, dep_chief_dep_id) VALUES (01, 'Администрация', NULL, NULL);
INSERT INTO Department(dep_id, dep_name, dep_manager_id, dep_chief_dep_id) VALUES (01, 'Администрация', NULL, NULL);
INSERT INTO Department(dep_id, dep_name, dep_manager_id, dep_chief_dep_id) VALUES (03, 'Разработки', NULL, 1);
INSERT INTO Department(dep_id, dep_name, dep_manager_id, dep_chief_dep_id) VALUES (05, 'Бухгалтерия', NULL, 1);
INSERT INTO Department(dep_id, dep_name, dep_manager_id, dep_chief_dep_id) VALUES (02, 'Отдел кадров', NULL, 5);
INSERT INTO Department(dep_id, dep_name, dep_manager_id, dep_chief_dep_id) VALUES (04, 'Тестирования', NULL, 3);

SELECT * FROM Department

UPDATE Department SET DEP_MANAGER_ID = 1 WHERE dep_id = 1;
UPDATE Department SET DEP_MANAGER_ID = 5 WHERE dep_id = 2;
UPDATE Department SET DEP_MANAGER_ID = 4 WHERE dep_id = 3;
UPDATE Department SET DEP_MANAGER_ID = 3 WHERE dep_id = 4;
UPDATE Department SET DEP_MANAGER_ID = 17 WHERE dep_id = 5;

SELECT constraint_name, table_name FROM user_constraints WHERE table_name = 'EMPLOYEE' OR table_name = 'DEPARTMENT' AND constraint_type <> 'P'
SELECT constraint_name, table_name, constraint_type FROM user_constraints WHERE table_name = 'EMPLOYEE' AND constraint_type <> 'P' OR table_name = 'DEPARTMENT' AND constraint_type <> 'P'

SELECT * FROM PROJECT
DESC project
SELECT * FROM department
SELECT * FROM employee

UPDATE Department SET dep_manager_id = 1 WHERE DEP_ID = 1;

SELECT *
FROM
WHERE
GROUP BY - группировка
HAVING - групповое условие
ORDER BY - группировка

SELECT DISTINCT EMP_FIRST_NAME, EMP_LAST_NAME FROM Employee

SELECT emp_salary*0.1, emp_first_name, emp_last_name FROM Employee
SELECT emp_salary*0.1 AS Премия, emp_first_name, emp_last_name FROM Employee WHERE emp_position = 'менеджер'

---------------------------
----18 октября-------------
---------------------------

SELECT emp_first_name, emp_last_name, 'Получит ', emp_salary*0.25 AS Бонус FROM employee
SELECT emp_first_name || ' ' || emp_last_name || ' получит ' || emp_salary*0.25 AS Бонус FROM employee

--Всех с зп больше 70 тысяч местных единиц
SELECT emp_first_name AS ИМЯ, emp_last_name AS Фамилия, emp_salary FROM employee WHERE emp_salary > 70000

--Вывод менеджеров и бухгалтеров
SELECT emp_first_name AS ИМЯ, emp_last_name AS Фамилия, emp_position AS Должность FROM employee WHERE emp_position = 'менеджер' OR emp_position = 'бухгалтер'

--Вывод менеджеров с зп больше 60 тыс. местных единиц
SELECT emp_first_name AS ИМЯ, emp_last_name AS Фамилия, emp_position AS Должность, emp_salary AS Зарплата FROM employee WHERE emp_position = 'менеджер' AND emp_salary > 60000

--Вывод сотрудников, дата поступления на который позже 1 января 2015 года
SELECT emp_first_name AS ИМЯ, emp_last_name AS Фамилия, EMP_JOIN_DATE AS дата_на_работу FROM Employee WHERE TO_CHAR(EMP_JOIN_DATE, 'YYYY') >= 2015
SELECT emp_first_name AS ИМЯ, emp_last_name AS Фамилия, EMP_JOIN_DATE AS дата_на_работу FROM Employee WHERE EMP_JOIN_DATE >= '1/1/2015'

--Вывод сотрудников с зп от 50 тыс. до 80 тыс. местных единиц
SELECT emp_first_name AS ИМЯ, emp_last_name AS Фамилия, emp_salary FROM employee WHERE emp_salary > 50000 AND emp_salary < 80000
SELECT emp_first_name AS ИМЯ, emp_last_name AS Фамилия, emp_salary FROM employee WHERE emp_salary BETWEEN 50000 AND 80000

--Вывод отдела, которые подчиняются не одному отделу
SELECT DEP_NAME FROM Department WHERE DEP_CHIEF_DEP_ID IS NULL

--Сотрудники, менеджеры, программисты и бухгалтеры
SELECT emp_first_name AS ИМЯ, emp_last_name AS Фамилия, emp_position AS Должность FROM employee WHERE emp_position = 'менеджер' OR emp_position = 'бухгалтер' OR emp_position = 'программист'
SELECT emp_first_name AS ИМЯ, emp_last_name AS Фамилия, emp_position AS Должность FROM employee WHERE emp_position IN ('менеджер', 'бухгалтер', 'программист')

--Не забываем про главбуха
SELECT emp_first_name AS ИМЯ, emp_last_name AS Фамилия, emp_position AS Должность FROM employee WHERE emp_position IN ('менеджер', 'программист') OR emp_position LIKE '%бухгалтер'
SELECT emp_first_name AS ИМЯ, emp_last_name AS Фамилия, emp_position AS Должность FROM employee WHERE emp_position LIKE '%бухгалтер'

--Фамилии на -ОВ
SELECT emp_first_name AS ИМЯ, emp_last_name AS Фамилия, emp_position AS Должность FROM employee WHERE emp_last_name LIKE '%ов'

-Есть Р и на -ОВ Фамилии
SELECT emp_first_name AS ИМЯ, emp_last_name AS Фамилия, emp_position AS Должность FROM employee WHERE emp_last_name LIKE '%р%ов'

-Есть Р и на -ОВ Фамилии

--Есть "Иван" подтекст в фамилии и имени
SELECT emp_first_name AS ИМЯ, emp_last_name AS Фамилия, emp_position AS Должность FROM employee WHERE emp_last_name LIKE '%Иван%' OR emp_first_name LIKE '%Иван%' OR emp_last_name LIKE '%иван%' OR emp_first_name LIKE '%иван%'

------------------------
--25 октября 2019-------
------------------------

--МНОГОТАБЛИЧНЫЕ ЗАПРОСЫ
--Имя сотрудника и их отделы, где работают
SELECT emp_first_name AS ИМЯ, emp_last_name AS Фамилия, dep_name AS Отделы FROM Employee, Department WHERE emp_dep_id = dep_id;

--Имя отдела и их руководителей
SELECT emp_first_name AS ИМЯ, emp_last_name AS Фамилия, dep_name AS Отделы FROM Employee, Department WHERE emp_id = dep_manager_id;
SELECT dep_name AS Отдел, emp_first_name||' '||emp_last_name AS Фамилия_Имя_Руководителя FROM Employee, Department WHERE emp_id = dep_manager_id;

--Имя проекта и их участники
SELECT PRJ_NAME AS Проект, emp_first_name||' '||emp_last_name AS Фамилия_Имя_Сотрудника FROM Project, Employee, REL_PRJ_EMP WHERE EMP_ID = REL_EMP_ID AND REL_PRJ_ID = PRJ_ID;

SELECT dep_name AS НАЗВАНИЕ_ОТДЕЛА, dep_name AS НАЗВ_ПОДЧИН_ОТДЕЛА FROM department WHERE НАЗВАНИЕ_ОТДЕЛА IN (SELECT dep_name FROM department WHERE dep_id IN (SELECT DEP_ID FROM department)) AND НАЗВ_ПОДЧИН_ОТДЕЛА IN
(SELECT dep_name FROM department WHERE dep_id IN (SELECT DEP_chief_dep_id FROM department))

--Имя отдела и его подчинение
SELECT D1.dep_name, D2.dep_name FROM department D1, department D2 WHERE D1.dep_chief_dep_id = d2.dep_id (+)

--Через джоины

SELECT t1.dep_name AS Назв_отдела, t2.dep_name AS назв_подч_отдела
FROM department t1
left join
department t2 ON (t1.dep_chief_dep_id  = t2.dep_id)

SELECT emp_first_name AS ИМЯ, emp_last_name AS Фамилия, dep_name AS Отделы FROM Employee inner join Department ON emp_dep_id = dep_id;

SELECT emp_first_name AS ИМЯ, emp_last_name AS Фамилия, dep_name AS Отделы FROM Employee inner join Department ON (emp_id = dep_manager_id);

SELECT emp_first_name AS ИМЯ, emp_last_name AS Фамилия, dep_name AS Отделы FROM Employee inner join Department ON emp_id = dep_manager_id;

-----------------------------
--01 ноября 2019 года -------
-----------------------------

SELECT emp_first_name||' '||emp_last_name AS Фамилия_Имя_Сотрудника, PRJ_NAME AS Проект FROM Project inner join REL_PRJ_EMP ON REL_PRJ_ID = PRJ_ID inner join Employee ON EMP_ID = REL_EMP_ID;

ALTER TABLE Employee MODIFY (EMP_ID NULL)

ALTER TABLE department ADD CONSTRAINT dep_chief_dep_id_FK Foreign KEY (dep_chief_dep_id) references department (dep_id)

ALTER TABLE employee ADD CONSTRAINT dep_id_FK Foreign KEY (emp_dep_id) references department (dep_id)

ALTER TABLE department ADD CONSTRAINT emp_manager_id_FK Foreign KEY (dep_manager_id) references employee (emp_id)

ALTER TABLE employee ADD CONSTRAINT salary_ck1 CHECK (EMP_SALARY > 14000)

DESC employee
EMP_ID, EMP_FIRST_NAME, EMP_LAST_NAME,  EMP_SALARY, EMP_POSITION, EMP_BIRTH_DATE, EMP_JOIN_DATE, EMP_DEP_ID
DESC department

CREATE TABLE department
(dep_id NUMBER PRIMARY KEY,
dep_name VARCHAR2(50) NOT NULL,
dep_manager_id NUMBER,
dep_chief_dep_id NUMBER
references department (dep_id)
)
SELECT constraint_name, table_name FROM user_constraints WHERE table_name = 'EMPLOYEE' OR table_name = 'DEPARTMENT'

CREATE TABLE Employee
(
emp_id NUMBER PRIMARY KEY,
emp_first_name VARCHAR2(50) NOT NULL,
emp_last_name VARCHAR2(50) NOT NULL,
emp_salary NUMBER,
emp_position VARCHAR2(50),
emp_birth_date DATE NOT NULL,
emp_join_date DATE,
emp_dep_id NUMBER,
CONSTRAINT salary_ck1
CHECK (emp_salary>14000)
)


CREATE TABLE Project
(
prj_id NUMBER PRIMARY KEY,
prj_name VARCHAR2(50) NOT NULL,
prj_start_date DATE,
prj_finish_date DATE,
prj_manager_id NUMBER
references Employee (emp_id)
)
ALTER TABLE Project MODIFY (PRJ_NAME VARCHAR2(50))

CREATE TABLE rel_prj_emp
(
rel_emp_id NUMBER references Employee (emp_id),
rel_prj_id NUMBER references Project (prj_id),
PRIMARY KEY (rel_emp_id, rel_prj_id)
)

DESC rel_prj_emp
DESC project
PRJ_ID, PRJ_NAME, PRJ_START_DATE, PRJ_FINISH_DATE, PRJ_MANAGER_ID

INSERT INTO Department(dep_id, dep_name, dep_manager_id, dep_chief_dep_id) VALUES (01, 'Администрация', NULL, NULL);
INSERT INTO Department(dep_id, dep_name, dep_manager_id, dep_chief_dep_id) VALUES (01, 'Администрация', NULL, NULL);
INSERT INTO Department(dep_id, dep_name, dep_manager_id, dep_chief_dep_id) VALUES (03, 'Разработки', NULL, 1);
INSERT INTO Department(dep_id, dep_name, dep_manager_id, dep_chief_dep_id) VALUES (05, 'Бухгалтерия', NULL, 1);
INSERT INTO Department(dep_id, dep_name, dep_manager_id, dep_chief_dep_id) VALUES (02, 'Отдел кадров', NULL, 5);
INSERT INTO Department(dep_id, dep_name, dep_manager_id, dep_chief_dep_id) VALUES (04, 'Тестирования', NULL, 3);

SELECT * FROM Department

UPDATE Department SET DEP_MANAGER_ID = 1 WHERE dep_id = 1;
UPDATE Department SET DEP_MANAGER_ID = 5 WHERE dep_id = 2;
UPDATE Department SET DEP_MANAGER_ID = 4 WHERE dep_id = 3;
UPDATE Department SET DEP_MANAGER_ID = 3 WHERE dep_id = 4;
UPDATE Department SET DEP_MANAGER_ID = 17 WHERE dep_id = 5;

SELECT constraint_name, table_name FROM user_constraints WHERE table_name = 'EMPLOYEE' OR table_name = 'DEPARTMENT' AND constraint_type <> 'P'
SELECT constraint_name, table_name, constraint_type FROM user_constraints WHERE table_name = 'EMPLOYEE' AND constraint_type <> 'P' OR table_name = 'DEPARTMENT' AND constraint_type <> 'P'

SELECT * FROM PROJECT
DESC project
SELECT * FROM department
SELECT * FROM employee

UPDATE Department SET dep_manager_id = 1 WHERE DEP_ID = 1;

SELECT *
FROM
WHERE
GROUP BY - группировка
HAVING - групповое условие
ORDER BY - группировка

SELECT DISTINCT EMP_FIRST_NAME, EMP_LAST_NAME FROM Employee

SELECT emp_salary*0.1, emp_first_name, emp_last_name FROM Employee
SELECT emp_salary*0.1 AS Премия, emp_first_name, emp_last_name FROM Employee WHERE emp_position = 'менеджер'

---------------------------
----18 октября-------------
---------------------------

SELECT emp_first_name, emp_last_name, 'Получит ', emp_salary*0.25 AS Бонус FROM employee
SELECT emp_first_name || ' ' || emp_last_name || ' получит ' || emp_salary*0.25 AS Бонус FROM employee

--Всех с зп больше 70 тысяч местных единиц
SELECT emp_first_name AS ИМЯ, emp_last_name AS Фамилия, emp_salary FROM employee WHERE emp_salary > 70000

--Вывод менеджеров и бухгалтеров
SELECT emp_first_name AS ИМЯ, emp_last_name AS Фамилия, emp_position AS Должность FROM employee WHERE emp_position = 'менеджер' OR emp_position = 'бухгалтер'

--Вывод менеджеров с зп больше 60 тыс. местных единиц
SELECT emp_first_name AS ИМЯ, emp_last_name AS Фамилия, emp_position AS Должность, emp_salary AS Зарплата FROM employee WHERE emp_position = 'менеджер' AND emp_salary > 60000

--Вывод сотрудников, дата поступления на который позже 1 января 2015 года
SELECT emp_first_name AS ИМЯ, emp_last_name AS Фамилия, EMP_JOIN_DATE AS дата_на_работу FROM Employee WHERE TO_CHAR(EMP_JOIN_DATE, 'YYYY') >= 2015
SELECT emp_first_name AS ИМЯ, emp_last_name AS Фамилия, EMP_JOIN_DATE AS дата_на_работу FROM Employee WHERE EMP_JOIN_DATE >= '1/1/2015'

--Вывод сотрудников с зп от 50 тыс. до 80 тыс. местных единиц
SELECT emp_first_name AS ИМЯ, emp_last_name AS Фамилия, emp_salary FROM employee WHERE emp_salary > 50000 AND emp_salary < 80000
SELECT emp_first_name AS ИМЯ, emp_last_name AS Фамилия, emp_salary FROM employee WHERE emp_salary BETWEEN 50000 AND 80000

--Вывод отдела, которые подчиняются не одному отделу
SELECT DEP_NAME FROM Department WHERE DEP_CHIEF_DEP_ID IS NULL

--Сотрудники, менеджеры, программисты и бухгалтеры
SELECT emp_first_name AS ИМЯ, emp_last_name AS Фамилия, emp_position AS Должность FROM employee WHERE emp_position = 'менеджер' OR emp_position = 'бухгалтер' OR emp_position = 'программист'
SELECT emp_first_name AS ИМЯ, emp_last_name AS Фамилия, emp_position AS Должность FROM employee WHERE emp_position IN ('менеджер', 'бухгалтер', 'программист')

--Не забываем про главбуха
SELECT emp_first_name AS ИМЯ, emp_last_name AS Фамилия, emp_position AS Должность FROM employee WHERE emp_position IN ('менеджер', 'программист') OR emp_position LIKE '%бухгалтер'
SELECT emp_first_name AS ИМЯ, emp_last_name AS Фамилия, emp_position AS Должность FROM employee WHERE emp_position LIKE '%бухгалтер'

--Фамилии на -ОВ
SELECT emp_first_name AS ИМЯ, emp_last_name AS Фамилия, emp_position AS Должность FROM employee WHERE emp_last_name LIKE '%ов'

-Есть Р и на -ОВ Фамилии
SELECT emp_first_name AS ИМЯ, emp_last_name AS Фамилия, emp_position AS Должность FROM employee WHERE emp_last_name LIKE '%р%ов'

-Есть Р и на -ОВ Фамилии

--Есть "Иван" подтекст в фамилии и имени
SELECT emp_first_name AS ИМЯ, emp_last_name AS Фамилия, emp_position AS Должность FROM employee WHERE emp_last_name LIKE '%Иван%' OR emp_first_name LIKE '%Иван%' OR emp_last_name LIKE '%иван%' OR emp_first_name LIKE '%иван%'

------------------------
--25 октября 2019-------
------------------------

--МНОГОТАБЛИЧНЫЕ ЗАПРОСЫ
--Имя сотрудника и их отделы, где работают
SELECT emp_first_name AS ИМЯ, emp_last_name AS Фамилия, dep_name AS Отделы FROM Employee, Department WHERE emp_dep_id = dep_id;

--Имя отдела и их руководителей
SELECT emp_first_name AS ИМЯ, emp_last_name AS Фамилия, dep_name AS Отделы FROM Employee, Department WHERE emp_id = dep_manager_id;
SELECT dep_name AS Отдел, emp_first_name||' '||emp_last_name AS Фамилия_Имя_Руководителя FROM Employee, Department WHERE emp_id = dep_manager_id;

--Имя проекта и их участники
SELECT PRJ_NAME AS Проект, emp_first_name||' '||emp_last_name AS Фамилия_Имя_Сотрудника FROM Project, Employee, REL_PRJ_EMP WHERE EMP_ID = REL_EMP_ID AND REL_PRJ_ID = PRJ_ID;

SELECT dep_name AS НАЗВАНИЕ_ОТДЕЛА, dep_name AS НАЗВ_ПОДЧИН_ОТДЕЛА FROM department WHERE НАЗВАНИЕ_ОТДЕЛА IN (SELECT dep_name FROM department WHERE dep_id IN (SELECT DEP_ID FROM department)) AND НАЗВ_ПОДЧИН_ОТДЕЛА IN
(SELECT dep_name FROM department WHERE dep_id IN (SELECT DEP_chief_dep_id FROM department))

--Имя отдела и его подчинение
SELECT D1.dep_name, D2.dep_name FROM department D1, department D2 WHERE D1.dep_chief_dep_id = d2.dep_id (+)

--Через джоины

SELECT t1.dep_name AS Назв_отдела, t2.dep_name AS назв_подч_отдела
FROM department t1
left join
department t2 ON (t1.dep_chief_dep_id  = t2.dep_id)

SELECT emp_first_name AS ИМЯ, emp_last_name AS Фамилия, dep_name AS Отделы FROM Employee inner join Department ON emp_dep_id = dep_id;

SELECT emp_first_name AS ИМЯ, emp_last_name AS Фамилия, dep_name AS Отделы FROM Employee inner join Department ON (emp_id = dep_manager_id);

SELECT emp_first_name AS ИМЯ, emp_last_name AS Фамилия, dep_name AS Отделы FROM Employee inner join Department ON emp_id = dep_manager_id;

-----------------------------
--01 ноября 2019 года--------
-----------------------------

SELECT emp_first_name||' '||emp_last_name AS Фамилия_Имя_Сотрудника, PRJ_NAME AS Проект FROM Project inner join REL_PRJ_EMP ON REL_PRJ_ID = PRJ_ID inner join Employee ON EMP_ID = REL_EMP_ID;

SELECT t1.dep_name AS Назв_отдела, t2.dep_name AS назв_подч_отдела FROM department t1 left join department t2 ON (t1.dep_chief_dep_id  = t2.dep_id)

SELECT emp_first_name||' '||emp_last_name AS Фамилия_Имя_Сотрудника, dep_name AS Отделы FROM Employee inner join Department ON emp_dep_id = dep_id WHERE dep_name LIKE '%Разработк%'

--Однострочные функции--
--lower--upper----------

SELECT LOWER(emp_first_name||' '||emp_last_name) AS Фамилия_Имя_Сотрудника FROM employee

SELECT LOWER(emp_last_name) AS Фамилия FROM employee

---------------------------
--08 ноября 2019 года------
---------------------------

--substr--
SELECT SUBSTR('Кошка Машка любит кашку', 10, 6) AS Строка FROM dual;
SELECT SUBSTR('Кошка Машка любит кашку', -10, 6) AS Строка FROM dual;
SELECT SUBSTR('Ехал Грека через реку', 6, 5) AS Строка FROM dual;

--instr--
SELECT INSTR('Кошка Машка любит кашку', 'шк', 11) AS Строка FROM dual;

--Replace-- (исх. строка, строка, замены)---
SELECT REPLACE('Кошка на окошке', 'ка') FROM dual;

SELECT REPLACE('Кошка на окошке', 'шк', 'шечк') AS Строка FROM dual;

--ABS
SELECT ABS(-6) FROM dual;
--ln
SELECT LN(8) FROM dual;
--exp
SELECT EXP(8) FROM dual;
--Power
SELECT POWER(8, 2) FROM dual;
--sqrt
SELECT SQRT(64) FROM dual;
--MOD
SELECT MOD(15, 4) FROM dual;
--ROUND
SELECT ROUND(15.7) FROM dual;
SELECT ROUND(15.4) FROM dual;
--Ceil (до верхней границы)
SELECT CEIL(14.3) FROM dual;
--floor (до нижней границы)
SELECT FLOOR(-14.3) FROM dual;
--TIME_FUNC
SELECT SYSDATE FROM dual;
SELECT SYSDATE+15 FROM dual;

SELECT ADD_MONTHS(SYSDATE, 3) FROM dual;

SELECT LAST_DAY(SYSDATE) - SYSDATE FROM dual;

SELECT MONTHS_BETWEEN(SYSDATE, '12/31/2019') FROM dual;

SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('31.12.19', 'dd.mm.yy')) FROM dual;

SELECT TO_DATE('31.12.19', 'dd.mm.yy') - SYSDATE FROM dual;

--to_char
SELECT TO_CHAR(SYSDATE, 'yyyy-mm-dd') FROM dual;

SELECT TO_CHAR(SYSDATE, 'yy-mm-dd') FROM dual;

SELECT TO_CHAR(SYSDATE, 'yy-mon-dd') FROM dual;

SELECT TO_CHAR(SYSDATE, 'yy-month-dd') FROM dual;

SELECT TO_CHAR(SYSDATE, 'yyyy-mon-dd,d') FROM dual;

SELECT TO_CHAR(SYSDATE, 'yyyy-mon-dd,d hh24:mi:ss') FROM dual;

SELECT TO_CHAR(SYSDATE, 'yyyy-mon-dd,d hh24:mi:ss') FROM dual;

SELECT TO_CHAR(SYSDATE, 'yyyy-mon-dd,d') FROM dual;

----------------------
--15-ноя-2019---------
----------------------

--Агрегатные функции
--Count

SELECT COUNT(*) FROM
--Сколько сотрудников
SELECT COUNT(*) FROM Employee

INSERT INTO Employee(emp_id, emp_first_name, emp_last_name, emp_birth_date, emp_join_date) VALUES(21, 'Иванов', 'Бездомный', TO_DATE('21.05.1990', 'dd.mm.yyyy'), SYSDATE)
UPDATE Employee SET emp_birth_date = TO_DATE('21.05.1990', 'dd.mm.yyyy') WHERE emp_id = 21

DESC Employee

--Сотрудники, количество, отдела, по отделам
SELECT COUNT(emp_dep_id) FROM Employee

SELECT emp_first_name AS ИМЯ, emp_last_name AS Фамилия, dep_name AS Отделы FROM Employee, Department WHERE emp_dep_id = dep_id;

--Количество должностей
SELECT COUNT(DISTINCT emp_position) AS Количество_Должностей FROM Employee

--Количество сотрудников в отделе разработки
SELECT COUNT(*) FROM Employee inner join Department ON emp_dep_id = dep_id AND dep_name LIKE '%работки%';

SELECT emp_first_name||' '||emp_last_name, dep_name FROM Employee, Department WHERE emp_dep_id = dep_id AND dep_name = 'Разработки';
SELECT * FROM department

SELECT emp_first_name||' '||emp_last_name, dep_name FROM Employee inner join Department ON emp_dep_id = dep_id AND dep_name LIKE '%работки%';

--Организации. Сумма зарплат
SELECT SUM(emp_salary) FROM employee

--Самый свежий в поступлении
SELECT MAX(emp_join_date) FROM employee

--Самый старый  по поступлению из БД
SELECT MIN(emp_join_date) FROM employee

--Разница ЗП (МАКС-МИН)
SELECT MAX(emp_salary) - MIN(emp_salary) AS Разница FROM employee

--Среднее значение ЗП
SELECT AVG(emp_salary) FROM employee WHERE emp_position LIKE '_енеджер'

--Group by
SELECT AVG(emp_salary) AS Ср_ЗП, emp_position AS Должность FROM employee GROUP BY emp_position

--Среднее ЗП по должностям, где зп более 40к
SELECT AVG(emp_salary) AS Ср_ЗП, emp_position AS Должность FROM employee WHERE emp_salary > 40000 GROUP BY emp_position

--Сколько представителей каждой должности
SELECT emp_position AS Должность, COUNT(*) AS Количество FROM employee GROUP BY emp_position

--Сумма зп по отделам
SELECT AVG(emp_salary) AS Ср_ЗП, dep_name AS Отдел FROM employee, department WHERE emp_dep_id = dep_id GROUP BY dep_name

--Сколько сотрудников над каждым проектом
--Select count(*) as Кол_во_Работников, prj_name as имя_проекта from employee, rel_prj_emp, project where emp_id = rel_emp_id and rel_prj_id = prj_id group by prj_name

SELECT COUNT(*) AS Кол_во_Работников, prj_name AS имя_проекта FROM rel_prj_emp, project WHERE rel_prj_id = prj_id GROUP BY prj_name

-------------------------
--22-ноя-2019------------
-------------------------

--Количество сотрудников по отделам и их должностям
SELECT dep_name AS Назв_отдела, emp_position AS Должности,  COUNT(emp_id) AS количество FROM Department, Employee WHERE emp_dep_id = dep_id GROUP BY dep_name, emp_position ORDER BY dep_name, emp_position
DESC employee

--Сумма ЗП по отделам
SELECT dep_name AS Отдел, emp_position AS Должность, SUM(emp_salary) AS Всего_ЗП FROM employee, department WHERE emp_dep_id = dep_id GROUP BY dep_name, emp_position

--Сумма ЗП по отделам среди менеджеров
SELECT dep_name AS Отдел, emp_position AS Должность, SUM(emp_salary) AS Всего_ЗП FROM employee, department WHERE emp_position LIKE '_енеджер' AND emp_dep_id = dep_id GROUP BY dep_name, emp_position

--Сумма ЗП по отделам среди менеджеров, с суммой их зп по отделам больше 70000
SELECT dep_name AS Отдел, emp_position AS Должность, SUM(emp_salary) AS Всего_ЗП FROM employee, department WHERE emp_position LIKE '_енеджер' AND emp_dep_id = dep_id GROUP BY dep_name, emp_position HAVING SUM(emp_salary) > 70000

--Найти максимальную сумму ЗП по отделам среди менеджеров
SELECT dep_name AS Отдел, SUM(emp_salary) AS Всего_ЗП FROM employee, department WHERE emp_dep_id = dep_id AND emp_position LIKE '_енеджер' GROUP BY dep_name, emp_position HAVING SUM(emp_salary)
IN (SELECT MAX(SUM(emp_salary)) FROM employee, department WHERE emp_dep_id = dep_id AND emp_position LIKE '_енеджер' GROUP BY dep_name, emp_position)

--Найти максимальную сумму по отделам
SELECT MAX(SUM(emp_salary)) AS ZP FROM employee, department WHERE emp_dep_id = dep_id GROUP BY dep_name, emp_position

--Подзапросы
--Вывести список людей с макс. ЗП
SELECT emp_first_name||' '||emp_last_name AS Фам_Имя, emp_salary FROM employee WHERE emp_salary = (SELECT MAX(emp_salary) FROM employee)

--Зарплаты плюс минус 10 процентов
--Список людей, у которых зарплата в пределах 10процентов от средней
SELECT emp_first_name||' '||emp_last_name AS Фам_Имя, emp_salary FROM employee WHERE emp_salary BETWEEN (SELECT (AVG(emp_salary)*0.9) FROM employee) AND (SELECT (AVG(emp_salary)*1.1) FROM employee)

--Фамилия сотрудников, которые участвуют в 3 процетах
SELECT emp_last_name AS Фамилии FROM employee, rel_prj_emp WHERE emp_id = rel_emp_id GROUP BY emp_last_name HAVING COUNT(emp_id) = 3

-------------------
--06-декабря-2019--
-------------------

--Кто является менеджером отдела, в которой работает программист
SELECT emp_first_name||' '||emp_last_name AS Фамилия_Имя_Сотрудника FROM Employee, Department WHERE Emp_id = dep_manager_id AND Dep_name IN (SELECT DISTINCT dep_name FROM Employee, Department WHERE Dep_id = Emp_dep_id AND EMP_position LIKE '_рограммист')

--Список сотрудников, и над какими количествами проектов они работают.
SELECT emp_first_name||' '||emp_last_name AS Фамилия_Имя_Сотрудника, (SELECT COUNT(*) FROM REL_PRJ_EMP  WHERE rel_emp_id = emp_id) FROM employee

--Список сотрудников, которые работают над 2 проектами
SELECT emp_first_name||' '||emp_last_name AS Фамилия_Имя_Сотрудника FROM Employee, REL_PRJ_EMP WHERE EMP_ID = REL_EMP_ID GROUP BY emp_first_name||' '||emp_last_name HAVING COUNT(REL_PRJ_ID) = 2

--Список сотрудников, которые работают над 2 проектами (коррелирующий подзапрос)
SELECT emp_first_name||' '||emp_last_name AS Фамилия_Имя_Сотрудника, (SELECT COUNT(*) FROM REL_PRJ_EMP WHERE rel_emp_id = emp_id) Количество FROM employee WHERE (SELECT COUNT(*) FROM REL_PRJ_EMP WHERE rel_emp_id = emp_id) = 2

--Список сотрудников с самой высокой ЗП в своём отделе
--Нерабочий прототип
SELECT emp_first_name||' '||emp_last_name, dep_name, emp_salary AS Фамилия_Имя_Сотрудника FROM Employee, department WHERE dep_id = emp_dep_id AND emp_salary IN (SELECT MAX(emp_salary) FROM Employee, department WHERE dep_id = emp_dep_id GROUP BY dep_id)

--Рабочий вариант
SELECT emp_first_name||' '||emp_last_name AS Фамилия_Имя_Сотрудника, EMP_SALARY FROM Employee E, (SELECT MAX(emp_salary) S, emp_dep_id FROM Employee GROUP BY emp_dep_id) T WHERE E.emp_salary = T.S AND T.emp_dep_id = E.EMP_DEP_ID

--Вывести названия отдела, сумма зарплат которой максимальная

--Вывести список сотрудников и их ЗП, их сумма ЗП которая максимальная