Constraints fire after DML Execution

CREATE TABLE T_DEPARTMENT (
    DEPARTMENT_ID      NUMBER         PRIMARY KEY NOT NULL,
    DEPARTMENT_NAME    VARCHAR2(50)   NOT NULL);

INSERT INTO T_DEPARTMENT (DEPARTMENT_ID, DEPARTMENT_NAME) VALUES( 1, 'ACCOUNTS'    );
INSERT INTO T_DEPARTMENT (DEPARTMENT_ID, DEPARTMENT_NAME) VALUES( 2, 'CALL CENTER' );
INSERT INTO T_DEPARTMENT (DEPARTMENT_ID, DEPARTMENT_NAME) VALUES( 3, 'PURCHASE'    );
INSERT INTO T_DEPARTMENT (DEPARTMENT_ID, DEPARTMENT_NAME) VALUES( 4, 'MARKETING'   );
INSERT INTO T_DEPARTMENT (DEPARTMENT_ID, DEPARTMENT_NAME) VALUES( 5, 'SALES'       );

SQL> SELECT * FROM T_DEPARTMENT ORDER BY 1;

DEPARTMENT_ID DEPARTMENT_NAME
------------- --------------------------------------------------
            1 ACCOUNTS
            2 CALL CENTER
            3 PURCHASE
            4 MARKETING
            5 SALES
  
UPDATE T_DEPARTMENT SET DEPARTMENT_ID = 4 WHERE DEPARTMENT_NAME = 'PURCHASE';
UPDATE T_DEPARTMENT SET DEPARTMENT_ID = 3 WHERE DEPARTMENT_NAME = 'MARKETING';

The above set of DMLs will not work as it violates the unique key constraint. Try the following DML

UPDATE T_DEPARTMENT SET DEPARTMENT_ID = DECODE(DEPARTMENT_NAME,
                                               'MARKETING', 3,
                                               'PURCHASE',  4 )
 WHERE DEPARTMENT_NAME IN ( 'PURCHASE', 'MARKETING' );

SQL> SELECT * FROM T_DEPARTMENT ORDER BY 1;

DEPARTMENT_ID DEPARTMENT_NAME
------------- --------------------------------------------------
            1 ACCOUNTS
            2 CALL CENTER
            3 MARKETING
            4 PURCHASE
            5 SALES

Different Variation of NVL

NVL2 (string1, if_not_null, if_null):

SELECT ename,
       NVL2 (comm, 'Commissionable', 'Non-Commissionable') AS STATUS
  FROM emp;

It is equivalent to

SELECT ename,
       CASE
         WHEN comm IS NOT NULL THEN 'Commissionable'
         ELSE 'Non-Commissionable'
       END AS STATUS
  FROM emp;

SELECT ename,
       DECODE (comm, NULL, 'Non-Commissionable', 'Commissionable') AS STATUS
  FROM emp;

LNNVL (condition):

Returns TRUE if the condition is FALSE or NULL
Returns FALSE if the condition is TRUE

SELECT * FROM emp WHERE LNNVL (comm > 500);

It is equivalent to

SELECT * FROM emp WHERE NVL(comm, 0) <= 500;

NULLIF (A, B):

Return NULL if A = B, else return A

SELECT NULLIF ('Smith', 'Smith'), -- returns NULL
       NULLIF ('Smith', 'SMITH'), -- returns Smith
       NULLIF ('Smith', ''),      -- returns Smith
       NULLIF ('Smith', NULL),    -- returns Smith
       NULLIF ('', 'SMITH'),      -- returns NULL
       NULLIF ('', '')            -- returns NULL
  FROM DUAL;

COALESCE (expr1, expr2, ... expr_n)

Returns the first not null value in the list

SELECT COALESCE( street1, street2, street3 ) address
  FROM customers;

It is equivalent to

IF street1 is not null THEN
   address := street1;
ELSIF address2 is not null THEN
   address := street2;
ELSIF address3 is not null THEN
   address := street3;
ELSE
   address := NULL;
END IF;