-- 1

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



-- 2

CREATE VIEW DISMISSED (EMPNAME, ENDDATE, DEPTNAME, JOBNAME) AS
    SELECT E.EMPNAME, C.ENDDATE, D.DEPTNAME, J.JOBNAME
    FROM EMP E NATURAL JOIN CAREER C NATURAL JOIN DEPT D NATURAL JOIN JOB J
    WHERE C.ENDDATE IS NOT NULL;



-- 3

CREATE OR REPLACE VIEW KVARTAL (SOTRUDNIK, DOLZHNOST, ITOGO_3_KV) AS
SELECT E.EMPNAME, J.JOBNAME, SUM(S.SALVALUE)
FROM JOB J NATURAL JOIN CAREER NATURAL JOIN EMP E NATURAL JOIN SALARY S
WHERE S.YEAR = 2010 AND S.MONTH BETWEEN 7 AND 9
GROUP BY E.EMPNAME, J.JOBNAME;



-- 4

CREATE VIEW DISMISSED2SALARY (EMPNAME, ENDDATE, DEPTNAME, JOBNAME, MONTH_AND_SUM) AS
    SELECT D.EMPNAME, D.ENDDATE, D.DEPTNAME, D.JOBNAME, S.MONTH || ', ' || S.SALVALUE AS MONTH_AND_SUM
    FROM DISMISSED D NATURAL JOIN SALARY S
    WHERE S.EMPNO IN (SELECT EMPNO
                        FROM DISMISSED NATURAL JOIN SALARY
                        GROUP BY EMPNO
                        HAVING COUNT(SALVALUE)>2);