-- TCL 
-- MERGE Statement (DML)
-- Joining data From 3 Diff Table 


-- TCL : Transaction Control Language 

	COMMIT			-- save
	ROLLBACK        -- undo
	SAVEPOINT       -- split 
	
	
Transaction ?  list of statements which we executed between two commit points.

CREATE t1

INSERT t1    10
INSERT t1    20
INSERT t1    30

COMMIT;

INSERT t1    40
INSERT t1    50

CREATE t2

INSERT t2   100
INSERT t2   200

SELECT * FROM t1;
SELECT * FROM t2;

ROLLBACK;

SELECT * FROM t1;
SELECT * FROM t2;

-- Join 

SELECT e.employee_id
     , e.first_name
     , d.department_name
  	 , l.street_address
  FROM employees e , departments d, locations l
 WHERE e.department_id = d.department_id
   AND d.location_id   = l.location_id;
   
SELECT e.employee_id
     , e.first_name
     , d.department_name
  	 , l.street_address
  FROM employees e JOIN departments d  ON e.department_id = d.department_id
                   JOIN locations   l  ON d.location_id   = l.location_id;


MERGE Statement 

	MERGE = INSERT + UPDATE 
	
CREATE TABLE course_tb(cid NUMBER, c_name VARCHAR2(30)); 
CREATE TABLE bkp_course_tb(cid NUMBER, c_name VARCHAR2(30));

INSERT INTO course_tb VALUES(10,'Oracle');
INSERT INTO course_tb VALUES(20,'Bigdata');
INSERT INTO course_tb VALUES(30,'AWS');


MERGE INTO bkp_course_tb b
USING course_tb c
ON (c.cid = b.cid)
WHEN MATCHED THEN
	UPDATE 
	   SET b.c_name = c.c_name
WHEN NOT MATCHED THEN
	INSERT VALUES(c.cid, c.c_name);
	
UPDATE course_tb
   SET c_name = 'Oracle SQL'
 WHERE cid = 10;
 
 INSERT INTO course_tb VALUES(40,'DevOps');
 
Table is row Destination
Sub Query can be table
ON Join two row sets
Must have MATCHED or NOT MATCHED 
MATCHED - Update with optional DELETE 
DELETE needs WHERE Clause
NOT MATCHED insert runs

SELECT * FROM recyclebin WHERE original_name = 'T1';

FLASHBACK TABLE t1 TO BEFORE DROP;

DROP TABLE t1 PURGE;