DaB 5

--1
CREATE OR REPLACE VIEW VIEW5 AS
SELECT SO.TITLE AS PART_TITLE, SD.TITLE, INITCAP(TRIM(SURNAME))||' '
||SUBSTR(FIRSTNAME,0,1)||'. '||SUBSTR(SURNAME,0,1)||'. ' AS FAM,
SS.SALARY, SS.ID, SS.HEAD, ST.STAGE
FROM EMPLOYEES SS, POSITIONS SD, DEPARTMENTS SO,
(SELECT EMPLOYEES.ID, ROUND(SYSDATE-COMEDATE) AS STAGE FROM EMPLOYEES ) ST
WHERE SS.POSITION_ID = SD.ID AND ST.ID=SS.ID
AND SS.DEPARTMENT_ID = SO.ID;

SELECT * FROM VIEW5;

--1A
SELECT LTRIM(SYS_CONNECT_BY_PATH(FAM || DECODE(HEAD, NULL, '', (' - ' || LOWER(TITLE))), ' / '), ' / ') AS HIERACHY
FROM VIEW5
WHERE CONNECT_BY_ISLEAF = 1
CONNECT BY PRIOR ID = HEAD START WITH HEAD IS NULL; --A

--1B
SELECT FAM, TRUNC(STAGE/365) YEAR, TRUNC(MOD(STAGE,365)/(365/12)) MONTH FROM VIEW5;

--2
GRANT SELECT ON T_SOTR TO DIT15_17_19;

CREATE OR REPLACE VIEW VIEW_FOR_2 AS
SELECT * FROM (
SELECT DEPARTMENT_ID, POSITION_ID
FROM (
SELECT EMPLOYESS.ID, EMPLOYEES.SURNAME, FIRSTNAME, DEPARTMENT_ID, POSITION_ID
FROM EMPLOYEES
UNION ALL
SELECT KOD_SOTR, FAM, NAME, kod_otd, kod_dol
FROM t_sotr))
PIVOT (COUNT (*) FOR DEPARTMENT_ID IN (16, 22, 96))
ORDER BY POSITION_ID; --2A

SELECT POSITIONS.TITLE
FROM POSITIONS, VIEW_FOR_2
WHERE VIEW_FOR_2."16" + VIEW_FOR_2."22" + VIEW_FOR_2."96" <> 0 AND POSITIONS.POSITION_ID=VIEW_FOR_2.POSITION_ID; --2B

--3
SELECT decode(GROUPING(TITLE), 0, ' ', 'ИТОГО: ' || TRIM(PART_TITLE)) AS "Итоги по отделам",
       decode(GROUPING(TITLE), 0, TRIM(TITLE), ' ') AS "Должность",
       SUM(SALARY) AS "Оклады", COUNT(*) AS "Кол. человек"
FROM VIEW5
GROUP BY ROLLUP (PART_TITLE, TITLE); --3

--4
SELECT DECODE(GROUPING(PART_TITLE),0,TRIM(PART_TITLE), 'ВСЕГО:') ОТДЕЛ,
DECODE(GROUPING(TITLE),0, TRIM(TITLE), 'ОБЩЕЕ КОЛИЧЕСТВО: '||TRIM(PART_TITLE)) ДОЛЖНОСТЬ, COUNT(*) "КОЛ-ВО ЧЕЛ."
FROM VIEW5
GROUP BY CUBE(PART_TITLE, TITLE) ORDER BY PART_TITLE, TITLE;

--6
CREATE SEQUENCE SQ1
INCREMENT BY -2
START WITH 120
MAXVALUE 120
MINVALUE 20
CYCLE
CACHE 5;

--7
INSERT INTO POSITIONS
VALUES (SQ1.NEXTVAL, 'ИНЖЕНЕР 3');
INSERT INTO POSITIONS
VALUES (SQ1.NEXTVAL, 'ПРОГРАММИСТ 3');

--8
ALTER SEQUENCE SQ1 INCREMENT BY 1;
ALTER SEQUENCE SQ1 NOCYCLE;

--9
CREATE SEQUENCE Ssq
INCREMENT BY 1
START WITH 100
MINVALUE 100
NOMAXVALUE
NOCYCLE;

--10
INSERT INTO DEPARTMENTS
VALUES (Ssq.NEXTVAL,'КРИПТОГРАФИЯ',NULL);
INSERT INTO DEPARTMENTS
VALUES (Ssq.NEXTVAL,'КРИПТОГРАФИЯ',NULL);