Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- 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);
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement