MD&SUBD LR

LR 3.1

--ПРОСТЕЙШИЕ ЗАПРОСЫ

--1. Выдать информацию об отделах, расположенных в Chicago и New York.

SELECT * 
FROM DEPT 
WHERE DEPTADDR IN ('NEW YORK', 'CHICAGO'); 

--ФУНКЦИИ

--2. Найти минимальную заработную плату, начисленную в 2009 году.

SELECT MIN(SALVALUE) AS MIN_SALARY_IN_2007 
FROM SALARY 
WHERE YEAR = 2007; 

--3. Выдать информацию обо всех работниках, родившихся не позднее 1 января 1960 года.
--TO_DATE( string1 [, format_mask] [, nls_language] ) 

SELECT * 
FROM EMP 
WHERE BIRTHDATE <= TO_DATE('01-01-1960','dd-mm-yyyy');

--4. Подсчитать число работников, сведения о которых имеются в базе данных.

SELECT COUNT(*) 
FROM EMP; 

--5. Найти работников, чьё имя состоит из одного слова. Имена выдать на нижнем регистре, 
--с удалением стоящей справа буквы t.
--RTRIM( string1, [ trim_string ] ) 

SELECT RTRIM(LOWER(EMPNAME), 't') AS FORMATTED_NAME 
FROM EMP 
WHERE EMPNAME NOT LIKE('% %'); 

--6. Выдать информацию о работниках, указав дату рождения в формате день(число), месяц(название), 
--год(название). 

SELECT EMPNO, EMPNAME, TO_CHAR(BIRTHDATE, 'DD-MONTH-YEAR') AS FORMATTED_BIRTHDATE 
FROM EMP; 

SELECT EMPNO, EMPNAME, TO_CHAR(BIRTHDATE, 'DD-MONTH-YYYY') AS FORMATTED_BIRTHDATE 
FROM EMP; 

--7. Выдать информацию о должностях, изменив названия должности “CLERK” и “DRIVER” на “WORKER”.
--DECODE( expression , search , result [, search , result]... [, default] ) 

SELECT DECODE(JOBNAME, 'DRIVER', 'WORKER', 'CLERK', 'WORKER', JOBNAME) AS FORMATTED_JOB 
FROM JOB; 

--HAVING

--8. Определите среднюю зарплату за годы, в которые были начисления не менее чем за три месяца.

SELECT YEAR, AVG(SALVALUE) AS AVERAGE_VALUE 
FROM SALARY 
GROUP BY YEAR 
HAVING COUNT(MONTH) >= 3; 

--СОЕДИНЕНИЕ ПО РАВЕНСТВУ

--9. Выведете ведомость получения зарплаты с указанием имен служащих.

SELECT EMP.EMPNAME, SALARY.MONTH, SALARY.SALVALUE 
FROM EMP, SALARY 
WHERE EMP.EMPNO = SALARY.EMPNO; 

--СОЕДИНЕНИЕ НЕ ПО РАВЕНСТВУ:

--10. Укажите  сведения о начислении сотрудникам зарплаты, попадающей в вилку: минимальный оклад 
--по должности - минимальный оклад по должности плюс пятьсот. Укажите соответствующую вилке  должность.

SELECT EMP.EMPNAME, JOB.JOBNAME, SALARY.SALVALUE, JOB.MINSALARY 
FROM SALARY 
INNER JOIN EMP 
ON SALARY.EMPNO = EMP.EMPNO 
INNER JOIN CAREER 
ON CAREER.EMPNO = EMP.EMPNO 
INNER JOIN JOB 
ON JOB.JOBNO = CAREER.JOBNO 
WHERE SALARY.SALVALUE > JOB.MINSALARY 
AND SALARY.SALVALUE < JOB.MINSALARY + 500; 

--ОБЪЕДИНЕНИЕ ТАБЛИЦ

--ВНУТРЕННЕЕ

--11. Укажите сведения о заработной плате, совпадающей с минимальными окладами по должностям 
--(с указанием этих должностей).

SELECT EMP.EMPNAME, SALARY.SALVALUE, JOB.MINSALARY, JOB.JOBNAME 
FROM SALARY 
INNER JOIN EMP 
ON SALARY.EMPNO = EMP.EMPNO 
INNER JOIN CAREER 
ON EMP.EMPNO = CAREER.EMPNO 
INNER JOIN JOB 
ON CAREER.JOBNO = JOB.JOBNO 
WHERE SALARY.SALVALUE = JOB.MINSALARY; 

