
CREATE TABLE EMPLOYEES (
  EMPLOYEE_ID  INTEGER  NOT NULL,
  FIRST_NAME  VARCHAR(20),
  LAST_NAME  VARCHAR(25)  NOT NULL,
  EMAIL  VARCHAR(25),
  PHONE_NUMBER  VARCHAR(20),
  HIRE_DATE  DATE  NOT NULL,
  SALARY  NUMERIC(8,2),
  COMMISSION_PCT  NUMERIC(2,2),
  MANAGER_ID  INTEGER  , /*NOT NULL, */
  DEPARTMENT_ID  INTEGER,  /*NOT NULL,*/
  JOB_ID  VARCHAR(10)  NOT NULL,

  PRIMARY KEY (EMPLOYEE_ID)
);


CREATE TABLE DEPARTMENTS (
  DEPARTMENT_ID  INTEGER  NOT NULL,
  DEPARTMENT_NAME  VARCHAR(30)  NOT NULL,
  MANAGER_ID  INTEGER  /*NOT NULL  */,
  LOCATION_ID  INTEGER ,  /* NOT NULL, */

  PRIMARY KEY (DEPARTMENT_ID)
);

CREATE TABLE JOB_GRADES (
  GRADE_LEVEL  VARCHAR(3)  NOT NULL,
  LOWEST_SAL  INTEGER  NOT NULL,
  HIGHEST_SAL  INTEGER  NOT NULL,

  PRIMARY KEY (GRADE_LEVEL)
);

CREATE TABLE LOCATIONS (
  LOCATION_ID  INTEGER  NOT NULL,
  STREET_ADDRESS  VARCHAR(40),
  POSTAL_CODE  VARCHAR(12),
  CITY  VARCHAR(30),
  STATE_PROVINCE  VARCHAR(25),
  COUNTRY_ID  CHAR(2)  NOT NULL,

  PRIMARY KEY (LOCATION_ID)
);

CREATE TABLE REGIONS (
  REGION_ID  INTEGER  NOT NULL,
  REGION_NAME  VARCHAR(25),

  PRIMARY KEY (REGION_ID)
);

CREATE TABLE COUNTRIES (
  COUNTRY_ID  CHAR(2)  NOT NULL,
  COUNTRY_NAME  VARCHAR(40),
  REGION_ID  INTEGER  NOT NULL,

  PRIMARY KEY (COUNTRY_ID)
);

CREATE TABLE JOBS (
  JOB_ID  VARCHAR(10)  NOT NULL,
  JOB_TITLE  VARCHAR(35)  NOT NULL,
  MIN_SALARY  NUMERIC(8),
  MAX_SALARY  NUMERIC(8),

  PRIMARY KEY (JOB_ID)
);





/*  Foreign Keys                                                    */



ALTER TABLE COUNTRIES ADD CONSTRAINT 
   FK_REGIONS_COUNTRIES FOREIGN KEY (REGION_ID) REFERENCES REGIONS(REGION_ID);

/*
ALTER TABLE EMPLOYEES ADD CONSTRAINT 
   FK_DEPARTMENTS_EMPLOYEES FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENTS(DEPARTMENT_ID);
*/

ALTER TABLE EMPLOYEES ADD CONSTRAINT 
   FK_JOBS_EMPLOYEES FOREIGN KEY (JOB_ID) REFERENCES JOBS(JOB_ID);



/*  Fast korrekt */
/*
ALTER TABLE DEPARTMENTS ADD CONSTRAINT 
   FK_EMPLOYEES_DEPARTMENTS FOREIGN KEY (MANAGER_ID) REFERENCES EMPLOYEES(MANAGER_ID);

   FK_EMPLOYEES_EMPLOYEES FOREIGN KEY (MANAGER_ID) REFERENCES EMPLOYEES(EMPLOYEE_ID);

*/

ALTER TABLE LOCATIONS ADD CONSTRAINT 
   FK_COUNTRIES_LOCATIONS FOREIGN KEY (COUNTRY_ID) REFERENCES COUNTRIES(COUNTRY_ID);

/* Fast korrekt */
/*ALTER TABLE DEPARTMENTS ADD CONSTRAINT 
   FK_LOCATIONS_DEPARTMENTS FOREIGN KEY (LOCATION_ID) REFERENCES LOCATIONS(LOCATION_ID);
*/

/*ALTER TABLE EMPLOYEES ADD CONSTRAINT */



ALTER TABLE JOB_GRADES
     ADD CONSTRAINT CH_LOW_HIGH_SAL CHECK (
  LOWEST_SAL < HIGHEST_SAL
);




ALTER TABLE JOB_GRADES
		ADD CONSTRAINT chsal CHECK(lowest_sal<highest_sal);






Insert into JOB_GRADES (GRADE_LEVEL , LOWEST_SAL , HIGHEST_SAL )
VALUES( 'A', 1000, 2999);

