
CREATE TABLE countries (
	"COUNTRY_ID" VARCHAR(2) NOT NULL, 
	"COUNTRY_NAME" VARCHAR(40) DEFAULT NULL, 
	"REGION_ID" DECIMAL(10, 0) DEFAULT NULL, 
	PRIMARY KEY ("COUNTRY_ID"), 
	FOREIGN KEY("REGION_ID") REFERENCES regions ("REGION_ID")
)


CREATE TABLE departments (
	"DEPARTMENT_ID" DECIMAL(4, 0) DEFAULT '0' NOT NULL, 
	"DEPARTMENT_NAME" VARCHAR(30) NOT NULL, 
	"MANAGER_ID" DECIMAL(6, 0) DEFAULT NULL, 
	"LOCATION_ID" DECIMAL(4, 0) DEFAULT NULL, 
	PRIMARY KEY ("DEPARTMENT_ID")
)


CREATE TABLE employees (
	"EMPLOYEE_ID" DECIMAL(6, 0) DEFAULT '0' NOT NULL, 
	"FIRST_NAME" VARCHAR(20) DEFAULT NULL, 
	"LAST_NAME" VARCHAR(25) NOT NULL, 
	"EMAIL" VARCHAR(25) NOT NULL, 
	"PHONE_NUMBER" VARCHAR(20) DEFAULT NULL, 
	"HIRE_DATE" DATE NOT NULL, 
	"JOB_ID" VARCHAR(10) NOT NULL, 
	"SALARY" DECIMAL(8, 2) DEFAULT NULL, 
	"COMMISSION_PCT" DECIMAL(2, 2) DEFAULT NULL, 
	"MANAGER_ID" DECIMAL(6, 0) DEFAULT NULL, 
	"DEPARTMENT_ID" DECIMAL(4, 0) DEFAULT NULL, 
	PRIMARY KEY ("EMPLOYEE_ID"), 
	FOREIGN KEY("JOB_ID") REFERENCES jobs ("JOB_ID"), 
	FOREIGN KEY("DEPARTMENT_ID") REFERENCES departments ("DEPARTMENT_ID")
)


CREATE TABLE job_history (
	"EMPLOYEE_ID" DECIMAL(6, 0) NOT NULL, 
	"START_DATE" DATE NOT NULL, 
	"END_DATE" DATE NOT NULL, 
	"JOB_ID" VARCHAR(10) NOT NULL, 
	"DEPARTMENT_ID" DECIMAL(4, 0) DEFAULT NULL, 
	PRIMARY KEY ("EMPLOYEE_ID", "START_DATE"), 
	FOREIGN KEY("JOB_ID") REFERENCES jobs ("JOB_ID"), 
	FOREIGN KEY("DEPARTMENT_ID") REFERENCES departments ("DEPARTMENT_ID"), 
	FOREIGN KEY("EMPLOYEE_ID") REFERENCES employees ("EMPLOYEE_ID")
)


CREATE TABLE jobs (
	"JOB_ID" VARCHAR(10) DEFAULT '' NOT NULL, 
	"JOB_TITLE" VARCHAR(35) NOT NULL, 
	"MIN_SALARY" DECIMAL(6, 0) DEFAULT NULL, 
	"MAX_SALARY" DECIMAL(6, 0) DEFAULT NULL, 
	PRIMARY KEY ("JOB_ID")
)


CREATE TABLE locations (
	"LOCATION_ID" DECIMAL(4, 0) DEFAULT '0' NOT NULL, 
	"STREET_ADDRESS" VARCHAR(40) DEFAULT NULL, 
	"POSTAL_CODE" VARCHAR(12) DEFAULT NULL, 
	"CITY" VARCHAR(30) NOT NULL, 
	"STATE_PROVINCE" VARCHAR(25) DEFAULT NULL, 
	"COUNTRY_ID" VARCHAR(2) DEFAULT NULL, 
	PRIMARY KEY ("LOCATION_ID"), 
	FOREIGN KEY("COUNTRY_ID") REFERENCES countries ("COUNTRY_ID")
)


CREATE TABLE regions (
	"REGION_ID" DECIMAL(5, 0) NOT NULL, 
	"REGION_NAME" VARCHAR(25) DEFAULT NULL, 
	PRIMARY KEY ("REGION_ID")
)

CREATE TABLE countries (
	"COUNTRY_ID" VARCHAR(2) NOT NULL, 
	"COUNTRY_NAME" VARCHAR(40) DEFAULT NULL, 
	"REGION_ID" DECIMAL(10, 0) DEFAULT NULL, 
	PRIMARY KEY ("COUNTRY_ID"), 
	FOREIGN KEY("REGION_ID") REFERENCES regions ("REGION_ID")
)