--ЕСТЕСТВЕННОЕ

--12. Найдите  сведения о карьере сотрудников с указанием вместо номера сотрудника его имени.

SELECT EMP.EMPNAME, SALARY.STARTDATE, JOB.ENDDATE 
FROM EMP 
NATURAL JOIN CAREER; 

--ПРОСТОЕ ВНУТРЕННЕЕ СОЕДИНЕНИЕ

--13. Найдите  сведения о карьере сотрудников с указанием вместо номера сотрудника его имени.

SELECT EMP.EMPNAME, CAREER.STARTDATE, CAREER.ENDDATE 
FROM EMP 
INNER JOIN CAREER 
ON EMP.EMPNO = CAREER.EMPNO; 

--ОБЪЕДИНЕНИЕ ТРЁХ И БОЛЬШЕГО ЧИСЛА ТАБЛИЦ

--14. Выдайте сведения о карьере сотрудников с указанием их имён, наименования должности, и 
--названия отдела.

SELECT EMP.EMPNAME, JOB.DEPTNAME, JOB.JOBNAME, CAREER.STARTDATE, CAREER.ENDDATE 
FROM EMP 
NATURAL JOIN CAREER 
NATURAL JOIN DEPT 
NATURAL JOIN JOB 
ORDER BY EMP.EMPNAME, CAREER.STARTDATE; 

--15. Выдайте сведения о карьере сотрудников с указанием их имён. Выдайте сведения о карьере 
-- сотрудников с указанием их имён. Какой вид внешнего объединения Вы использовали? Составьте запрос
-- с использованием противоположного вида соединения. Составьте запрос с использованием полного
-- внешнего соединения.

SELECT EMP.EMPNAME, CAREER.STARTDATE, CAREER.ENDDATE 
FROM EMP 
RIGHT OUTER JOIN CAREER 
ON EMP.EMPNO = CAREER.EMPNO 
ORDER BY EMP.EMPNAME, CAREER.STARTDATE; 

LR 3.2

-- ПОДЗАПРОСЫ, ВЫБИРАЮЩИЕ ОДНУ СТРОКУ
-- WHERE IN returns values that matches values in a list or subquery  

--1. Найти имена сотрудников, получивших за годы начисления зарплаты минимальную зарплату.

SELECT EMP.EMPNAME  
FROM EMP  
WHERE (  
  SELECT MIN(SALARY.SALVALUE) 
  FROM SALARY 
  WHERE SALARY.EMPNO = EMP.EMPNO 
) 
IN ( 
  SELECT JOB.MINSALARY 
  FROM JOB NATURAL JOIN CAREER 
  WHERE EMP.EMPNO = CAREER.EMPNO 
); 

-- ПОДЗАПРОСЫ, ВОЗВРАЩАЮЩИЕ БОЛЕЕ ОДНОЙ СТРОКИ

--2. Найти имена сотрудников, работавших или работающих в тех же отделах, в которых 
--работал или работает сотрудник с именем RICHARD MARTIN.

SELECT EMP.EMPNAME  
FROM EMP NATURAL JOIN CAREER 
WHERE CAREER.DEPTNO 
IN ( 
  SELECT CAREER.DEPTNO 
  FROM CAREER NATURAL JOIN EMP 
  WHERE EMP.EMPNAME = 'RICHARD MARTIN' 
) 
AND EMP.EMPNAME != 'RICHARD MARTIN' 
GROUP BY EMP.EMPNAME; 

--СРАВНЕНИЕ БОЛЕЕ ЧЕМ ПО ОДНОМУ ЗНАЧЕНИЮ

--3. Найти имена сотрудников, работавших или работающих в тех же отделах и должностях, 
--что и сотрудник 'RICHARD MARTIN'.

SELECT EMP.EMPNAME  
FROM EMP NATURAL JOIN CAREER 
WHERE (CAREER.DEPTNO, CAREER.JOBNO) 
IN ( 
  SELECT CAREER.DEPTNO, CAREER.JOBNO 
  FROM CAREER NATURAL JOIN EMP 
  WHERE EMP.EMPNAME = 'RICHARD MARTIN' 
) 
AND EMP.EMPNAME != 'RICHARD MARTIN' 
GROUP BY EMP.EMPNAME; 

