
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 the employee number, name( first name and last name ), and salary for all employees who earn more than the average salary and who work in a department with any employee with a 'J' in their first name.
SELECT employee_id ,  first_name ,  last_name ,  salary FROM employees WHERE salary  >  ( SELECT AVG (salary) FROM employees ) AND department_id IN ( SELECT department_id FROM employees WHERE first_name LIKE '%J%')
[{'EMPLOYEE_ID': 108, 'FIRST_NAME': 'Nancy', 'LAST_NAME': 'Greenberg', 'SALARY': 12000}, {'EMPLOYEE_ID': 109, 'FIRST_NAME': 'Daniel', 'LAST_NAME': 'Faviet', 'SALARY': 9000}, {'EMPLOYEE_ID': 110, 'FIRST_NAME': 'John', 'LAST_NAME': 'Chen', 'SALARY': 8200}, {'EMPLOYEE_ID': 111, 'FIRST_NAME': 'Ismael', 'LAST_NAME': 'Sciarra', 'SALARY': 7700}, {'EMPLOYEE_ID': 112, 'FIRST_NAME': 'Jose Manuel', 'LAST_NAME': 'Urman', 'SALARY': 7800}, {'EMPLOYEE_ID': 113, 'FIRST_NAME': 'Luis', 'LAST_NAME': 'Popp', 'SALARY': 6900}, {'EMPLOYEE_ID': 120, 'FIRST_NAME': 'Matthew', 'LAST_NAME': 'Weiss', 'SALARY': 8000}, {'EMPLOYEE_ID': 121, 'FIRST_NAME': 'Adam', 'LAST_NAME': 'Fripp', 'SALARY': 8200}, {'EMPLOYEE_ID': 122, 'FIRST_NAME': 'Payam', 'LAST_NAME': 'Kaufling', 'SALARY': 7900}, {'EMPLOYEE_ID': 123, 'FIRST_NAME': 'Shanta', 'LAST_NAME': 'Vollman', 'SALARY': 6500}, {'EMPLOYEE_ID': 145, 'FIRST_NAME': 'John', 'LAST_NAME': 'Russell', 'SALARY': 14000}, {'EMPLOYEE_ID': 146, 'FIRST_NAME': 'Karen', 'LAST_NAME': 'Partners', 'SALARY': 13500}, {'EMPLOYEE_ID': 147, 'FIRST_NAME': 'Alberto', 'LAST_NAME': 'Errazuriz', 'SALARY': 12000}, {'EMPLOYEE_ID': 148, 'FIRST_NAME': 'Gerald', 'LAST_NAME': 'Cambrault', 'SALARY': 11000}, {'EMPLOYEE_ID': 149, 'FIRST_NAME': 'Eleni', 'LAST_NAME': 'Zlotkey', 'SALARY': 10500}, {'EMPLOYEE_ID': 150, 'FIRST_NAME': 'Peter', 'LAST_NAME': 'Tucker', 'SALARY': 10000}, {'EMPLOYEE_ID': 151, 'FIRST_NAME': 'David', 'LAST_NAME': 'Bernstein', 'SALARY': 9500}, {'EMPLOYEE_ID': 152, 'FIRST_NAME': 'Peter', 'LAST_NAME': 'Hall', 'SALARY': 9000}, {'EMPLOYEE_ID': 153, 'FIRST_NAME': 'Christopher', 'LAST_NAME': 'Olsen', 'SALARY': 8000}, {'EMPLOYEE_ID': 154, 'FIRST_NAME': 'Nanette', 'LAST_NAME': 'Cambrault', 'SALARY': 7500}, {'EMPLOYEE_ID': 155, 'FIRST_NAME': 'Oliver', 'LAST_NAME': 'Tuvault', 'SALARY': 7000}, {'EMPLOYEE_ID': 156, 'FIRST_NAME': 'Janette', 'LAST_NAME': 'King', 'SALARY': 10000}, {'EMPLOYEE_ID': 157, 'FIRST_NAME': 'Patrick', 'LAST_NAME': 'Sully', 'SALARY': 9500}, {'EMPLOYEE_ID': 158, 'FIRST_NAME': 'Allan', 'LAST_NAME': 'McEwen', 'SALARY': 9000}, {'EMPLOYEE_ID': 159, 'FIRST_NAME': 'Lindsey', 'LAST_NAME': 'Smith', 'SALARY': 8000}, {'EMPLOYEE_ID': 160, 'FIRST_NAME': 'Louise', 'LAST_NAME': 'Doran', 'SALARY': 7500}, {'EMPLOYEE_ID': 161, 'FIRST_NAME': 'Sarath', 'LAST_NAME': 'Sewall', 'SALARY': 7000}, {'EMPLOYEE_ID': 162, 'FIRST_NAME': 'Clara', 'LAST_NAME': 'Vishney', 'SALARY': 10500}, {'EMPLOYEE_ID': 163, 'FIRST_NAME': 'Danielle', 'LAST_NAME': 'Greene', 'SALARY': 9500}, {'EMPLOYEE_ID': 164, 'FIRST_NAME': 'Mattea', 'LAST_NAME': 'Marvins', 'SALARY': 7200}, {'EMPLOYEE_ID': 165, 'FIRST_NAME': 'David', 'LAST_NAME': 'Lee', 'SALARY': 6800}, {'EMPLOYEE_ID': 168, 'FIRST_NAME': 'Lisa', 'LAST_NAME': 'Ozer', 'SALARY': 11500}, {'EMPLOYEE_ID': 169, 'FIRST_NAME': 'Harrison', 'LAST_NAME': 'Bloom', 'SALARY': 10000}, {'EMPLOYEE_ID': 170, 'FIRST_NAME': 'Tayler', 'LAST_NAME': 'Fox', 'SALARY': 9600}, {'EMPLOYEE_ID': 171, 'FIRST_NAME': 'William', 'LAST_NAME': 'Smith', 'SALARY': 7400}, {'EMPLOYEE_ID': 172, 'FIRST_NAME': 'Elizabeth', 'LAST_NAME': 'Bates', 'SALARY': 7300}, {'EMPLOYEE_ID': 174, 'FIRST_NAME': 'Ellen', 'LAST_NAME': 'Abel', 'SALARY': 11000}, {'EMPLOYEE_ID': 175, 'FIRST_NAME': 'Alyssa', 'LAST_NAME': 'Hutton', 'SALARY': 8800}, {'EMPLOYEE_ID': 176, 'FIRST_NAME': 'Jonathon', 'LAST_NAME': 'Taylor', 'SALARY': 8600}, {'EMPLOYEE_ID': 177, 'FIRST_NAME': 'Jack', 'LAST_NAME': 'Livingston', 'SALARY': 8400}]