Untitled

CREATE TABLE DEPARTMENTS_ALGI(
DEPARTMENT_ALGI_ID NUMBER (4,0),
DEPARTMENT_ALGI_NAME VARCHAR2 (40),
MANAGER_ALGI_ID NUMBER (4,0),
LOCATION_ALGI_ID NUMBER (4,0),
PRIMARY KEY(DEPARTMENT_ALGI_ID));

CREATE TABLE EMPLOYEES_ALGI(
EMPLOYEE_ALGI_ID NUMBER (5,0),
FIRST_NAME VARCHAR2 (20),
LAST_NAME VARCHAR2 (30),
EMAIL VARCHAR2 (40),
PHONE_NUMBER VARCHAR2 (25),
HIRE_DATE DATE,
JOB_ALGI_ID NUMBER (5,0) NOT NULL,
SALARY NUMBER (7,2),
MANAGER_ALGI_ID NUMBER (4,0) NOT NULL,
DEPARTMENT_ALGI_ID NUMBER (4,0) NOT NULL,
PRIMARY KEY (DEPARTMENT_ALGI_ID, MANAGER_ALGI_ID, JOB_ALGI_ID),
FOREIGN KEY (DEPARTMENT_ALGI_ID) REFERENCES DEPARTMENTS_ALGI(DEPARTMENT_ALGI_ID));

CREATE TABLE LOCATIONS_ALGI(
LOCATION_ALGI_ID NUMBER (4,0),
STREET_ADDRESS VARCHAR2 (50),
POSTAL_CODE VARCHAR2 (15),
CITY VARCHAR2 (25),
STATE_PROVINCE_ID NUMBER (3,0));

CREATE TABLE JOBS_ALGI(
JOB_ALGI_ID NUMBER (5,0),
JOB_TITLE VARCHAR2 (35),
MIN_SALARY NUMBER (6,2),
MAX_SALARY NUMBER (7,2));

CREATE TABLE STATE_PROVINCE_ALGI(
STATE_PROVINCE_ID NUMBER (3,0),
STATE_PROVINCE_NAME VARCHAR (30),
PRIMARY KEY (STATE_PROVINCE_ID));

ALTER TABLE LOCATIONS_ALGI
ADD PRIMARY KEY (LOCATION_ALGI_ID);

ALTER TABLE JOBS_ALGI
ADD PRIMARY KEY (JOB_ALGI_ID);

INSERT INTO STATE_PROVINCE_ALGI
VALUES (1, 'Adjara');

INSERT INTO STATE_PROVINCE_ALGI
VALUES (2, 'Guria');

INSERT INTO STATE_PROVINCE_ALGI
VALUES (3, 'Samegrelo');

INSERT INTO STATE_PROVINCE_ALGI
VALUES (4, 'Shida kartli');

INSERT INTO STATE_PROVINCE_ALGI
VALUES (5, 'Kakheti');

INSERT INTO LOCATIONS_ALGI
VALUES (1, 'Benzes qucha', 6169, 'Batumi', 1);

INSERT INTO LOCATIONS_ALGI
VALUES (2, 'Tbilisi st', 5269, 'Ozurgeti', 2);

INSERT INTO LOCATIONS_ALGI
VALUES (3, 'Meunergias qucha', 6274, 'Zugdidi', 3);

INSERT INTO LOCATIONS_ALGI
VALUES (4, 'Wavwavadze', 7432, 'Tbilisi', 4);

INSERT INTO LOCATIONS_ALGI
VALUES (5, 'Davit agmasheneblis qucha', 6432, 'Dedofliswyaro', 5);

INSERT INTO JOBS_ALGI
VALUES (1, 'PROGRAMMER', 4000, 8000);

INSERT INTO JOBS_ALGI
VALUES (2, 'Cashier', 3000, 4000);

INSERT INTO JOBS_ALGI
VALUES (3, 'Manager', 3000, 6000);

INSERT INTO JOBS_ALGI
VALUES (4, 'ASSISTANT INSTRUCTOR', 5000, 6000);

INSERT INTO JOBS_ALGI
VALUES (5, 'Maid', 3000, 7000);

INSERT INTO DEPARTMENTS_ALGI
VALUES (11, 'IT SUPPORT', 1, 1);

INSERT INTO DEPARTMENTS_ALGI
VALUES (21, 'SALES', 2, 2);

INSERT INTO DEPARTMENTS_ALGI
VALUES (31, 'MANAGMENT', 3, 3);

INSERT INTO DEPARTMENTS_ALGI
VALUES (41, 'INSTRUCTORS', 4, 4);

INSERT INTO DEPARTMENTS_ALGI
VALUES (51, 'CLEAN', 5, 5);

INSERT INTO EMPLOYEES_ALGI
VALUES (1, 'GIORGI', 'SHALAMBERIDZE', 'GIORGI1', '55122331', '2-JAN-2012', 1, 7000, 1, 11);

INSERT INTO EMPLOYEES_ALGI
VALUES (2, 'nikoloz', 'pirveli', 'nikoloz2', '555123151', '2-FEB-2012', 2, 7000, 2, 21);

INSERT INTO EMPLOYEES_ALGI
VALUES (3, 'aleqsandre', 'sajaia', 'ALEKSANDRE2', '548123151', '3-FEB-2013', 3 , 7000, 3, 31);

INSERT INTO EMPLOYEES_ALGI
VALUES (4, 'temuri', 'intskirveli', 'temuri4', '515123331', '2-JAN-2012', 4, 7000, 4, 41);

INSERT INTO EMPLOYEES_ALGI
VALUES (5, 'lasha', 'kvaracxelia', 'lasha5', '574173151', '5-FEB-2011', 5, 7000, 5, 51);


ALTER TABLE EMPLOYEES_ALGI
ADD FOREIGN KEY (JOB_ALGI_ID) REFERENCES JOBS_ALGI(JOB_ALGI_ID);

ALTER TABLE  DEPARTMENTS_ALGI
ADD FOREIGN KEY (LOCATION_ALGI_ID) REFERENCES LOCATIONS_ALGI(LOCATION_ALGI_ID);

ALTER TABLE  LOCATIONS_ALGI
ADD FOREIGN KEY (STATE_PROVINCE_ID) REFERENCES STATE_PROVINCE_ALGI(STATE_PROVINCE_ID);

COMMIT;