--ОПЕРАТОРЫ ANY/ALL
-- The ANY and ALL operators are used with a WHERE or HAVING clause. 
-- The ANY operator returns true if any of the subquery values meet the condition. 
-- The ALL operator returns true if all of the subquery values meet the condition. 

--4. Найти сведения о номерах сотрудников, получивших за какой-либо месяц зарплату большую, 
--чем средняя зарплата   за 2007 г. или большую чем средняя зарплата за 2008г. 

SELECT EMPNO 
FROM SALARY 
WHERE SALVALUE > ANY ( 
  SELECT AVG(SALVALUE) 
  FROM SALARY 
  WHERE YEAR = 2007 
) 
OR SALVALUE > ANY ( 
  SELECT AVG(SALVALUE) 
  FROM SALARY 
  WHERE YEAR = 2008 
)  
GROUP BY EMPNO; 

--5. Найти сведения о номерах сотрудников, получивших зарплату за какой-либо месяц большую, 
--чем средние зарплаты за все годы начислений.

SELECT EMPNO 
FROM SALARY 
WHERE SALVALUE > ANY ( 
  SELECT AVG(SALVALUE) 
  FROM SALARY 
) 
GROUP BY EMPNO; 

--ИСПОЛЬЗОВАНИЕ HAVING С ВЛОЖЕННЫМИ ПОДЗАПРОСАМИ
--The HAVING was added to SQL because the WHERE keyword could not be used with aggregate functions

--6. Определить годы, в которые начисленная средняя зарплата была больше средней
--зарплаты за все годы начислений.

SELECT YEAR 
FROM SALARY 
GROUP BY YEAR 
HAVING AVG(SALVALUE) > ( 
  SELECT AVG(SALVALUE) 
  FROM SALARY 
); 

--КОРРЕЛИРУЮЩИЕ ПОДЗАПРОСЫ

--7. Определить номера отделов, в которых работали или работают сотрудники, имеющие начисления зарплаты. 

SELECT DEPTNO 
FROM DEPT 
WHERE DEPTNO 
IN ( 
  SELECT DEPTNO FROM CAREER 
  NATURAL JOIN EMP 
  NATURAL JOIN SALARY 
  WHERE SALARY.SALVALUE IS NOT NULL 
) 
GROUP BY DEPTNO; 

--ОПЕРАТОР EXISTS
--The EXISTS operator is used to test for the existence of any record in a subquery.
--The EXISTS operator returns true if the subquery returns one or more records.

--8. Определить номера отделов, в которых работали или работают сотрудники, имеющие начисления зарплаты.

SELECT DEPTNO 
FROM DEPT 
WHERE EXISTS ( 
  SELECT SALVALUE 
  FROM CAREER 
  NATURAL JOIN EMP 
  NATURAL JOIN SALARY  
  WHERE DEPT.DEPTNO = CAREER.DEPTNO 
); 

--ОПЕРАТОР NOT EXISTS
--NOT EXISTS returns true if the search result is empty

--9. Определить номера отделов, для сотрудников которых не начислялась зарплата.

SELECT DEPTNO 
FROM DEPT 
WHERE NOT EXISTS (
  SELECT SALVALUE 
  FROM CAREER 
  NATURAL JOIN EMP 
  NATURAL JOIN SALARY 
  WHERE DEPT.DEPTNO = CAREER.DEPTNO 
); 

--СОСТАВНЫЕ ЗАПРОСЫ

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

SELECT EMP.EMPNAME, DEPT.DEPTNAME, DEPT.DEPTADDR 
FROM EMP 
NATURAL JOIN CAREER 
NATURAL JOIN DEPT; 

--ОПЕРАТОР CAST
-- CAST() converts a value (of any type) into a specified datatype. 
-- FLOOR() returns the largest integer less than or equal to the specified numeric expression. 

--11. Определить целую часть средних зарплат, по годам начисления.

SELECT 
  YEAR, 
  AVG(SALVALUE) AS AVERAGE_SALVALUE, 
  CAST(FLOOR(AVG(SALVALUE)) AS NUMBER(20)) AS INTEGER_AVERAGE_SALVALUE 
FROM SALARY 
GROUP BY YEAR; 

--ОПЕРАТОР CASE
-- CASE - go through conditions and return a value when the first condition is met 
--The MONTHS_BETWEEN() function is used to get the number of months between dates (date1, date2)

