BD laborator 03.12.2018

--lab 7
--1 (de in
CREATE TABLE emp_cpi AS
SELECT * FROM employees;

CREATE TABLE dept_cpi AS
SELECT * FROM departments;

--2
DESC emp_cpi;

--4
ALTER TABLE emp_cpi
ADD CONSTRAINT pk_emp_cpi PRIMARY KEY(employee_id);

ALTER TABLE dept_cpi
ADD CONSTRAINT pk_dept_cpi PRIMARY KEY(department_id);

ALTER TABLE emp_cpi
ADD CONSTRAINT fk_emp_dept_cpi
 FOREIGN KEY(department_id) REFERENCES dept_cpi(department_id);

 --5
 INSERT INTO dept_cpi
VALUES (300, ‘Programare’);

INSERT INTO dept_cpi (department_id, department_name)
 VALUES (300, "Programare");

 INSERT INTO dept_cpi (department_name, department_id)
 VALUES (300, "Programare");

 INSERT INTO dept_cpi (department_id, department_name, location_id)
 VALUES (300, "Programare", NULL);

 INSERT INTO dept_cpi (department_name, location_id)
 VALUES (‘Programare’, NULL);

 ROLLBACK;

 INSERT INTO dept_cpi (department_id, department_name, location_id)
 VALUES (301, 'Programare', NULL);
 ROLLBACK;

 --6
 INSERT INTO emp_cpi
 VALUES (250, 'Prenume', 'Nume', '[email protected]', NULL, sysdate, 'SA_REP', 15000, NULL, NULL, 300);

 --7
  INSERT INTO emp_cpi (employee_id, last_name, email, hire_date, job_id, department_id)
 VALUES (251, 'Nume', '[email protected]', sysdate, 'SA_REP', 301);

 --9

 CREATE TABLE emp1_cpi AS
 SELECT * FROM employees;

 DELETE FROM emp1_cpi;

 --10
 INSERT INTO emp1_cpi
    SELECT * FROM employees
    WHERE commission_pct > 0.25;
commit;

--11?
SELECT USER
FROM dual;

INSERT INTO emp_cpi
    SELECT 0, USER, USER, 'TOTAL', 'TOTAL', sysdate, 'TOTAL', SUM(salary), avg(commission_pct), NULL, NULL
    FROM employees;

--12
CREATE TABLE emp2_cpi AS
SELECT * FROM employees;

CREATE TABLE emp3_cpi AS
SELECT * FROM employees;

DELETE FROM emp2_cpi;
commit;

INSERT ALL;
WHEN salary < 5000 THEN INTO emp1_cpi
WHEN salary > 5000 AND salary < 10000 THEN INTO emp2_cpi
WHEN salary > 10000 THEN INTO emp3_cpi
SELECT * FROM employees;

ROLLBACK;

--13


--14

UPDATE emp_cpi
SET salary = salary * 0.05;
ROLLBACK;


--15
UPDATE emp_cpi
SET job_id = 'SA_REP'
WHERE department_id = 80 AND commission_pct IS NOT NULL;

--16
UPDATE emp_cpi
SET department_id = 20, salary = salary + 1000
WHERE LOWER(first_name || ' ' || last_name) = 'douglas grant';

UPDATE emp_cpi
SET manager_id = (
    SELECT employee_id
    FROM emp_cpi
    WHERE LOWER(first_name || ' ' || last_name) = 'douglas grant' )
  WHERE department_id = 20;

--17
UPDATE emp_cpi e
SET (salary, commission_pct) = (
    SELECT salary, commission_pct
    FROM emp_cpi
    WHERE employee_id =  e.manager_id)
WHERE employee_id = (
    SELECT employee_id
    FROM employees
    WHERE salary > (
        SELECT MIN(salary)
        FROM employees) );

--23
DELETE FROM emp_cpi
WHERE commission_pct IS NULL;
ROLLBACK;