Insert into JOB_GRADES (GRADE_LEVEL , LOWEST_SAL , HIGHEST_SAL )
VALUES( 'B', 3000, 5999);

Insert into JOB_GRADES (GRADE_LEVEL , LOWEST_SAL , HIGHEST_SAL )
VALUES( 'C', 6000, 9999);

Insert into JOB_GRADES (GRADE_LEVEL , LOWEST_SAL , HIGHEST_SAL )
VALUES( 'D', 10000, 14999);

Insert into JOB_GRADES (GRADE_LEVEL , LOWEST_SAL , HIGHEST_SAL )
VALUES( 'E', 15000, 24999);

Insert into JOB_GRADES (GRADE_LEVEL , LOWEST_SAL , HIGHEST_SAL )
VALUES( 'F', 25000, 40000);



Insert into REGIONS (REGION_ID , REGION_NAME )
VALUES( 1, 'Europe');

Insert into REGIONS (REGION_ID , REGION_NAME )
VALUES( 2, 'Americas');

Insert into REGIONS (REGION_ID , REGION_NAME )
VALUES( 3, 'Asia');

Insert into REGIONS (REGION_ID , REGION_NAME )
VALUES( 4, 'Middle East Africa');




Insert into COUNTRIES (COUNTRY_ID , COUNTRY_NAME, REGION_ID )
VALUES( 'CA', 'Canada', 2);

Insert into COUNTRIES (COUNTRY_ID , COUNTRY_NAME, REGION_ID )
VALUES( 'DE', 'Germany', 1);

Insert into COUNTRIES (COUNTRY_ID , COUNTRY_NAME, REGION_ID )
VALUES( 'UK', 'United Kingdom', 1);

Insert into COUNTRIES (COUNTRY_ID , COUNTRY_NAME, REGION_ID )
VALUES( 'US', 'United States of Amerika', 2);




Insert into JOBS  (  JOB_ID, JOB_TITLE ,  MIN_SALARY,  MAX_SALARY )
VALUES( 'AD_PRES', 'President', 20000, 40000);

Insert into JOBS  (  JOB_ID, JOB_TITLE ,  MIN_SALARY,  MAX_SALARY )
VALUES( 'AD_VP', 'Administration Vice President', 15000, 30000);

Insert into JOBS  (  JOB_ID, JOB_TITLE ,  MIN_SALARY,  MAX_SALARY )
VALUES( 'AD_ASST', 'Administration Assistant', 3000, 6000);

Insert into JOBS  (  JOB_ID, JOB_TITLE ,  MIN_SALARY,  MAX_SALARY )
VALUES( 'AC_MGR', 'Accounting Manager', 8200, 16000);

Insert into JOBS  (  JOB_ID, JOB_TITLE ,  MIN_SALARY,  MAX_SALARY )
VALUES( 'AC_ACCOUNT', 'Public Accountant', 4200, 9000);

Insert into JOBS  (  JOB_ID, JOB_TITLE ,  MIN_SALARY,  MAX_SALARY )
VALUES( 'SA_MAN', 'Sales Manager', 10000, 20000);

Insert into JOBS  (  JOB_ID, JOB_TITLE ,  MIN_SALARY,  MAX_SALARY )
VALUES( 'SA_REP', 'Sales Representative', 6000,  12000);

Insert into JOBS  (  JOB_ID, JOB_TITLE ,  MIN_SALARY,  MAX_SALARY )
VALUES('ST_MAN', 'Stock Manager', 5500, 8500);

Insert into JOBS  (  JOB_ID, JOB_TITLE ,  MIN_SALARY,  MAX_SALARY )
VALUES('ST_CLERK', 'Stock Clerk', 2000, 5000);

Insert into JOBS  (  JOB_ID, JOB_TITLE , MIN_SALARY, MAX_SALARY )
VALUES('IT_PROG', 'Programmer',  4000, 10000);

Insert into JOBS  (  JOB_ID, JOB_TITLE ,  MIN_SALARY,  MAX_SALARY )
VALUES('MK_MAN', 'Marketing Manager', 9000, 15000);

Insert into JOBS  (  JOB_ID, JOB_TITLE ,  MIN_SALARY,  MAX_SALARY )
VALUES( 'MK_REP', 'Marketing Representative', 4000,  9000);



INSERT INTO LOCATIONS (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID)
VALUES(1400, '2014 Jabberwocky Rd', '26192', 'Southlake', 'Texas', 'US');

INSERT INTO LOCATIONS (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID)
VALUES(1500, '2011 Interiors Blvd', '99236', 'South San Francisco', 'California', 'US');

INSERT INTO LOCATIONS (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID)
VALUES(1700, '2004 Charade Rd', '98199', 'Seattle', 'Washington', 'US');

INSERT INTO LOCATIONS (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID)
VALUES(1800, '460 Bloor St. W.', 'ON M5S 1X8', 'Toronto', 'Ontario', 'CA');