--12. Разделите сотрудников на возрастные группы: A) возраст 20-30 лет; B) 31-40 лет; C) 41-50; 
--D) 51-60 или возраст не определён.

SELECT 
  EMPNO, 
  BIRTHDATE, 
  FLOOR(MONTHS_BETWEEN(SYSDATE, BIRTHDATE) / 12) AS YEARS, 
  (CASE
    WHEN FLOOR(MONTHS_BETWEEN(SYSDATE, BIRTHDATE) / 12) >= 20 
     AND FLOOR(MONTHS_BETWEEN(SYSDATE, BIRTHDATE) / 12) <= 30 THEN 'A' 
    WHEN FLOOR(MONTHS_BETWEEN(SYSDATE, BIRTHDATE) / 12) >= 31 
     AND FLOOR(MONTHS_BETWEEN(SYSDATE, BIRTHDATE) / 12) <= 40 THEN 'B' 
    WHEN FLOOR(MONTHS_BETWEEN(SYSDATE, BIRTHDATE) / 12) >= 41 
     AND FLOOR(MONTHS_BETWEEN(SYSDATE, BIRTHDATE) / 12) <= 50 THEN 'C' 
    WHEN FLOOR(MONTHS_BETWEEN(SYSDATE, BIRTHDATE) / 12) >= 51 
     AND FLOOR(MONTHS_BETWEEN(SYSDATE, BIRTHDATE) / 12) <= 60 THEN 'D' 
    WHEN BIRTHDATE IS NULL THEN 'D' 
    ELSE NULL
   END) AS AGE_GROUP 
FROM EMP;

--13. Перекодируйте номера отделов, добавив перед номером отдела буквы BI для
номеров &lt;=20, буквы LN для номеров &gt;=30.

SELECT 
  DEPTNO, 
  (CASE
    WHEN DEPTNO <= 20 THEN CONCAT('BI', CAST(DEPTNO AS VARCHAR(20))) 
    WHEN DEPTNO >= 30 THEN CONCAT('LN', CAST(DEPTNO AS VARCHAR(20))) 
    ELSE CAST(DEPTNO AS VARCHAR(20))
  END) AS FORMATTED_DEPTNO 
FROM DEPT; 

--ОПЕРАТОР COALESCE (объединяться)
--COALESCE evaluates the arguments in order and returns the current value of the first 
--expression that initially does not evaluate to NULL.
--For example, SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value'); returns the third value 
--because the third value is the first value that is not null.

--14. Выдать информацию о сотрудниках из таблицы EMP, заменив отсутствие данного о дате рождения 
--датой '01-01-1000'.

SELECT 
  EMPNO, 
  BIRTHDATE, 
  COALESCE(BIRTHDATE, TO_DATE('01-01-1000', 'DD-MM-YYYY')) AS FORMATTED_BIRTHDATE 
FROM EMP; 

LR 4

--1. Поднимите нижнюю границу минимальной заработной платы в таблице JOB до 1000$. 

UPDATE JOB 
SET MINSALARY = 1000 
WHERE MINSALARY < 1000; 

SELECT MINSALARY 
FROM JOB 
WHERE MINSALARY < 1000; 

--2. Поднимите минимальную зарплату в таблице JOB на 10% для всех специальностей, 
-- кроме финансового директора.
-- <> - not equal

SELECT JOBNAME, MINSALARY 
FROM JOB 
WHERE JOBNAME <> 'FINANCIAL DIRECTOR'; 

UPDATE JOB 
SET MINSALARY = (MINSALARY + 0.1 * MINSALARY) 
WHERE JOBNAME <> 'FINANCIAL DIRECTOR'; 

SELECT JOBNAME, MINSALARY AS UPDATED_MINSALARY 
FROM JOB 
WHERE JOBNAME <> 'FINANCIAL DIRECTOR'; 

--3. Поднимите минимальную зарплату в таблице JOB на 10% для клерков и на 20% для 
-- финансового директора (одним оператором).

SELECT JOBNAME, MINSALARY 
FROM JOB 
WHERE JOBNAME IN ('CLERK', 'FINANCIAL DIRECTOR'); 

UPDATE JOB 
SET MINSALARY = 
(CASE
  WHEN JOBNAME = 'CLERK' THEN (1.1 * MINSALARY) 
  WHEN JOBNAME = 'FINANCIAL DIRECTOR' THEN (1.2 * MINSALARY) 
  ELSE MINSALARY
END); 

