DROP TABLE REGIONS CASCADE CONSTRAINT;
                                    /*REGIONS*/
CREATE TABLE REGIONS
(region_id NUMBER NOT NULL,
region_name VARCHAR(25),
CONSTRAINT pk_region_id PRIMARY KEY (region_id)
);

DROP TABLE JOBS CASCADE CONSTRAINT;
                                    /*JOBS*/
CREATE TABLE JOBS
(job_id VARCHAR(10) NOT NULL,
job_title VARCHAR(35) NOT NULL,
min_salary NUMBER(6),
max_salary NUMBER(6),
CONSTRAINT pk_job_id PRIMARY KEY (job_id)
);

DROP TABLE COUNTRIES CASCADE CONSTRAINT;
                                    /*COUNTRIES*/
CREATE TABLE COUNTRIES
(country_id CHAR(2) NOT NULL,
country_name VARCHAR2 (40),
region_id NUMBER,
CONSTRAINT pk_regions PRIMARY KEY(country_id),
CONSTRAINT fk_region_id FOREIGN KEY (region_id)
    REFERENCES REGIONS(region_id)
);

DROP TABLE LOCATIONS CASCADE CONSTRAINT;
                                    /*LOCATIONS*/
CREATE TABLE LOCATIONS
(location_id NUMBER(4) NOT NULL,
street_address VARCHAR2(40),
postal_code VARCHAR2(12),
city VARCHAR2 (30) NOT NULL,
state_province VARCHAR2 (25),
country_id CHAR(2),
CONSTRAINT pk_location_id PRIMARY KEY (location_id),
CONSTRAINT fk_country_id FOREIGN KEY (country_id)
    REFERENCES COUNTRIES(country_id)
);


DROP TABLE DEPARTMENTS CASCADE CONSTRAINT;
                                    /*DEPARTMENTS*/
CREATE TABLE DEPARTMENTS
(department_id NUMBER(4) NOT NULL,
department_name VARCHAR(30) NOT NULL,
manager_id NUMBER (5),
location_id NUMBER (4),
CONSTRAINT pk_department_id PRIMARY KEY (department_id),
CONSTRAINT fk_location_id FOREIGN KEY (location_id)
    REFERENCES LOCATIONS(location_id)
);


DROP TABLE EMPLOYEES CASCADE CONSTRAINT;

CREATE TABLE EMPLOYEES
(emloyee_id NUMBER (6) NOT NULL,
first_name VARCHAR2 (20),
last_name VARCHAR2 (25) NOT NULL,
email VARCHAR2 (25) NOT NULL,
phone_number VARCHAR2(20),
hire_date DATE NOT NULL,
job_id VARCHAR2 (10) NOT NULL,
salary NUMBER (8),
comission_pct NUMBER (4),
manager_id NUMBER (6),
department_id NUMBER (4),
CONSTRAINT pk_emloyee_id PRIMARY KEY (emloyee_id),
CONSTRAINT fk_job_id FOREIGN KEY (job_id)
    REFERENCES JOBS(job_id),
CONSTRAINT fk_department_id FOREIGN KEY (department_id)
    REFERENCES DEPARTMENTS(department_id)
);


/*DROP TABLE JOB_HISTORY CASCADE CONSTRAINT;

CREATE TABLE JOB_HISTORY
(employee_id NUMBER (6) NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
job_id VARCHAR2 (10) NOT NULL,
department_id NUMBER (4),
PRIMARY KEY (employee_id, start_date),
CONSTRAINT fk_employee_id FOREIGN KEY (employee_id)
    REFERENCES EMPLOYEES(employee_id), ???????
CONSTRAINT fk_job_id FOREIGN KEY (job_id)
    REFERENCES JOBS(job_id),
CONSTRAINT fk_department_id FOREIGN KEY (department_id)
    REFERENCES DEPARTMENTS(department_id)
);
*/
TRUNCATE TABLE REGIONS;


INSERT INTO regions
VALUES (1,'Boston1');
INSERT INTO regions
VALUES (2,'Boston2');
INSERT INTO regions
VALUES (3,'Boston3');
INSERT INTO regions
VALUES (4,'Boston4');
INSERT INTO regions
VALUES (5,'Boston5');
INSERT INTO regions
VALUES (6,'Boston6');
INSERT INTO regions
VALUES (7,'Boston7');
INSERT INTO regions
VALUES (8,'Boston8');
INSERT INTO regions
VALUES (9,'Boston9');
INSERT INTO regions
VALUES (10,'Boston10');
select * from regions;


INSERT INTO COUNTRIES
VALUES (1,'Cambridge1',1);
INSERT INTO COUNTRIES
VALUES (2,'Cambridge2',2);
INSERT INTO COUNTRIES
VALUES (3,'Cambridge3',3);
INSERT INTO COUNTRIES
VALUES (4,'Cambridge4',4);
INSERT INTO COUNTRIES
VALUES (5,'Cambridge5',5);
INSERT INTO COUNTRIES
VALUES (6,'Cambridge6',6);
INSERT INTO COUNTRIES
VALUES (7,'Cambridge7',7);
INSERT INTO COUNTRIES
VALUES (8,'Cambridge8',8);
INSERT INTO COUNTRIES
VALUES (9,'Cambridge9',9);
INSERT INTO COUNTRIES
VALUES (10,'Cambridge10',10);

select * from countries;


