1) Rollback and Commit commands Create Teacher table with the following
fields(Name, DeptNo, Date of joining, DeptName, Location, Salary) .
( a ). Insert five records
( b ) Give Increment of 25% salary for Mathematics Department .
( c ) Perform Rollback command
( d ) Give Increment of 15% salary for Commerce Department
( e ) Perform commit command
Here are the SQL commands to fulfill the given requirements:
1) Create Teacher table with the specified fields:
CREATE TABLE Teacher (
Name VARCHAR(50),
DeptNo INT,
DateOfJoining DATE,
DeptName VARCHAR(50),
Location VARCHAR(50),
Salary FLOAT
);
(a) Insert five records:
INSERT INTO Teacher (Name, DeptNo, DateOfJoining, DeptName, Location, Salary)
VALUES ('John Doe', 1, '2010-01-01', 'Mathematics', 'New York', 50000),
('Jane Smith', 2, '2012-05-15', 'Physics', 'Los Angeles', 60000),
('Michael Johnson', 3, '2015-08-10', 'Chemistry', 'Chicago', 55000),
('Emily Wilson', 4, '2017-03-02', 'Biology', 'Boston', 45000),
('David Thompson', 5, '2019-11-20', 'Commerce', 'San Francisco', 70000);
(b) Give an increment of 25% salary for Mathematics Department:
UPDATE Teacher
SET Salary = Salary * 1.25
WHERE DeptName = 'Mathematics';
(c) Perform rollback command:
ROLLBACK;
(d) Give an increment of 15% salary for Commerce Department:
UPDATE Teacher
SET Salary = Salary * 1.15
WHERE DeptName = 'Commerce';
(e) Perform commit command:
COMMIT;