SELECT JOBNAME, MINSALARY AS UPDATED_MINSALARY 
FROM JOB 
WHERE JOBNAME IN ('CLERK', 'FINANCIAL DIRECTOR'); 

--4. Установите минимальную зарплату финансового директора равной 90% от зарплаты 
--исполнительного директора.

SELECT JOBNAME, MINSALARY 
FROM JOB 
WHERE JOBNAME IN ('CLERK', 'FINANCIAL DIRECTOR'); 

UPDATE JOB 
SET MINSALARY = 0.9 * 
(
  SELECT MINSALARY 
  FROM JOB 
  WHERE JOBNAME = 'EXECUTIVE DIRECTOR'
)
WHERE JOBNAME = 'FINANCIAL DIRECTOR'; 

SELECT JOBNAME, MINSALARY AS UPDATED_MINSALARY 
FROM JOB 
WHERE JOBNAME IN ('CLERK', 'FINANCIAL DIRECTOR'); 

--5. Приведите в таблице EMP имена служащих, начинающиеся на букву ‘J’, к нижнему регистру.
--SUBSTR( string, start_position, [ length ] ) 

SELECT EMPNAME 
FROM EMP 
WHERE SUBSTR(EMPNAME, 1, 1) = 'J';

UPDATE EMP
SET EMPNAME = LOWER(EMPNAME)
WHERE SUBSTR(EMPNAME, 1, 1) = 'J';

SELECT EMPNAME AS FORMATTED_EMPNAME 
FROM EMP 
WHERE SUBSTR(EMPNAME, 1, 1) = 'J';

--6. Измените в таблице EMP имена служащих, состоящие из двух слов, так, чтобы оба слова 
-- в имени начинались с заглавной буквы, а продолжались прописными. 
--INITCAP returns char, with the first letter of each word in uppercase, all other letters in lowercase.

SELECT EMPNAME 
FROM EMP 
WHERE EMPNAME LIKE('% %'); 

UPDATE EMP 
SET EMPNAME = INITCAP(EMPNAME) 
WHERE EMPNAME LIKE('% %');

SELECT EMPNAME AS FORMATTED_EMPNAME 
FROM EMP 
WHERE EMPNAME LIKE('% %'); 

--7. Приведите в таблице EMP имена служащих к верхнему регистру.

SELECT EMPNAME 
FROM EMP; 

UPDATE EMP 
SET EMPNAME = UPPER(EMPNAME); 

SELECT EMPNAME AS FORMATTED_EMPNAME 
FROM EMP; 

--8. Перенесите отдел исследований (RESEARCH) в тот же город, в котором расположен отдел продаж (SALES).

SELECT DEPTNAME, DEPTADDR 
FROM DEPT 
WHERE DEPTNAME IN ('SALES', 'RESEARCH');

UPDATE DEPT
SET DEPTADDR = 
(
  SELECT DEPTADDR 
  FROM DEPT 
  WHERE DEPTNAME = 'SALES'
) 
WHERE DEPTNAME = 'RESEARCH'; 

SELECT DEPTNAME, DEPTADDR  AS UPDATED_DEPTADDR 
FROM DEPT 
WHERE DEPTNAME IN ('SALES', 'RESEARCH');

--9. Добавьте нового сотрудника в таблицу EMP. Его имя и фамилия должны совпадать с Вашими, 
--записанными латинскими буквами согласно паспорту, дата рождения также совпадает с Вашей.

SELECT * 
FROM EMP 
WHERE EMPNO = 7317; 

INSERT INTO EMP 
VALUES (7317, 'SHPAK MAKSIM', TO_DATE('17-02-1998','DD-MM-YYYY')); 

SELECT * 
FROM EMP 
WHERE EMPNO = 7317; 

--10. Определите нового сотрудника (см. предыдущее задание) на работу в бухгалтерию 
--(отдел ACCOUNTING) начиная с текущей даты.

SELECT DEPTNO, DEPTNAME 
FROM DEPT 
WHERE DEPTNAME = 'ACCOUNTING'; 

INSERT INTO CAREER 
VALUES (1004, 7317, 10, CURRENT_DATE, TO_DATE('29-05-2019','DD-MM-YYYY')); 

SELECT * 
FROM CAREER 
WHERE EMPNO = 7317; 

--11. Удалите все записи из таблицы TMP_EMP. Добавьте в нее информацию о сотрудниках, 
--которые работают клерками в настоящий момент.

