/* 020 - COMMIT & ROLLBACK */
-- First go to MYSQL WorkBench > edit > preferences > SQL Execution > Restart
-- create table
CREATE TABLE tOne(
id INT NOT NULL UNIQUE,
name VARCHAR(50) NOT NULL,
percentage INT NOT NULL,
age INT NOT NULL CHECK(age >= 18),
gender VARCHAR(1) NOT NULL,
phone VARCHAR(10) NOT NULL UNIQUE,
city VARCHAR(15) NOT NULL DEFAULT 'Agra'
);
-- insert records
INSERT INTO tOne(id,name,percentage,age,gender,phone,city)
VALUES
(1,"Ram Kumar","45","23","M","4022155","Agra"),
(2,"Sarita Kumari","56","21","F","4034421","Delhi"),
(3,"Salman Khan","62","20","M","4056221","Agra"),
(4,"Juhi Chawla","47","28","F","4022158","Bhopal"),
(5,"Anil Kapoor","74","22","M","4025221","Agra"),
(6,"John Abraham","64","21","M","4056776","Delhi"),
(7,"Shahid Kapoor","52","20","M","4022784","Agra");
COMMIT; -- commit the above data fist other wise rollback delete all the entries
-- Example-1 ( successfully roll back the entry )
SELECT * FROM tOne;
UPDATE tOne SET percentage = 60 WHERE id = 2;
ROLLBACK;
-- Example-2 ( can not roll back the entry )
SELECT * FROM tOne;
UPDATE tOne SET percentage = 60 WHERE id = 2;
COMMIT;
ROLLBACK;
-- Example-3 ( the below code and work in one go and works fine )
SELECT * FROM tOne;
UPDATE tOne SET percentage = 50 WHERE id = 2;
SELECT * FROM tOne; -- just for check
COMMIT; -- above entry will not change
UPDATE tOne SET percentage = 00 WHERE id = 2; -- only this entry will change
ROLLBACK;
-- Example-4 ( roll back to original value of both )
SELECT * FROM tOne;
COMMIT;
UPDATE tOne SET age = 20 WHERE id = 4;
UPDATE tOne SET percentage = 60 WHERE id = 2;
ROLLBACK;
-- Example-5
SELECT * FROM tOne;
UPDATE tOne SET age = 20 WHERE id = 4;
COMMIT;
UPDATE tOne SET percentage = 60 WHERE id = 2;
ROLLBACK;