/*
Запрос 1
*/


WITH 
SRC AS
(SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID, BONUS
   FROM EMPLOYEES_TEST),

TARG AS
(SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID, BONUS
   FROM EMPLOYEES_TEST_BKP),
        
DELETED AS 
(SELECT * FROM
     (SELECT TARG.EMPLOYEE_ID FROM TARG
       MINUS
      SELECT SRC.EMPLOYEE_ID FROM SRC)),
       
INS AS 
(SELECT * FROM
      (SELECT SRC.EMPLOYEE_ID FROM SRC
        MINUS
       SELECT TARG.EMPLOYEE_ID FROM TARG)),

UPD AS
(SELECT * FROM
       (SELECT SRC.* FROM SRC
         MINUS
        SELECT TARG.* FROM TARG)
         WHERE EMPLOYEE_ID NOT IN (SELECT EMPLOYEE_ID FROM DELETED 
                                    UNION ALL 
                                   SELECT EMPLOYEE_ID FROM INS))

SELECT TARG.*, 'D' AS OPER FROM TARG WHERE TARG.EMPLOYEE_ID IN (SELECT DELETED.EMPLOYEE_ID FROM DELETED)
   UNION ALL
SELECT SRC.*, 'I' AS OPER FROM SRC WHERE SRC.EMPLOYEE_ID IN (SELECT INS.EMPLOYEE_ID FROM INS)
   UNION ALL
SELECT UPD.*, 'U' AS OPER FROM UPD

/*
Запрос 2, убираем из сравнения SALARY
*/


WITH 
SRC AS
(SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID, BONUS
   FROM EMPLOYEES_TEST),

TARG AS
(SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID, BONUS
   FROM EMPLOYEES_TEST_BKP),
        
DELETED AS 
(SELECT * FROM
     (SELECT TARG.EMPLOYEE_ID FROM TARG
       MINUS
      SELECT SRC.EMPLOYEE_ID FROM SRC)),
       
INS AS 
(SELECT * FROM
      (SELECT SRC.EMPLOYEE_ID FROM SRC
        MINUS
       SELECT TARG.EMPLOYEE_ID FROM TARG)),

UPD AS
(SELECT * FROM
       (SELECT SRC.* FROM SRC
         MINUS
        SELECT TARG.* FROM TARG)
         WHERE EMPLOYEE_ID NOT IN (SELECT EMPLOYEE_ID FROM DELETED 
                                    UNION ALL 
                                   SELECT EMPLOYEE_ID FROM INS))

SELECT TARG.*, 'D' AS OPER FROM TARG WHERE TARG.EMPLOYEE_ID IN (SELECT DELETED.EMPLOYEE_ID FROM DELETED)
   UNION ALL
SELECT SRC.*, 'I' AS OPER FROM SRC WHERE SRC.EMPLOYEE_ID IN (SELECT INS.EMPLOYEE_ID FROM INS)
   UNION ALL
SELECT UPD.*, 'U' AS OPER FROM UPD