How to use MERGE records in Oracle table.

DROP TABLE employee;
DROP TABLE  bonuses;

CREATE TABLE employee (
employee_id NUMBER(5),
dept_no     NUMBER(2),
salary      NUMBER(10));

INSERT INTO employee
     VALUES (1, 10, 30000);
INSERT INTO employee
     VALUES (2, 20, 60000);
INSERT INTO employee
     VALUES (3, 20, 35000);
INSERT INTO employee
     VALUES (4, 10, 70000);
INSERT INTO employee
     VALUES (5, 20, 35000);
INSERT INTO employee
     VALUES (6, 20, 45000);
INSERT INTO employee
     VALUES (7, 20, 80000);

CREATE TABLE bonuses (
employee_id NUMBER, bonus NUMBER DEFAULT 500);

INSERT INTO bonuses
            (employee_id
            )
     VALUES (1
            );
INSERT INTO bonuses
            (employee_id
            )
     VALUES (3
            );
INSERT INTO bonuses
            (employee_id
            )
     VALUES (4
            );
INSERT INTO bonuses
            (employee_id
            )
     VALUES (7
            );
COMMIT ;

MERGE INTO bonuses b
   USING (SELECT employee_id, salary, dept_no
            FROM employee
           WHERE dept_no = 20) e
   ON (b.employee_id = e.employee_id)
   WHEN MATCHED THEN
      UPDATE
         SET b.bonus = b.bonus + e.salary * 0.02
         WHERE e.salary < 50000
   WHEN NOT MATCHED THEN
      INSERT (b.employee_id, b.bonus)
      VALUES (e.employee_id, e.salary * 0.03);

COMMIT;



SELECT *
  FROM employee;

EMPLOYEE_ID DEPT_NO SALARY     
1           10      30000      
2           20      60000      
3           20      35000      
4           10      70000      
5           20      35000      
6           20      45000      
7           20      80000      

SELECT *
  FROM bonuses;
  

EMPLOYEE_ID  BONUS  
1            500    
3            1200   
4            500    
7            500    
6            1350   
5            1050   
2            1800