Miscellany

CREATE VIEW "B1_AUF1A"AS  SELECT E.FIRST_NAME, E.LAST_NAME  FROM HR.EMPLOYEES E  INNER JOIN HR.DEPARTMENTS D USING(DEPARTMENT_ID)  INNER JOIN HR.LOCATIONS L USING(LOCATION_ID)  WHERE L.COUNTRY_ID = 'US'  AND E.JOB_ID       = 'IT_PROG';  CREATE VIEW "B1_AUF1BV1"AS  SELECT E.FIRST_NAME, E.LAST_NAME  FROM HR.EMPLOYEES E  WHERE add_months(E.HIRE_DATE, 12*5) < CURRENT_DATE;CREATE VIEW "B1_AUF1BV2"AS  SELECT E.FIRST_NAME, E.LAST_NAME  FROM HR.EMPLOYEES E  WHERE TRUNC((sysdate-E.HIRE_DATE)/365) > 5;CREATE VIEW "B1_AUF1C"AS  SELECT E2.FIRST_NAME, E2.LAST_NAME, E2.JOB_ID, E2.SALARY  FROM( SELECT E.JOB_ID AS JOB, MIN(E.SALARY) AS SALARY        FROM HR.EMPLOYEES E        GROUP BY E.JOB_ID  ) J , HR.EMPLOYEES E2  WHERE J.JOB  = E2.JOB_ID  AND J.SALARY = E2.SALARY;CREATE VIEW "B1_AUF1D"AS  SELECT E.FIRST_NAME, E.LAST_NAME  FROM( SELECT JH.EMPLOYEE_ID AS EMP, MAX(J.MAX_SALARY) AS SALARY        FROM HR.JOB_HISTORY JH        INNER JOIN HR.JOBS J ON JH.JOB_ID = J.JOB_ID        GROUP BY JH.EMPLOYEE_ID  ) M INNER JOIN HR.EMPLOYEES E ON M.EMP = E.EMPLOYEE_ID  WHERE (M.SALARY*0.8) < E.SALARY;CREATE VIEW "B1_AUF1E"AS  SELECT DISTINCT D.DEPARTMENT_NAME, TRUNC(D2.AVG_SALARY,2) AS SALARY, D2.NUM  FROM( SELECT E.DEPARTMENT_ID AS DEP, AVG(E.SALARY) AS AVG_SALARY, COUNT(E.EMPLOYEE_ID) AS NUM        FROM HR.EMPLOYEES E        GROUP BY E.DEPARTMENT_ID  ) D2 INNER JOIN HR.DEPARTMENTS D ON D2.DEP = D.DEPARTMENT_ID  WHERE D2.NUM > 2;CREATE VIEW "B1_AUF1F"AS  SELECT E2.FIRST_NAME, E2.LAST_NAME, DS.DEPARTMENT_NAME, TRUNC(D.SALARY,2) AS SALAERY, TRUNC((E2.SALARY-D.SALARY),2) AS DIFF  FROM( SELECT E.DEPARTMENT_ID AS DEP, AVG(E.SALARY) AS SALARY        FROM HR.EMPLOYEES E        GROUP BY E.DEPARTMENT_ID  ) D   INNER JOIN HR.DEPARTMENTS DS ON D.DEP = DS.DEPARTMENT_ID  INNER JOIN HR.EMPLOYEES E2 ON DS.DEPARTMENT_ID = E2.DEPARTMENT_ID;    CREATE TABLE "ABTEILUNGEN"    (	    "NR" INTEGER PRIMARY KEY,     "LEITER" INTEGER REFERENCES PROFESSOREN(ID),     "#PROFS" INTEGER   );  CREATE TABLE "PROFESSOREN"    (    "ID" INTEGER PRIMARY KEY,     "NAME" VARCHAR(50) NOT NULL,     "GRAD" CHAR(2) NOT NULL,    "ABTEILUNG" NUMBER REFERENCES ABTEILUNGEN(NR),    "GEHALT" DECIMAL(7,2),     "AGE" INTEGER(3),    CONSTRAINT "CheckGrad" CHECK ("GRAD" IN ('C2','C3','C4')),    CONSTRAINT "CheckAge" CHECK ("AGE" > 0 and "AGE" < 70)   );      CREATE TABLE "VORLESUNGEN"    (	    "ID" INTEGER PRIMARY KEY,     "TITEL" VARCHAR(50) NOT NULL,     "DOZENT" REFERENCES PROFESSOREN(ID),     "HOERER" VARCHAR(50),     "SEMESTER" INTEGER(2),   );     CREATE TABLE "MITARBEITER"    (	    "NAME" VARCHAR(50) PRIMARY KEY,     "CHEF" INTEGER REFERENCES PROFESSOREN(ID),     "GEHALT" NUMBER,     "QUALIFIKATION" VARCHAR(50)   );      CREATE TABLE "BETEILIGT"    (	    "PROFNAME" INTEGER REFERENCES PROFESSOREN(ID),     "PROJEKTID" INTEGER REFERENCES PROJEKTE(ID),     "ANTEIL" DECIMAL(1,1),     PRIMARY KEY(PROFNAME,PROJEKTID),    CONSTRAINT "CheckAnteil" CHECK ("ANTEIL" >= 0 and "ANTEIL" <= 1)   );     CREATE TABLE "PROJEKTE"    (	    "ID" NUMBER PRIMARY KEY,     "BEZEICHNUNG" VARCHAR(50),     "BEGINN" DATE NOT NULL,     "ENDE" DATE,     "VOLUMEN" VARCHAR(50)   );      CREATE TABLE "STUDENTEN"    (	    "ID" INTEGER PRIMARY KEY,     "NAME" VARCHAR(50),     "JOB" VARCHAR(50),     "SEMESTER" INTEGER(3),     "DIPLOMVATER" INTEGER REFERENCES PROFESSOREN(ID),     "VORDIPLOM" INTEGER(1)   );