/*
2 rows from countries table:
COUNTRY_ID	COUNTRY_NAME	REGION_ID
AR	Argentina	2
AU	Australia	3
*/


CREATE TABLE departments (
	"DEPARTMENT_ID" DECIMAL(4, 0) DEFAULT '0' NOT NULL, 
	"DEPARTMENT_NAME" VARCHAR(30) NOT NULL, 
	"MANAGER_ID" DECIMAL(6, 0) DEFAULT NULL, 
	"LOCATION_ID" DECIMAL(4, 0) DEFAULT NULL, 
	PRIMARY KEY ("DEPARTMENT_ID")
)

/*
2 rows from departments table:
DEPARTMENT_ID	DEPARTMENT_NAME	MANAGER_ID	LOCATION_ID
10	Administration	200	1700
20	Marketing	201	1800
*/


CREATE TABLE employees (
	"EMPLOYEE_ID" DECIMAL(6, 0) DEFAULT '0' NOT NULL, 
	"FIRST_NAME" VARCHAR(20) DEFAULT NULL, 
	"LAST_NAME" VARCHAR(25) NOT NULL, 
	"EMAIL" VARCHAR(25) NOT NULL, 
	"PHONE_NUMBER" VARCHAR(20) DEFAULT NULL, 
	"HIRE_DATE" DATE NOT NULL, 
	"JOB_ID" VARCHAR(10) NOT NULL, 
	"SALARY" DECIMAL(8, 2) DEFAULT NULL, 
	"COMMISSION_PCT" DECIMAL(2, 2) DEFAULT NULL, 
	"MANAGER_ID" DECIMAL(6, 0) DEFAULT NULL, 
	"DEPARTMENT_ID" DECIMAL(4, 0) DEFAULT NULL, 
	PRIMARY KEY ("EMPLOYEE_ID"), 
	FOREIGN KEY("JOB_ID") REFERENCES jobs ("JOB_ID"), 
	FOREIGN KEY("DEPARTMENT_ID") REFERENCES departments ("DEPARTMENT_ID")
)

/*
2 rows from employees table:
EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	HIRE_DATE	JOB_ID	SALARY	COMMISSION_PCT	MANAGER_ID	DEPARTMENT_ID
100	Steven	King	SKING	515.123.4567	1987-06-17	AD_PRES	24000.00	0.00	0	90
101	Neena	Kochhar	NKOCHHAR	515.123.4568	1987-06-18	AD_VP	17000.00	0.00	100	90
*/


CREATE TABLE job_history (
	"EMPLOYEE_ID" DECIMAL(6, 0) NOT NULL, 
	"START_DATE" DATE NOT NULL, 
	"END_DATE" DATE NOT NULL, 
	"JOB_ID" VARCHAR(10) NOT NULL, 
	"DEPARTMENT_ID" DECIMAL(4, 0) DEFAULT NULL, 
	PRIMARY KEY ("EMPLOYEE_ID", "START_DATE"), 
	FOREIGN KEY("JOB_ID") REFERENCES jobs ("JOB_ID"), 
	FOREIGN KEY("DEPARTMENT_ID") REFERENCES departments ("DEPARTMENT_ID"), 
	FOREIGN KEY("EMPLOYEE_ID") REFERENCES employees ("EMPLOYEE_ID")
)

/*
2 rows from job_history table:
EMPLOYEE_ID	START_DATE	END_DATE	JOB_ID	DEPARTMENT_ID
102	1993-01-13	1998-07-24	IT_PROG	60
101	1989-09-21	1993-10-27	AC_ACCOUNT	110
*/


CREATE TABLE jobs (
	"JOB_ID" VARCHAR(10) DEFAULT '' NOT NULL, 
	"JOB_TITLE" VARCHAR(35) NOT NULL, 
	"MIN_SALARY" DECIMAL(6, 0) DEFAULT NULL, 
	"MAX_SALARY" DECIMAL(6, 0) DEFAULT NULL, 
	PRIMARY KEY ("JOB_ID")
)

/*
2 rows from jobs table:
JOB_ID	JOB_TITLE	MIN_SALARY	MAX_SALARY
AD_PRES	President	20000	40000
AD_VP	Administration Vice President	15000	30000
*/