INSERT INTO locations
VALUES (1,'Red street 1',1,Kharkiv1,Beedichev1,1);
INSERT INTO LOCATIONS
VALUES (2,'Red street 2',2,Kharkiv2,Beedichev2,2);
INSERT INTO LOCATIONS
VALUES (3,'Red street 3',3,Kharkiv3,Beedichev3,3);
INSERT INTO LOCATIONS
VALUES (4,'Red street 4',4,Kharkiv4,Beedichev4,4);
INSERT INTO LOCATIONS
VALUES (5,'Red street 5',5,Kharkiv5,Beedichev5,5);
INSERT INTO LOCATIONS
VALUES (6,'Red street 6',6,Kharkiv6,Beedichev6,6);
INSERT INTO LOCATIONS
VALUES (7,'Red street 7',7,Kharkiv7,Beedichev7,7);
INSERT INTO LOCATIONS
VALUES (8,'Red street 8',8,Kharkiv8,Beedichev8,8);
INSERT INTO LOCATIONS
VALUES (9,'Red street 9',9,Kharkiv9,Beedichev9,9);
INSERT INTO LOCATIONS
VALUES (10,'Red street 10',10,Kharkiv10,Beedichev10,10);

select * from LOCATIONS;


INSERT INTO DEPARTMENTS
VALUES (1,'documentary1',1,1);
INSERT INTO DEPARTMENTS
VALUES (2,'documentary2',2,2);
INSERT INTO DEPARTMENTS
VALUES (3,'documentary3',3,3);
INSERT INTO DEPARTMENTS
VALUES (4,'documentary4',4,4);
INSERT INTO DEPARTMENTS
VALUES (5,'documentary5',5,5);
INSERT INTO DEPARTMENTS
VALUES (6,'documentary6',6,6);
INSERT INTO DEPARTMENTS
VALUES (7,'documentary7',7,7);
INSERT INTO DEPARTMENTS
VALUES (8,'documentary8',8,8);
INSERT INTO DEPARTMENTS
VALUES (9,'documentary9',9,9);
INSERT INTO DEPARTMENTS
VALUES (10,'documentary10',10,10);
SELECT * FROM departments;

INSERT INTO jobs
VALUES (1,'developer1',10000,120000);
INSERT INTO jobs
VALUES (2,'developer2',8000,80000);
INSERT INTO jobs
VALUES (3,'developer3',10000,130000);
INSERT INTO jobs
VALUES (4,'developer4',7000,90000);
INSERT INTO jobs
VALUES (5,'developer5',5000,70000);
INSERT INTO jobs
VALUES (6,'developer6',13000,100000);
INSERT INTO jobs
VALUES (7,'developer7',12000,140000);
INSERT INTO jobs
VALUES (8,'developer8',9500,70000);
INSERT INTO jobs
VALUES (9,'developer9',15000,90000);
INSERT INTO jobs
VALUES (10,'developer10',4000,110000);

INSERT INTO employees
VALUES (1,'John1','Smith1','@gmail.com',111111,'2016-03-11',1,10000,0.22,1,1);
INSERT INTO employees
VALUES (2,'John2','Smith2','2@gmail.com',222222,'2016-04-01',2,10000,0.24,2,2);
INSERT INTO employees
VALUES (3,'John3','Smith3','3@gmail.com',333333,'2016-05-02',3,10000,0.19,3,3);
INSERT INTO employees
VALUES (4,'John4','Smith4','4@gmail.com',444444,'2016-06-03',4,10000,0.21,4,4);
INSERT INTO employees
VALUES (5,'John5','Smith5','5@gmail.com',555555,'2015-07-04',5,10000,0.15,5,5);
INSERT INTO employees
VALUES (6,'John6','Smith6','6@gmail.com',666666,'2016-08-05',6,10000,0.17,6,6);
INSERT INTO employees
VALUES (7,'John7','Smith7','7@gmail.com',777777,'2016-09-06',7,10000,0.19,7,7);
INSERT INTO employees
VALUES (8,'John8','Smith8','8@gmail.com',888888,'2016-10-07',8,10000,0.2,8,8);
INSERT INTO employees
VALUES (9,'John9','Smith9','9@gmail.com',999999,'2016-11-08',9,10000,0.25,9,9);
INSERT INTO employees
VALUES (10,'John10','Smith10','10@gmail.com',000000,'2016-12-09',10,10000,0.26,10,10);

select * from employees;

INSERT INTO job_history
VALUES (1,'2016-03-11','2017-03-11',1,1);
INSERT INTO job_history
VALUES (2,'2016-04-01','2017-04-01',2,2);
INSERT INTO job_history
VALUES (3,'2016-05-02','2017-05-02',3,3);
INSERT INTO job_history
VALUES (4,'2016-06-03','2017-06-03',4,4);
INSERT INTO job_history
VALUES (5,'2015-07-04','2016-07-04',5,5);
INSERT INTO job_history
VALUES (6,'2016-08-05','2017-08-05',6,6);
INSERT INTO job_history
VALUES (7,'2016-09-06','2017-09-06',7,7);
INSERT INTO job_history
VALUES (8,'2016-10-07','2017-10-07',8,8);
INSERT INTO job_history
VALUES (9,'2016-11-08','2017-11-08',9,9);
INSERT INTO job_history
VALUES (10,'2016-12-09','2017-12-09',10,10);