
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
*/
display those employees who joined after 7th September, 1987.
SELECT * FROM employees WHERE hire_date  >  '1987-09-07'
[{'EMPLOYEE_ID': 183, 'FIRST_NAME': 'Girard', 'LAST_NAME': 'Geoni', 'EMAIL': 'GGEONI', 'PHONE_NUMBER': '650.507.9879', 'HIRE_DATE': '1987-09-08', 'JOB_ID': 'SH_CLERK', 'SALARY': 2800, 'COMMISSION_PCT': 0, 'MANAGER_ID': 120, 'DEPARTMENT_ID': 50}, {'EMPLOYEE_ID': 184, 'FIRST_NAME': 'Nandita', 'LAST_NAME': 'Sarchand', 'EMAIL': 'NSARCHAN', 'PHONE_NUMBER': '650.509.1876', 'HIRE_DATE': '1987-09-09', 'JOB_ID': 'SH_CLERK', 'SALARY': 4200, 'COMMISSION_PCT': 0, 'MANAGER_ID': 121, 'DEPARTMENT_ID': 50}, {'EMPLOYEE_ID': 185, 'FIRST_NAME': 'Alexis', 'LAST_NAME': 'Bull', 'EMAIL': 'ABULL', 'PHONE_NUMBER': '650.509.2876', 'HIRE_DATE': '1987-09-10', 'JOB_ID': 'SH_CLERK', 'SALARY': 4100, 'COMMISSION_PCT': 0, 'MANAGER_ID': 121, 'DEPARTMENT_ID': 50}, {'EMPLOYEE_ID': 186, 'FIRST_NAME': 'Julia', 'LAST_NAME': 'Dellinger', 'EMAIL': 'JDELLING', 'PHONE_NUMBER': '650.509.3876', 'HIRE_DATE': '1987-09-11', 'JOB_ID': 'SH_CLERK', 'SALARY': 3400, 'COMMISSION_PCT': 0, 'MANAGER_ID': 121, 'DEPARTMENT_ID': 50}, {'EMPLOYEE_ID': 187, 'FIRST_NAME': 'Anthony', 'LAST_NAME': 'Cabrio', 'EMAIL': 'ACABRIO', 'PHONE_NUMBER': '650.509.4876', 'HIRE_DATE': '1987-09-12', 'JOB_ID': 'SH_CLERK', 'SALARY': 3000, 'COMMISSION_PCT': 0, 'MANAGER_ID': 121, 'DEPARTMENT_ID': 50}, {'EMPLOYEE_ID': 188, 'FIRST_NAME': 'Kelly', 'LAST_NAME': 'Chung', 'EMAIL': 'KCHUNG', 'PHONE_NUMBER': '650.505.1876', 'HIRE_DATE': '1987-09-13', 'JOB_ID': 'SH_CLERK', 'SALARY': 3800, 'COMMISSION_PCT': 0, 'MANAGER_ID': 122, 'DEPARTMENT_ID': 50}, {'EMPLOYEE_ID': 189, 'FIRST_NAME': 'Jennifer', 'LAST_NAME': 'Dilly', 'EMAIL': 'JDILLY', 'PHONE_NUMBER': '650.505.2876', 'HIRE_DATE': '1987-09-14', 'JOB_ID': 'SH_CLERK', 'SALARY': 3600, 'COMMISSION_PCT': 0, 'MANAGER_ID': 122, 'DEPARTMENT_ID': 50}, {'EMPLOYEE_ID': 190, 'FIRST_NAME': 'Timothy', 'LAST_NAME': 'Gates', 'EMAIL': 'TGATES', 'PHONE_NUMBER': '650.505.3876', 'HIRE_DATE': '1987-09-15', 'JOB_ID': 'SH_CLERK', 'SALARY': 2900, 'COMMISSION_PCT': 0, 'MANAGER_ID': 122, 'DEPARTMENT_ID': 50}, {'EMPLOYEE_ID': 191, 'FIRST_NAME': 'Randall', 'LAST_NAME': 'Perkins', 'EMAIL': 'RPERKINS', 'PHONE_NUMBER': '650.505.4876', 'HIRE_DATE': '1987-09-16', 'JOB_ID': 'SH_CLERK', 'SALARY': 2500, 'COMMISSION_PCT': 0, 'MANAGER_ID': 122, 'DEPARTMENT_ID': 50}, {'EMPLOYEE_ID': 192, 'FIRST_NAME': 'Sarah', 'LAST_NAME': 'Bell', 'EMAIL': 'SBELL', 'PHONE_NUMBER': '650.501.1876', 'HIRE_DATE': '1987-09-17', 'JOB_ID': 'SH_CLERK', 'SALARY': 4000, 'COMMISSION_PCT': 0, 'MANAGER_ID': 123, 'DEPARTMENT_ID': 50}, {'EMPLOYEE_ID': 193, 'FIRST_NAME': 'Britney', 'LAST_NAME': 'Everett', 'EMAIL': 'BEVERETT', 'PHONE_NUMBER': '650.501.2876', 'HIRE_DATE': '1987-09-18', 'JOB_ID': 'SH_CLERK', 'SALARY': 3900, 'COMMISSION_PCT': 0, 'MANAGER_ID': 123, 'DEPARTMENT_ID': 50}, {'EMPLOYEE_ID': 194, 'FIRST_NAME': 'Samuel', 'LAST_NAME': 'McCain', 'EMAIL': 'SMCCAIN', 'PHONE_NUMBER': '650.501.3876', 'HIRE_DATE': '1987-09-19', 'JOB_ID': 'SH_CLERK', 'SALARY': 3200, 'COMMISSION_PCT': 0, 'MANAGER_ID': 123, 'DEPARTMENT_ID': 50}, {'EMPLOYEE_ID': 195, 'FIRST_NAME': 'Vance', 'LAST_NAME': 'Jones', 'EMAIL': 'VJONES', 'PHONE_NUMBER': '650.501.4876', 'HIRE_DATE': '1987-09-20', 'JOB_ID': 'SH_CLERK', 'SALARY': 2800, 'COMMISSION_PCT': 0, 'MANAGER_ID': 123, 'DEPARTMENT_ID': 50}, {'EMPLOYEE_ID': 196, 'FIRST_NAME': 'Alana', 'LAST_NAME': 'Walsh', 'EMAIL': 'AWALSH', 'PHONE_NUMBER': '650.507.9811', 'HIRE_DATE': '1987-09-21', 'JOB_ID': 'SH_CLERK', 'SALARY': 3100, 'COMMISSION_PCT': 0, 'MANAGER_ID': 124, 'DEPARTMENT_ID': 50}, {'EMPLOYEE_ID': 197, 'FIRST_NAME': 'Kevin', 'LAST_NAME': 'Feeney', 'EMAIL': 'KFEENEY', 'PHONE_NUMBER': '650.507.9822', 'HIRE_DATE': '1987-09-22', 'JOB_ID': 'SH_CLERK', 'SALARY': 3000, 'COMMISSION_PCT': 0, 'MANAGER_ID': 124, 'DEPARTMENT_ID': 50}, {'EMPLOYEE_ID': 198, 'FIRST_NAME': 'Donald', 'LAST_NAME': 'OConnell', 'EMAIL': 'DOCONNEL', 'PHONE_NUMBER': '650.507.9833', 'HIRE_DATE': '1987-09-23', 'JOB_ID': 'SH_CLERK', 'SALARY': 2600, 'COMMISSION_PCT': 0, 'MANAGER_ID': 124, 'DEPARTMENT_ID': 50}, {'EMPLOYEE_ID': 199, 'FIRST_NAME': 'Douglas', 'LAST_NAME': 'Grant', 'EMAIL': 'DGRANT', 'PHONE_NUMBER': '650.507.9844', 'HIRE_DATE': '1987-09-24', 'JOB_ID': 'SH_CLERK', 'SALARY': 2600, 'COMMISSION_PCT': 0, 'MANAGER_ID': 124, 'DEPARTMENT_ID': 50}, {'EMPLOYEE_ID': 200, 'FIRST_NAME': 'Jennifer', 'LAST_NAME': 'Whalen', 'EMAIL': 'JWHALEN', 'PHONE_NUMBER': '515.123.4444', 'HIRE_DATE': '1987-09-25', 'JOB_ID': 'AD_ASST', 'SALARY': 4400, 'COMMISSION_PCT': 0, 'MANAGER_ID': 101, 'DEPARTMENT_ID': 10}, {'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}]