SHARE
TWEET

Untitled

a guest Dec 3rd, 2019 76 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- 1
  2.  
  3. CREATE VIEW PENSIONER AS
  4.     SELECT * FROM EMP
  5.     WHERE MONTHS_BETWEEN(SYSDATE, BIRTHDATE)/12 >= 60;
  6.  
  7.  
  8.  
  9. -- 2
  10.  
  11. CREATE VIEW DISMISSED (EMPNAME, ENDDATE, DEPTNAME, JOBNAME) AS
  12.     SELECT E.EMPNAME, C.ENDDATE, D.DEPTNAME, J.JOBNAME
  13.     FROM EMP E NATURAL JOIN CAREER C NATURAL JOIN DEPT D NATURAL JOIN JOB J
  14.     WHERE C.ENDDATE IS NOT NULL;
  15.  
  16.  
  17.  
  18. -- 3
  19.  
  20. CREATE OR REPLACE VIEW KVARTAL (SOTRUDNIK, DOLZHNOST, ITOGO_3_KV) AS
  21. SELECT E.EMPNAME, J.JOBNAME, SUM(S.SALVALUE)
  22. FROM JOB J NATURAL JOIN CAREER NATURAL JOIN EMP E NATURAL JOIN SALARY S
  23. WHERE S.YEAR = 2010 AND S.MONTH BETWEEN 7 AND 9
  24. GROUP BY E.EMPNAME, J.JOBNAME;
  25.  
  26.  
  27.  
  28. -- 4
  29.  
  30. CREATE VIEW DISMISSED2SALARY (EMPNAME, ENDDATE, DEPTNAME, JOBNAME, MONTH_AND_SUM) AS
  31.     SELECT D.EMPNAME, D.ENDDATE, D.DEPTNAME, D.JOBNAME, S.MONTH || ', ' || S.SALVALUE AS MONTH_AND_SUM
  32.     FROM DISMISSED D NATURAL JOIN SALARY S
  33.     WHERE S.EMPNO IN (SELECT EMPNO
  34.                         FROM DISMISSED NATURAL JOIN SALARY
  35.                         GROUP BY EMPNO
  36.                         HAVING COUNT(SALVALUE)>2);
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top