
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 emails of the employees who have no commission percentage and salary within the range 7000 to 12000 and works in that department which number is 50.
SELECT email FROM employees WHERE commission_pct  =  "null" AND salary BETWEEN 7000 AND 12000 AND department_id = 50
[{'Nenhum': 'Nenhum resultado encontrado'}]