DROP TABLE TMP_EMP;

CREATE TABLE TMP_EMP (
  EMPNO NUMBER(20) PRIMARY KEY, 
  EMPNAME VARCHAR2(20) NOT NULL, 
  BIRTHDATE DATE
);

DELETE FROM TMP_EMP; 

SELECT JOB.JOBNAME, CAREER.STARTDATE, CAREER.ENDDATE 
FROM JOB INNER JOIN CAREER 
ON CAREER.JOBNO = JOB.JOBNO 
WHERE JOB.JOBNAME = 'CLERK';  

INSERT INTO TMP_EMP 
SELECT * 
FROM EMP 
WHERE EMP.EMPNO IN 
(
  SELECT EMPNO 
  FROM CAREER INNER JOIN JOB 
  ON CAREER.JOBNO = JOB.JOBNO 
  WHERE JOB.JOBNAME = 'CLERK' AND CAREER.STARTDATE <= CURRENT_DATE 
    AND (CAREER.ENDDATE > CURRENT_DATE OR CAREER.ENDDATE IS NULL)
);

SELECT * 
FROM TMP_EMP; 

--12. Добавьте в таблицу TMP_EMP информацию о тех сотрудниках, которые уже не работают на 
--предприятии, а в период работы занимали только одну должность.

SELECT CAREER.EMPNO, COUNT(CAREER.JOBNO) AS COUNT_JOBS 
FROM CAREER 
GROUP BY CAREER.EMPNO 
ORDER BY CAREER.EMPNO; 

SELECT EMP.EMPNO, EMP.EMPNAME 
FROM EMP 
INNER JOIN CAREER 
ON EMP.EMPNO = CAREER.EMPNO 
WHERE CAREER.ENDDATE IS NOT NULL 
AND EMP.EMPNO IN ( 
  SELECT EMP.EMPNO 
  FROM EMP 
  INNER JOIN CAREER 
  ON EMP.EMPNO = CAREER.EMPNO 
  GROUP BY EMP.EMPNO 
  HAVING COUNT(CAREER.JOBNO) = 1 
) 
ORDER BY EMP.EMPNO; 

INSERT INTO TMP_EMP 
SELECT DISTINCT EMP.* 
FROM EMP 
INNER JOIN CAREER 
ON EMP.EMPNO = CAREER.EMPNO 
WHERE CAREER.ENDDATE IS NOT NULL 
AND EMP.EMPNO IN ( 
  SELECT EMP.EMPNO 
  FROM EMP 
  INNER JOIN CAREER 
  ON EMP.EMPNO = CAREER.EMPNO 
  GROUP BY EMP.EMPNO 
  HAVING COUNT(CAREER.JOBNO) = 1 
) 
AND EMP.EMPNO NOT IN ( 
  SELECT EMP.EMPNO 
  FROM EMP 
  INNER JOIN TMP_EMP 
  ON EMP.EMPNO = TMP_EMP.EMPNO 
); 

--13. Выполните тот же запрос для тех сотрудников, которые никогда не приступали к работе на 
--предприятии. 

SELECT DISTINCT EMP.*, CAREER.STARTDATE 
FROM EMP 
INNER JOIN CAREER 
ON EMP.EMPNO = CAREER.EMPNO 
WHERE CAREER.STARTDATE IS NULL 
ORDER BY EMP.EMPNO; 

SELECT DISTINCT EMP.* 
FROM EMP 
INNER JOIN CAREER 
ON EMP.EMPNO NOT IN (
  SELECT CAREER.EMPNO 
  FROM CAREER 
); 

INSERT INTO TMP_EMP 
SELECT DISTINCT EMP.* 
FROM EMP 
INNER JOIN CAREER 
ON EMP.EMPNO NOT IN (
  SELECT CAREER.EMPNO 
  FROM CAREER 
) AND EMP.EMPNO NOT IN ( 
  SELECT EMP.EMPNO 
  FROM EMP 
  INNER JOIN TMP_EMP 
  ON EMP.EMPNO = TMP_EMP.EMPNO 
); 

--14. Удалите все записи из таблицы TMP_JOB и добавьте в нее информацию по тем специальностям, 
--которые не используются в настоящий момент на предприятии.

DROP TABLE TMP_JOB; 

