text: 
  -- creating tables:
  
  CREATE TABLE REGIONS 
  (
    REGION_ID INTEGER PRIMARY KEY NOT NULL 
  , REGION_NAME VARCHAR(25) 
  );
  
  CREATE TABLE JOBS 
  (
    JOB_ID VARCHAR(10) PRIMARY KEY NOT NULL 
  , JOB_TITLE VARCHAR(35) NOT NULL 
  , MIN_SALARY NUMERIC 
  , MAX_SALARY NUMERIC
  );
  
  CREATE TABLE COUNTRIES 
  (
    COUNTRY_ID CHAR(2) PRIMARY KEY NOT NULL 
  , COUNTRY_NAME VARCHAR(40) 
  , REGION_ID INT
  , FOREIGN KEY(REGION_ID) REFERENCES REGIONS(REGION_ID) ON DELETE CASCADE
   );
  
  CREATE TABLE LOCATIONS 
  (
    LOCATION_ID INTEGER PRIMARY KEY NOT NULL 
  , STREET_ADDRESS VARCHAR(40) 
  , POSTAL_CODE VARCHAR(12) 
  , CITY VARCHAR(30) NOT NULL 
  , STATE_PROVINCE VARCHAR(25) 
  , COUNTRY_ID CHAR(2) 
  , FOREIGN KEY(COUNTRY_ID) REFERENCES COUNTRIES(COUNTRY_ID) ON DELETE CASCADE
  );
  
  CREATE TABLE DEPARTMENTS 
  (
    DEPARTMENT_ID INTEGER PRIMARY KEY NOT NULL 
  , DEPARTMENT_NAME VARCHAR(30) NOT NULL 
  , MANAGER_ID INTEGER
  , LOCATION_ID INTEGER
  , FOREIGN KEY(MANAGER_ID) REFERENCES EMPLOYEES(EMPLOYEE_ID) ON DELETE SET NULL
  , FOREIGN KEY(LOCATION_ID) REFERENCES LOCATIONS(LOCATION_ID) ON DELETE SET NULL
  );
  
  CREATE TABLE EMPLOYEES 
  (
    EMPLOYEE_ID INTEGER PRIMARY KEY NOT NULL 
  , FIRST_NAME VARCHAR(20) 
  , LAST_NAME VARCHAR2(25) NOT NULL 
  , EMAIL VARCHAR(25) NOT NULL 
  , PHONE_NUMBER VARCHAR(20) 
  , HIRE_DATE TEXT NOT NULL 
  , JOB_ID VARCHAR(10) NOT NULL 
  , SALARY REAL
  , COMMISSION_PCT REAL
  , MANAGER_ID INTEGER
  , DEPARTMENT_ID INTEGER
  , FOREIGN KEY(MANAGER_ID) REFERENCES EMPLOYEES(EMPLOYEE_ID) ON DELETE CASCADE
  , FOREIGN KEY(DEPARTMENT_ID) REFERENCES DEPARTMENTS(DEPARTMENT_ID) ON DELETE SET NULL
  , FOREIGN KEY(JOB_ID) REFERENCES JOBS(JOB_ID) ON DELETE RESTRICT
  );
  
  CREATE TABLE JOB_HISTORY 
  (
    EMPLOYEE_ID INTEGER NOT NULL 
  , START_DATE TEXT NOT NULL 
  , END_DATE TEXT NOT NULL 
  , JOB_ID VARCHAR(10) NOT NULL 
  , DEPARTMENT_ID INTEGER
  , FOREIGN KEY(JOB_ID) REFERENCES JOBS(JOB_ID) ON DELETE CASCADE
  , FOREIGN KEY(DEPARTMENT_ID) REFERENCES DEPARTMENTS(DEPARTMENT_ID) ON DELETE SET NULL
  ); 
  
  
  -- creating indexes:
  
  CREATE UNIQUE INDEX EMP_EMP_ID_PK ON EMPLOYEES (EMPLOYEE_ID ASC);
  CREATE INDEX EMP_MANAGER_IX ON EMPLOYEES (MANAGER_ID ASC);
  CREATE INDEX EMP_DEPARTMENT_IX ON EMPLOYEES (DEPARTMENT_ID ASC);
  CREATE INDEX EMP_JOB_IX ON EMPLOYEES (JOB_ID ASC); 
  CREATE INDEX EMP_NAME_IX ON EMPLOYEES (LAST_NAME ASC, FIRST_NAME ASC); 
  CREATE UNIQUE INDEX EMP_EMAIL_UK ON EMPLOYEES (EMAIL ASC);
  
  CREATE UNIQUE INDEX DEPT_ID_PK ON DEPARTMENTS (DEPARTMENT_ID ASC);
  CREATE INDEX DEPT_LOCATION_IX ON DEPARTMENTS (LOCATION_ID ASC);
  
  CREATE INDEX LOC_CITY_IX ON LOCATIONS (CITY ASC);
  CREATE INDEX LOC_COUNTRY_IX ON LOCATIONS (COUNTRY_ID ASC);
  CREATE INDEX LOC_STATE_PROVINCE_IX ON LOCATIONS (STATE_PROVINCE ASC);
  
  CREATE UNIQUE INDEX REG_ID_PK ON REGIONS (REGION_ID ASC);
  
  CREATE UNIQUE INDEX JOB_ID_PK ON JOBS (JOB_ID ASC); 
  CREATE INDEX JHIST_EMPLOYEE_IX ON JOB_HISTORY (EMPLOYEE_ID ASC);
  CREATE INDEX JHIST_DEPARTMENT_IX ON JOB_HISTORY (DEPARTMENT_ID ASC);
  CREATE UNIQUE INDEX JHIST_EMP_ID_ST_DATE_PK ON JOB_HISTORY (EMPLOYEE_ID ASC, START_DATE ASC);
  CREATE INDEX JHIST_JOB_IX ON JOB_HISTORY (JOB_ID ASC);
  
  
  
  
  
  
  