INSERT INTO LOCATIONS (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID)
VALUES(2500, 'Magdalen Centre, The Oxford Science Park', 'OX9 9ZB', 'Oxford', 'Oxford', 'UK');





INSERT INTO DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
VALUES(10, 'Administration', 200, 1700);

INSERT INTO DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
VALUES(20, 'Marketing', 201, 1800);

INSERT INTO DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
VALUES(50, 'Shipping', 124, 1500);

INSERT INTO DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
VALUES(60, 'IT', 103, 1400);

INSERT INTO DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
VALUES(80, 'Sales', 149, 2500);


INSERT INTO DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
VALUES(90, 'Executive', 100, 1700);


INSERT INTO DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
VALUES(110, 'Accounting', 205, 1700);

INSERT INTO DEPARTMENTS (DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID)
VALUES(190, 'Contracting', 1700);



Insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID)
VALUES(200, 'Jennifer', 'Whalen', 'JWHALEN', '515.123.4444', '17.09.1987', 'AD_ASST', 4400, 101, 10);

Insert into EMPLOYEES(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID)
VALUES( 201 , 'Michael' , 'Hartstein', 'MHARTSTE' , '5.151.235.555', '17.02.1996', 'MK_MAN' , 13000 ,  100 , 20);

Insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID)
VALUES( 202 , 'Pat' , 'Fay', 'PFAY' , '6.031.236.666', '17.08.1997', 'MK_REP' , 6000 , 201 , 20);

Insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID)
VALUES( 205 , 'Shelley' , 'Higgins', 'SHIGGINS' , '5.151.238.080', '07.06.1994', 'AC_MGR' , 12000 ,  101 , 110);

Insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID)
VALUES( 206 , 'William' , 'Gietz', 'WGIETZ' , '5.151.238.181', '07.06.1994', 'AC_ACCOUNT' , 8300 ,  205 , 110);



Insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID)
VALUES( 100 , 'Steven' , 'King', 'SKING' , '5.151.234.567', '17.06.1987', 'AD_PRES' , 24000 , 90);

Insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID)
VALUES( 101 , 'Neena' , 'Kochhar', 'NKOCHHAR' , '5.151.234.568', '21.09.1989', 'AD_VP' , 17000 ,  100 , 90);

Insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID)
VALUES( 102 , 'Lex' , 'De Haan', 'LDEHAAN' , '5.151.234.569', '13.01.1993', 'AD_VP' , 17000 ,  100 , 90);

Insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID)
VALUES( 103 , 'Alexander' , 'Hunold', 'AHUNOLD' , '5.904.234.567', '03.01.1990', 'IT_PROG' , 9000 ,  102 , 60);





Insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID)
VALUES( 104 , 'Bruce' , 'Ernst', 'BERNST' , '5.904.234.568', '21.05.1991', 'IT_PROG' , 6000 ,  103 , 60);

Insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID)
VALUES( 107 , 'Diana' , 'Lorentz', 'DLORENTZ' , '5.904.235.567', '07.02.1999', 'IT_PROG' , 4200 ,  103 , 60);

Insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID)
VALUES( 124 , 'Kevin' , 'Mourgos', 'KMOURGOS' , '6.501.235.234', '16.11.1999', 'ST_MAN' , 5800 ,  100 , 50);

Insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID)
VALUES( 141 , 'Trenna' , 'Rajs', 'TRAJS' , '6.501.218.009', '17.10.1995', 'ST_CLERK' , 3500 ,  124 , 50);




Insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID)
VALUES( 142 , 'Curtis' , 'Davies', 'CDAVIES' , '6.501.212.994', '29.01.1997', 'ST_CLERK' , 3100 ,  124 , 50);

Insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID)
VALUES( 143 , 'Randall' , 'Matos', 'RMATOS' , '6.501.212.874', '15.03.1998', 'ST_CLERK' , 2600 ,  124 , 50);

Insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID)
VALUES( 144 , 'Peter' , 'Vargas', 'PVARGAS' , '6.501.212.004', '09.07.1998', 'ST_CLERK' , 2500 ,  124 , 50);

Insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
VALUES( 149 , 'Eleni' , 'Zlotkey', 'EZLOTKEY' , '011.44.1344.429018', '29.01.2000', 'SA_MAN' , 10500 , .2 , 100 , 80);

Insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
VALUES( 174 , 'Ellen' , 'Abel', 'EABEL' , '011.44.1644.429267', '11.05.1996', 'SA_REP' , 11000 , .3 , 149 , 80);

Insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
VALUES( 176 , 'Jonathon' , 'Taylor', 'JTAYLOR' , '011.44.1644.429265', '24.03.1998', 'SA_REP' , 8600 , .2 , 149 , 80);

Insert into EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID)
VALUES( 178 , 'Kimberely' , 'Grant', 'KGRANT' , '011.44.1644.429263', '24.05.1999', 'SA_REP' , 7000 , .15 , 149 );