CREATE TABLE TMP_JOB ( 
  JOBNO NUMBER(20) PRIMARY KEY, 
  JOBNAME VARCHAR2(20) NOT NULL, 
  MINSALARY NUMBER(20) 
); 

DELETE FROM TMP_JOB; 

SELECT DISTINCT * 
FROM JOB 
WHERE JOB.JOBNO NOT IN ( 
  SELECT CAREER.JOBNO 
  FROM CAREER 
  INNER JOIN JOB 
  ON CAREER.JOBNO = JOB.JOBNO 
); 

INSERT INTO TMP_JOB
SELECT * 
FROM JOB 
WHERE JOB.JOBNO NOT IN ( 
  SELECT CAREER.JOBNO 
  FROM CAREER 
  INNER JOIN JOB 
  ON CAREER.JOBNO = JOB.JOBNO 
); 

--15. Начислите зарплату в размере 120% минимального должностного оклада всем сотрудникам, 
--работающим на предприятии. Зарплату начислять по должности, занимаемой сотрудником в 
--настоящий момент и отнести ее на прошлый месяц относительно текущей даты.
--The EXTRACT() function extracts a part from a given date 

SELECT CAREER.EMPNO, 
  1.2 * JOB.MINSALARY AS NEW_SALARY, 
  EXTRACT(MONTH FROM ADD_MONTHS(CURRENT_DATE, -1)) AS EXTRACTED_MONTH, 
  EXTRACT(YEAR FROM ADD_MONTHS(CURRENT_DATE, -1)) AS EXTRACTED_YEAR 
FROM CAREER 
INNER JOIN JOB 
ON CAREER.JOBNO = JOB.JOBNO 
WHERE CAREER.STARTDATE IS NOT NULL 
AND CAREER.ENDDATE IS NULL; 

INSERT INTO SALARY (EMPNO, SALVALUE, MONTH, YEAR) 
SELECT CAREER.EMPNO, 
  1.2 * JOB.MINSALARY, 
  EXTRACT(MONTH FROM ADD_MONTHS(CURRENT_DATE, -1)), 
  EXTRACT(YEAR FROM ADD_MONTHS(CURRENT_DATE, -1))  
FROM CAREER 
INNER JOIN JOB 
ON CAREER.JOBNO = JOB.JOBNO 
WHERE CAREER.STARTDATE IS NOT NULL 
AND CAREER.ENDDATE IS NULL; 

--16. Удалите данные о зарплате за прошлый год.

SELECT * 
FROM SALARY 
WHERE YEAR = EXTRACT(YEAR FROM ADD_MONTHS(CURRENT_DATE, -12)); 

DELETE FROM SALARY 
WHERE YEAR = EXTRACT(YEAR FROM ADD_MONTHS(CURRENT_DATE, -12)); 

--17. Удалите информацию о карьере сотрудников, которые в настоящий момент уже не работают на 
--предприятии, но когда-то работали.

SELECT * 
FROM CAREER 
WHERE ENDDATE IS NOT NULL 
AND ENDDATE < CURRENT_DATE; 

DELETE FROM CAREER 
WHERE ENDDATE IS NOT NULL 
AND ENDDATE < CURRENT_DATE; 

--18. Удалите информацию о начисленной зарплате сотрудников, которые в настоящий момент уже 
--не работают на предприятии (можно использовать результаты работы предыдущего запроса).

SELECT * 
FROM SALARY 
WHERE EMPNO = ( 
  SELECT EMPNO
  FROM CAREER 
  WHERE ENDDATE IS NOT NULL 
  AND ENDDATE < CURRENT_DATE 
); 

DELETE FROM SALARY 
WHERE EMPNO = ( 
  SELECT EMPNO
  FROM CAREER 
  WHERE ENDDATE IS NOT NULL 
  AND ENDDATE < CURRENT_DATE 
); 

--19. Удалите записи из таблицы EMP для тех сотрудников, которые никогда не приступали 
--к работе на предприятии.

SELECT * 
FROM EMP 
WHERE EMP.EMPNO NOT IN (
  SELECT CAREER.EMPNO 
  FROM CAREER 
);

DELETE FROM EMP 
WHERE EMP.EMPNO NOT IN ( 
  SELECT CAREER.EMPNO 
  FROM CAREER 
); 

LR 5

DROP VIEW RETIREE_EMP; 
DROP VIEW DISMISSED_EMP; 
DROP VIEW SOME_EMP_DATA; 
DROP VIEW SOME_DISMISSED_EMP_DATA; 