CREATE TABLE locations (
	"LOCATION_ID" DECIMAL(4, 0) DEFAULT '0' NOT NULL, 
	"STREET_ADDRESS" VARCHAR(40) DEFAULT NULL, 
	"POSTAL_CODE" VARCHAR(12) DEFAULT NULL, 
	"CITY" VARCHAR(30) NOT NULL, 
	"STATE_PROVINCE" VARCHAR(25) DEFAULT NULL, 
	"COUNTRY_ID" VARCHAR(2) DEFAULT NULL, 
	PRIMARY KEY ("LOCATION_ID"), 
	FOREIGN KEY("COUNTRY_ID") REFERENCES countries ("COUNTRY_ID")
)

/*
2 rows from locations table:
LOCATION_ID	STREET_ADDRESS	POSTAL_CODE	CITY	STATE_PROVINCE	COUNTRY_ID
1000	1297 Via Cola di Rie	989	Roma		IT
1100	93091 Calle della Testa	10934	Venice		IT
*/


CREATE TABLE regions (
	"REGION_ID" DECIMAL(5, 0) NOT NULL, 
	"REGION_NAME" VARCHAR(25) DEFAULT NULL, 
	PRIMARY KEY ("REGION_ID")
)

/*
2 rows from regions table:
REGION_ID	REGION_NAME
1	Europe\r
2	Americas\r
*/
Find the ids of the employees who does not work in those departments where some employees works whose manager id within the range 100 and 200.
SELECT * FROM employees WHERE department_id NOT IN (SELECT department_id FROM departments WHERE manager_id BETWEEN 100 AND 200)
[{'EMPLOYEE_ID': 178, 'FIRST_NAME': 'Kimberely', 'LAST_NAME': 'Grant', 'EMAIL': 'KGRANT', 'PHONE_NUMBER': '011.44.1644.429263', 'HIRE_DATE': '1987-09-03', 'JOB_ID': 'SA_REP', 'SALARY': 7000, 'COMMISSION_PCT': 0.15, 'MANAGER_ID': 149, 'DEPARTMENT_ID': 0}, {'EMPLOYEE_ID': 201, 'FIRST_NAME': 'Michael', 'LAST_NAME': 'Hartstein', 'EMAIL': 'MHARTSTE', 'PHONE_NUMBER': '515.123.5555', 'HIRE_DATE': '1987-09-26', 'JOB_ID': 'MK_MAN', 'SALARY': 13000, 'COMMISSION_PCT': 0, 'MANAGER_ID': 100, 'DEPARTMENT_ID': 20}, {'EMPLOYEE_ID': 202, 'FIRST_NAME': 'Pat', 'LAST_NAME': 'Fay', 'EMAIL': 'PFAY', 'PHONE_NUMBER': '603.123.6666', 'HIRE_DATE': '1987-09-27', 'JOB_ID': 'MK_REP', 'SALARY': 6000, 'COMMISSION_PCT': 0, 'MANAGER_ID': 201, 'DEPARTMENT_ID': 20}, {'EMPLOYEE_ID': 203, 'FIRST_NAME': 'Susan', 'LAST_NAME': 'Mavris', 'EMAIL': 'SMAVRIS', 'PHONE_NUMBER': '515.123.7777', 'HIRE_DATE': '1987-09-28', 'JOB_ID': 'HR_REP', 'SALARY': 6500, 'COMMISSION_PCT': 0, 'MANAGER_ID': 101, 'DEPARTMENT_ID': 40}, {'EMPLOYEE_ID': 204, 'FIRST_NAME': 'Hermann', 'LAST_NAME': 'Baer', 'EMAIL': 'HBAER', 'PHONE_NUMBER': '515.123.8888', 'HIRE_DATE': '1987-09-29', 'JOB_ID': 'PR_REP', 'SALARY': 10000, 'COMMISSION_PCT': 0, 'MANAGER_ID': 101, 'DEPARTMENT_ID': 70}, {'EMPLOYEE_ID': 205, 'FIRST_NAME': 'Shelley', 'LAST_NAME': 'Higgins', 'EMAIL': 'SHIGGINS', 'PHONE_NUMBER': '515.123.8080', 'HIRE_DATE': '1987-09-30', 'JOB_ID': 'AC_MGR', 'SALARY': 12000, 'COMMISSION_PCT': 0, 'MANAGER_ID': 101, 'DEPARTMENT_ID': 110}, {'EMPLOYEE_ID': 206, 'FIRST_NAME': 'William', 'LAST_NAME': 'Gietz', 'EMAIL': 'WGIETZ', 'PHONE_NUMBER': '515.123.8181', 'HIRE_DATE': '1987-10-01', 'JOB_ID': 'AC_ACCOUNT', 'SALARY': 8300, 'COMMISSION_PCT': 0, 'MANAGER_ID': 205, 'DEPARTMENT_ID': 110}]