--REGIONS START
CREATE TABLE REGIONS(
  REGION_ID    NUMBER CONSTRAINT REGION_ID_NN   NOT NULL,
  REGION_NAME  VARCHAR2(25 BYTE));
--COUNTRIES START
CREATE TABLE COUNTRIES(
  COUNTRY_ID    CHAR(2 BYTE) CONSTRAINT COUNTRY_ID_NN NOT NULL,
  COUNTRY_NAME  VARCHAR2(40 BYTE),
  REGION_ID     NUMBER, 
  CONSTRAINT COUNTRY_C_ID_PK
 PRIMARY KEY
 (COUNTRY_ID));
--LOCATIONS START
CREATE TABLE LOCATIONS(
  LOCATION_ID     NUMBER(4),
  STREET_ADDRESS  VARCHAR2(40 BYTE),
  POSTAL_CODE     VARCHAR2(12 BYTE),
  CITY            VARCHAR2(30 BYTE) CONSTRAINT LOC_CITY_NN NOT NULL,
  STATE_PROVINCE  VARCHAR2(25 BYTE),
  COUNTRY_ID      CHAR(2 BYTE));
--JOBS START
CREATE TABLE JOBS(
  JOB_ID      VARCHAR2(10 BYTE),
  JOB_TITLE   VARCHAR2(35 BYTE) CONSTRAINT JOB_TITLE_NN NOT NULL,
  MIN_SALARY  NUMBER(6),
  MAX_SALARY  NUMBER(6));
--EMPLOYEES START
CREATE TABLE EMPLOYEES(
  EMPLOYEE_ID     NUMBER(6),
  FIRST_NAME      VARCHAR2(20 BYTE),
  LAST_NAME       VARCHAR2(25 BYTE) CONSTRAINT EMP_LAST_NAME_NN NOT NULL,
  EMAIL           VARCHAR2(25 BYTE) CONSTRAINT EMP_EMAIL_NN NOT NULL,
  PHONE_NUMBER    VARCHAR2(20 BYTE),
  HIRE_DATE       DATE CONSTRAINT EMP_HIRE_DATE_NN NOT NULL,
  JOB_ID          VARCHAR2(10 BYTE) CONSTRAINT EMP_JOB_NN NOT NULL,
  SALARY          NUMBER(8,2),
  COMMISSION_PCT  NUMBER(2,2),
  MANAGER_ID      NUMBER(6),
  DEPARTMENT_ID   NUMBER(4));
--DEPARTMENTS START
CREATE TABLE DEPARTMENTS(
  DEPARTMENT_ID    NUMBER(4),
  DEPARTMENT_NAME  VARCHAR2(30 BYTE) CONSTRAINT DEPT_NAME_NN NOT NULL,
  MANAGER_ID       NUMBER(6),
  LOCATION_ID      NUMBER(4));
--VIEW EMPLOYEE_DETAILS START
CREATE OR REPLACE FORCE VIEW EMP_DETAILS_VIEW(
   EMPLOYEE_ID,
   JOB_ID,
   MANAGER_ID,
   DEPARTMENT_ID,
   LOCATION_ID,
   COUNTRY_ID,
   FIRST_NAME,
   LAST_NAME,
   SALARY,
   COMMISSION_PCT,
   DEPARTMENT_NAME,
   JOB_TITLE,
   CITY,
   STATE_PROVINCE,
   COUNTRY_NAME,
   REGION_NAME)AS SELECT e.employee_id,
          e.job_id,
          e.manager_id,
          e.department_id,
          d.location_id,
          l.country_id,
          e.first_name,
          e.last_name,
          e.salary,
          e.commission_pct,
          d.department_name,
          j.job_title,
          l.city,
          l.state_province,
          c.country_name,
          r.region_name
     FROM employees e,
          departments d,
          jobs j,
          locations l,
          countries c,
          regions r
    WHERE     e.department_id = d.department_id
          AND d.location_id = l.location_id
          AND l.country_id = c.country_id
          AND c.region_id = r.region_id
          AND j.job_id = e.job_id
   WITH READ ONLY;
-- REGIONS
insert into regions values ('1','Europe');
-- COUNTRIES
insert into countries values ('DE','Germany','1');
insert into countries values ('UK','United Kingdom','1');
-- LOCATIONS
insert into locations values ('2500','Magdalen Centre, The Oxford Science Park','OX9 9ZB','Oxford','Oxford','UK');
-- JOBS
insert into jobs values ('SA_MAN','Sales Manager','10000','20080');
insert into jobs values ('SA_REP','Sales Representative','6000','12008');
--EMPLOYEES
insert into employees values ('145','John','Russell','JRUSSEL','011.44.1344.429268',TO_DATE('01.10.2004','DD.MM.YYYY'),'SA_MAN','14000','0.4','100','80');
insert into employees values ('150','Peter','Tucker','PTUCKER','011.44.1344.129268',TO_DATE('30.01.2005','DD.MM.YYYY'),'SA_REP','10000','0.3','145','80');
insert into employees values ('151','David','Bernstein','DBERNSTE','011.44.1344.345268',TO_DATE('24.03.2005','DD.MM.YYYY'),'SA_REP','9500','0.25','145','80');
insert into employees values ('152','Peter','Hall','PHALL','011.44.1344.478968',TO_DATE('20.08.2005','DD.MM.YYYY'),'SA_REP','9000','0.25','145','80');
insert into employees values ('153','Christopher','Olsen','COLSEN','011.44.1344.498718',TO_DATE('30.03.2006','DD.MM.YYYY'),'SA_REP','8000','0.2','145','80');
insert into employees values ('154','Nanette','Cambrault','NCAMBRAU','011.44.1344.987668',TO_DATE('09.12.2006','DD.MM.YYYY'),'SA_REP','7500','0.2','145','80');
insert into employees values ('155','Oliver','Tuvault','OTUVAULT','011.44.1344.486508',TO_DATE('23.11.2007','DD.MM.YYYY'),'SA_REP','7000','0.15','145','80');
-- DEPARTMENTS
insert into departments values ('80','Sales','145','2500');