BD LAB 9

INSERT INTO jobs
VALUES ('job_id', 'job_title', 10000, 70000);
ROLLBACK;

CREATE TABLE EMP_ALX AS
SELECT * FROM employees;

CREATE TABLE DEPT_ALX AS
SELECT * FROM departments;

DESC employees;
DESC emp_alx;

--4

ALTER TABLE emp_alx
ADD CONSTRAINT pk_emp_alx PRIMARY KEY(employee_id);

ALTER TABLE dept_alx
ADD CONSTRAINT pk_dept_alx PRIMARY KEY(department_id);

ALTER TABLE emp_alx
ADD CONSTRAINT fk_emp_alx
FOREIGN KEY(department_id) REFERENCES dept_alx(department_id);

--5
INSERT INTO dept_alx(department_id, department_name)
VALUES(300, 'Programare');

INSERT INTO dept_alx(department_id, department_name, location_id)
VALUES(300, 'Programare', NULL);

ROLLBACK;

--6
INSERT INTO emp_alx
VALUES (250, NULL, 'Nume', 'email@gmail.com', NULL, sysdate,'SA_REP', NULL, NULL, NULL, 300);

INSERT INTO emp_alx (employee_id, last_name, email, hire_date, job_id, department_id)
VALUES(251, 'Nume', 'email@gmail.com', sysdate, 'SA_REP', 300);
commit;

--9
CREATE TABLE emp1_alx AS
SELECT * FROM employees;

DELETE emp1_alx;

INSERT INTO emp1_alx
  SELECT *
  FROM employees
  WHERE commission_pct>0.25;

--10
SELECT USER
FROM dual;

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

--12
CREATE TABLE EMP3_ALX AS
SELECT *
FROM employees;
DELETE FROM emp3_alx;

INSERT FIRST
WHEN SALARY <= 5000 THEN INTO emp1_alx
WHEN salary >5000 AND salary <10000 THEN INTO emp2_alx
WHEN salary >= 10000 THEN INTO emp3_alx
SELECT * FROM employees;
ROLLBACK;

--13
INSERT FIRST
WHEN department_id=80 THEN INTO emp0_alx
WHEN SALARY <= 5000 THEN INTO emp1_alx
WHEN salary >5000 AND salary <10000 THEN INTO emp2_alx
WHEN salary >= 10000 THEN INTO emp3_alx
SELECT * FROM employees;
ROLLBACK;

--14
UPDATE emp_alx
SET salary=salary+0.05*salary;

--15
UPDATE emp_alx
SET job_id= 'SA_REP'
WHERE department_id = 80;

--16
UPDATE emp_alx
SET salary=salary+1000, department_id=20
WHERE employee_id=(
    SELECT employee_id
    FROM employees
    WHERE LOWER(first_name || ' ' || last_name) = 'douglas grant');

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

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

--18
UPDATE emp_alx e
SET email= SUBSTR(last_name,1,1) || NVL(first_name,'.')
WHERE salary = (
    SELECT MAX(salary)
    FROM emp_alx
    WHERE e.department_id=department_id
    GROUP BY department_id);
ROLLBACK;

--19
UPDATE emp_alx e
SET salary=(
    SELECT avg(salary)
    FROM emp_alx)
WHERE hire_date=(
    SELECT MIN(hire_date)
    FROM emp_alx
    WHERE department_id= e.department_id
    GROUP BY department_id);

--20
UPDATE emp_alx
SET (job_id, department_id) =(
    SELECT job_id, department_id
    FROM emp_alx
    WHERE employee_id = 205)
WHERE employee_id=114;

--22
DELETE FROM dept_alx;
--nu merge

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

--24
DELETE FROM dept_alx
WHERE department_id IN(
    SELECT department_id
    FROM emp_alx JOIN dept_alx USING(department_id)
    GROUP BY department_id
    HAVING COUNT(employee_id)=0);