--1. Создайте представление, содержащее данные о сотрудниках пенсионного
--возраста. 

CREATE VIEW RETIREE_EMP AS 
SELECT * 
FROM EMP 
WHERE MONTHS_BETWEEN(SYSDATE, BIRTHDATE) / 12 >= 60; 

SELECT * 
FROM RETIREE_EMP; 

--2. Создайте представление, содержащее данные об уволенных сотрудниках:
--имя сотрудника, дата увольнения, отдел, должность.

CREATE VIEW DISMISSED_EMP (EMPNO, EMPNAME, ENDDATE, DEPTNAME, JOBNAME) AS 
SELECT EMP.EMPNO, EMP.EMPNAME, CAREER.ENDDATE, DEPT.DEPTNAME, JOB.JOBNAME 
FROM EMP 
  INNER JOIN CAREER 
   ON EMP.EMPNO = CAREER.EMPNO 
  INNER JOIN DEPT 
   ON DEPT.DEPTNO = CAREER.DEPTNO 
  INNER JOIN JOB 
   ON JOB.JOBNO = CAREER.JOBNO 
WHERE CAREER.ENDDATE IS NOT NULL 
 AND CURRENT_DATE >= CAREER.ENDDATE; 

SELECT * 
FROM DISMISSED_EMP; 

--3. Создайте представление, содержащее имя сотрудника, должность,
--занимаемую сотрудником в данный момент, суммарную заработную плату
--сотрудника за третий квартал 2010 года. Первый столбец назвать Sotrudnik,
--второй – Dolzhnost, третий – Itogo_3_kv. 

SELECT EMP.EMPNAME, JOB.JOBNAME, SUM(SALARY.SALVALUE) AS SUM_SALARY 
FROM JOB 
  INNER JOIN CAREER 
   ON JOB.JOBNO = CAREER.JOBNO 
  INNER JOIN EMP 
   ON EMP.EMPNO = CAREER.EMPNO 
  INNER JOIN SALARY 
   ON SALARY.EMPNO = EMP.EMPNO 
WHERE SALARY.YEAR = 2007 
 AND SALARY.MONTH >= 7 
 AND SALARY.MONTH <= 9 
GROUP BY EMP.EMPNAME, JOB.JOBNAME; 
   
CREATE VIEW SOME_EMP_DATA (Sotrudik, Dolzhnost, Itogo_3_kv) AS 
SELECT EMP.EMPNAME, JOB.JOBNAME, SUM(SALARY.SALVALUE) 
FROM JOB 
  INNER JOIN CAREER 
   ON JOB.JOBNO = CAREER.JOBNO 
  INNER JOIN EMP 
   ON EMP.EMPNO = CAREER.EMPNO 
  INNER JOIN SALARY 
   ON SALARY.EMPNO = EMP.EMPNO 
WHERE SALARY.YEAR = 2007 
 AND SALARY.MONTH >= 7 
 AND SALARY.MONTH <= 9 
 AND CAREER.ENDDATE IS NULL 
 AND CURRENT_DATE < CAREER.ENDDATE 
GROUP BY EMP.EMPNAME, JOB.JOBNAME; 

SELECT * 
FROM SOME_EMP_DATA; 

--4. На основе представления из задания 2 и таблицы SALARY создайте
--представление, содержащее данные об уволенных сотрудниках, которым
--зарплата начислялась более 2 раз. В созданном представлении месяц
--начисления зарплаты и сумма зарплаты вывести в одном столбце, в качестве
--разделителя использовать запятую.

CREATE VIEW SOME_DISMISSED_EMP_DATA AS 
SELECT DISMISSED_EMP.*, SALARY.MONTH || ', ' || SALARY.SALVALUE AS MONTH_WITH_SALARY 
FROM DISMISSED_EMP 
  INNER JOIN SALARY 
   ON SALARY.EMPNO = DISMISSED_EMP.EMPNO 
WHERE SALARY.EMPNO IN (
  SELECT DISTINCT DISMISSED_EMP.EMPNO 
  FROM DISMISSED_EMP 
    INNER JOIN SALARY 
     ON SALARY.EMPNO = DISMISSED_EMP.EMPNO 
  GROUP BY DISMISSED_EMP.EMPNO 
  HAVING COUNT(SALARY.SALVALUE) > 2
);

SELECT * 
FROM SOME_DISMISSED_EMP_DATA;