CREATE TABLE persons(
    id NUMBER(10),
    lastname VARCHAR2(30),
    firstname VARCHAR2(30)
);

ALTER TABLE persons
    ADD (age NUMBER(3) CHECK (age >= 18),
         PRIMARY KEY(id),
         UNIQUE(firstname, lastname));

CREATE TABLE jobs(
    id NUMBER(10),
    name VARCHAR(50)
);

ALTER TABLE jobs
    ADD (salary NUMBER(8),
         PRIMARY KEY(id));

CREATE TABLE employees(
    id NUMBER(10),
    person_id NUMBER(10),
    job_id NUMBER(10)
);

ALTER TABLE employees
    ADD (employment_date DATE,
         PRIMARY KEY(id),
         CONSTRAINT person_key FOREIGN KEY (person_id) REFERENCES persons(id),
         CONSTRAINT job_key FOREIGN KEY (job_id) REFERENCES jobs(id));

INSERT INTO persons VALUES(1, 'Ivanov', 'Ivan', 25);
INSERT INTO persons VALUES(2, 'Petrov', 'Petr', 20);
INSERT INTO persons VALUES(3, 'Alexeev', 'Alexry', 38);

INSERT INTO jobs VALUES(1, 'Cleaner', 10000);
INSERT INTO jobs VALUES(2, 'Sailor', 150000);
INSERT INTO jobs VALUES(3, 'Main Architector', 400000);

INSERT INTO employees VALUES(1, 1, 2, TO_DATE('2014/07/22', 'yyyy/mm/dd'));
INSERT INTO employees VALUES(2, 3, 1, TO_DATE('1998/10/05', 'yyyy/mm/dd'));
INSERT INTO employees VALUES(3, 2, 3, TO_DATE('0001/01/01', 'yyyy/mm/dd'));

UPDATE persons
    SET age = 99
    WHERE id = 1;

ALTER TABLE employees
    ADD (salary NUMBER(20),
         premium_size NUMBER(20));

UPDATE employees
    SET salary = 10000;

UPDATE employees
    SET premium_size = 2000;

UPDATE employees
    SET salary = salary * 1.1
    WHERE id = 2;

UPDATE employees
    SET premium_size = premium_size * 1.3
    WHERE id = 2;

DELETE FROM employees WHERE id = 3;

ALTER TABLE employees
    DROP CONSTRAINT job_key;

DESC persons;
DESC jobs;
DESC employees;

SELECT * FROM persons;
SELECT * FROM jobs;
SELECT * FROM employees;