text: ---------------------------Práctica 4---------------------------
  
  --EJERCICIO 6
  
  CREATE TABLE EMPGRADE(EMPNO NUMBER, GRADE NUMBER, CONSTRAINT PK_EMPGRADE PRIMARY KEY (EMPNO));
  
  INSERT INTO EMPGRADE
  SELECT EMPNO,
  CASE
      WHEN ((SAL >= (SELECT LOSAL FROM SALGRADE WHERE GRADE=1)) AND (SAL <= (SELECT HISAL FROM SALGRADE WHERE GRADE=1))) THEN 1
      WHEN ((SAL >= (SELECT LOSAL FROM SALGRADE WHERE GRADE=2)) AND (SAL <= (SELECT HISAL FROM SALGRADE WHERE GRADE=2))) THEN 2
      WHEN ((SAL >= (SELECT LOSAL FROM SALGRADE WHERE GRADE=3)) AND (SAL <= (SELECT HISAL FROM SALGRADE WHERE GRADE=3))) THEN 3
      WHEN ((SAL >= (SELECT LOSAL FROM SALGRADE WHERE GRADE=4)) AND (SAL <= (SELECT HISAL FROM SALGRADE WHERE GRADE=4))) THEN 4
      WHEN ((SAL >= (SELECT LOSAL FROM SALGRADE WHERE GRADE=5)) AND (SAL <= (SELECT HISAL FROM SALGRADE WHERE GRADE=5))) THEN 5
      ELSE 0
  END AS GRADE
  FROM EMP;
  
  ---------------------------------------------------
  ---------------------------------------------------
  ---------------------------------------------------
  
  --1. Crear un empleat nou amb les següents característiques:
  --a. Nombre: 8010
  --b. NAME: CAGE
  --c. JOB: ASSISTANT
  
  SET TRANSACTION READ WRITE;
  
  INSERT INTO EMP(EMPNO, ENAME, JOB) VALUES (8010, 'CAGE', 'ASSISTANT');
  
  --2. Actualitzar el empleat creat (8010) amb els valores:
  --a. MGR: 8001
  --b. HIREDATE: 13/01/83
  --c. SAL: 3800
  --d. COMM: 100
  --e. DEPTNO: 50
  
  SAVEPOINT sp1;
  
  UPDATE EMP SET MGR = 8001, HIREDATE = '13/01/83', SAL = 3800, COMM = 100, DEPTNO = 50 WHERE EMPNO = 8010;
  
  --3. Assignar el nou empleat creat al grau salarial nombre 4 amb una inserció en la taula EMPGRADE.
  
  SAVEPOINT sp2;
  
  INSERT INTO EMPGRADE VALUES (8010, 4);
  
  --4. Comprovar si es compleixen les següents condicions
  --a. Condició #1: el grau salarial assignat coincideix amb el salari del empleat.
  
  SELECT GRADE FROM EMPGRADE WHERE EMPNO = 8010; --4
  
  SELECT EMPNO,
  CASE
      WHEN ((SAL >= (SELECT LOSAL FROM SALGRADE WHERE GRADE=1)) AND (SAL <= (SELECT HISAL FROM SALGRADE WHERE GRADE=1))) THEN 1
      WHEN ((SAL >= (SELECT LOSAL FROM SALGRADE WHERE GRADE=2)) AND (SAL <= (SELECT HISAL FROM SALGRADE WHERE GRADE=2))) THEN 2
      WHEN ((SAL >= (SELECT LOSAL FROM SALGRADE WHERE GRADE=3)) AND (SAL <= (SELECT HISAL FROM SALGRADE WHERE GRADE=3))) THEN 3
      WHEN ((SAL >= (SELECT LOSAL FROM SALGRADE WHERE GRADE=4)) AND (SAL <= (SELECT HISAL FROM SALGRADE WHERE GRADE=4))) THEN 4
      WHEN ((SAL >= (SELECT LOSAL FROM SALGRADE WHERE GRADE=5)) AND (SAL <= (SELECT HISAL FROM SALGRADE WHERE GRADE=5))) THEN 5
      ELSE 0
  END AS GRADE
  FROM EMP WHERE EMPNO = 8010; --5
  
  --No coinciden, no se cumple la condición 1
  
  --b. Condició #2: el salari de l'empleat és inferior al salari del seu cap.
  
  SELECT SAL FROM EMP WHERE EMPNO = 8010; --3800
  
  SELECT SAL FROM EMP WHERE EMPNO = (SELECT MGR FROM EMP WHERE EMPNO = 8010); --3500
  
  --No es mayor, no se cumple la condición 2
  
  --5. En cas de que no es complisca la Condició #2, desfer l'actualització i tornar-la a fer amb un salari 800 unitats inferior al del seu cap.
  
  ROLLBACK TO sp1;
  
  UPDATE EMP SET MGR = 8001, HIREDATE = '13/01/83', SAL = (SELECT SAL-800 FROM EMP WHERE EMPNO = 8001), COMM = 100, DEPTNO = 50 WHERE EMPNO = 8010;
  
  --6. En cas de que no es complisca la Condició #1, desfer la inserció i assignar en EMPGRADE el valor correcte.
  
  ROLLBACK TO sp2; --Da error porque se ha vuelto antes al sp1, se ignora
  
  INSERT INTO EMPGRADE
  SELECT EMPNO,
  CASE
      WHEN ((SAL >= (SELECT LOSAL FROM SALGRADE WHERE GRADE=1)) AND (SAL <= (SELECT HISAL FROM SALGRADE WHERE GRADE=1))) THEN 1
      WHEN ((SAL >= (SELECT LOSAL FROM SALGRADE WHERE GRADE=2)) AND (SAL <= (SELECT HISAL FROM SALGRADE WHERE GRADE=2))) THEN 2
      WHEN ((SAL >= (SELECT LOSAL FROM SALGRADE WHERE GRADE=3)) AND (SAL <= (SELECT HISAL FROM SALGRADE WHERE GRADE=3))) THEN 3
      WHEN ((SAL >= (SELECT LOSAL FROM SALGRADE WHERE GRADE=4)) AND (SAL <= (SELECT HISAL FROM SALGRADE WHERE GRADE=4))) THEN 4
      WHEN ((SAL >= (SELECT LOSAL FROM SALGRADE WHERE GRADE=5)) AND (SAL <= (SELECT HISAL FROM SALGRADE WHERE GRADE=5))) THEN 5
      ELSE 0
  END AS GRADE
  FROM EMP WHERE EMPNO = 8010;
  
  --7. Validar la transacció.
  
  COMMIT;
  
  