--CREATE TABLE EMP_IMares AS SELECT * FROM employees;
--CREATE TABLE DEPT_IMares AS SELECT * FROM departments;

ALTER TABLE EMP_IMares
ADD CONSTRAINT pk_emp_IMares PRIMARY KEY(employee_id);

ALTER TABLE DEPT_IMares
ADD CONSTRAINT pk_dept_IMares PRIMARY KEY(department_id);

ALTER TABLE emp_IMares
ADD CONSTRAINT fk_emp_dept_IMares
  Foreign key(department_id) REFERENCES dept_IMares(department_id);

ALTER TABLE EMP_IMares
ADD CONSTRAINT emp_sef_ang_IMares
  foreign key(manager_id) references emp_IMares(employee_id);

ALTER TABLE DEPT_IMares
ADD CONSTRAINT dept_IMares_sef_dept_IMares
  FOREIGN KEY(manager_id) REFERENCES emp_IMares(employee_id);

INSERT INTO DEPT_IMares (department_id, department_name)
VALUES (300,'Programare');

COMMIT;

INSERT INTO EMP_IMares VALUES(600,NULL,'Popa','popa@yahoo.com',NULL,to_date('23.03.2018','dd mm yyyy'),'IT_PROG',NULL,NULL,NULL,300);

INSERT INTO EMP_IMares (employee_id,last_name,email,hire_date,job_id,department_id)
VALUES(601,'Popa','popa@yahoo.com',to_date('23.03.2018','dd mm yyyy'),'IT_PROG',300);

COMMIT;

INSERT INTO (SELECT employee_id,last_name,email,hire_date,job_id,department_id FROM emp_imares)
VALUES(602,'Al 3 lea angajat','ang3rd@yahoo.com',to_date('23.03.2018','dd mm yyyy'),'IT_PROG',300);

INSERT INTO emp_imares (employee_id,last_name,email,hire_date,job_id,department_id)
VALUES((select max(employee_id)+1 from emp_imares),'Al 4 lea angajat','ang4th@yahoo.com',to_date('23.03.2018','dd mm yyyy'),'IT_PROG',300);

COMMIT;

CREATE TABLE emp1_imares AS SELECT * FROM employees WHERE salary<0;
commit;
ALTER TABLE EMP1_IMares
ADD CONSTRAINT pk_emp1_IMares PRIMARY KEY(employee_id);

INSERT INTO EMP1_IMares
SELECT *
FROM employees
WHERE (commission_pct*salary) > salary/4  ;

COMMIT;
--

SELECT *
from employees join departments using(department_id);

INSERT INTO (SELECT employee_id,last_name,email,hire_date,job_id,department_id FROM emp_imares)
VALUES(604,'Al 5 lea angajat','ang3rd@yahoo.com',to_date('23.03.2018','dd mm yyyy'),'IT_PROG',500);

-- trebuie respectata constrangerea! (nu se poate adauga angajat cu department_id nou)

--@script

INSERT INTO emp1_IMares(employee_id,first_name,last_name,hire_date,job_id,email)
VALUES(&cod,'&&prenume','&&nume',SYSDATE,'&job',substr(&prenume,1,7)||substr(&nume,1,7));
undefine nume;
undefine prenume;

DELETE FROM emp1_IMares;
COMMIT;

CREATE TABLE emp3_IMares
as select *
FROM employees
WHERE 1=2;

commit;

--first/all
INSERT all
WHEN department_id = 80 THEN INTO emp0_IMares
WHEN salary<5000 THEN INTO emp1_IMares
WHEN salary BETWEEN 5000 AND 10000 THEN INTO emp2_IMares
WHEN salary>10000 THEN INTO emp3_IMares
SELECT * FROM employees;

COMMIT;