
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
*/
What are all the employee ids and the names of the countries in which they work?
SELECT T1.employee_id ,  T4.country_name FROM employees AS T1 JOIN departments AS T2 ON T1.department_id  =  T2.department_id JOIN locations AS T3 ON T2.location_id  =  T3.location_id JOIN countries AS T4 ON T3.country_id  =  T4.country_id
[{'EMPLOYEE_ID': 100, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 101, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 102, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 103, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 104, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 105, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 106, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 107, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 108, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 109, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 110, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 111, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 112, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 113, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 114, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 115, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 116, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 117, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 118, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 119, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 120, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 121, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 122, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 123, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 124, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 125, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 126, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 127, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 128, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 129, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 130, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 131, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 132, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 133, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 134, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 135, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 136, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 137, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 138, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 139, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 140, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 141, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 142, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 143, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 144, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 180, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 181, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 182, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 183, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 184, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 185, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 186, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 187, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 188, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 189, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 190, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 191, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 192, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 193, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 194, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 195, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 196, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 197, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 198, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 199, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 200, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 201, 'COUNTRY_NAME': 'Canada'}, {'EMPLOYEE_ID': 202, 'COUNTRY_NAME': 'Canada'}, {'EMPLOYEE_ID': 203, 'COUNTRY_NAME': 'United Kingdom'}, {'EMPLOYEE_ID': 204, 'COUNTRY_NAME': 'Germany'}, {'EMPLOYEE_ID': 205, 'COUNTRY_NAME': 'United States of America'}, {'EMPLOYEE_ID': 206, 'COUNTRY_NAME': 'United States of America'}]