TCL Commands in SQL

Learn TCL Commands in SQL with examples — COMMIT, ROLLBACK, SAVEPOINT and AUTOCOMMIT. Understand how transactions work and how to save or undo changes using SQL queries with outputs. Useful for DBMS, MySQL, and SQL exam preparation.

TCL Commands in SQL

Transaction Control Language (TCL) commands.

They control changes made by:

• INSERT
• UPDATE
• DELETE

COMMIT

Saves the changes permanently to the database.

Syntax

COMMIT;

ROLLBACK

Cancels (undoes) changes made after the last COMMIT.

Syntax

ROLLBACK;

Note: Works only in transactional tables (e.g., InnoDB).

AUTOCOMMIT in MySQL

By default:

autocommit = ON

Meaning every statement is automatically committed.

To control transactions manually:

SET autocommit = 0;

or

START TRANSACTION;

TCL Queries with OUTPUT

Supplier Table

SELECT * FROM supplier;

INSERT followed by ROLLBACK

INSERT INTO supplier VALUES

(204003,’Sanjay’,’Hardware’,’Pune’,’MH’,9822334455),

(204004,’Kavita’,’Automobile’,’Delhi’,’Delhi’,9898989898);

Output BEFORE rollback

ROLLBACK;

Output AFTER rollback

INSERT followed by COMMIT

INSERT INTO supplier VALUES

(204003,’Sanjay’,’Hardware’,’Pune’,’MH’,9822334455),

(204004,’Kavita’,’Automobile’,’Delhi’,’Delhi’,9898989898);

COMMIT;

Output AFTER COMMIT

After COMMIT, ROLLBACK will not remove data.

Create Savepoints

SAVEPOINT s1;

Insert more records:

INSERT INTO supplier VALUES

(204007,’Meena’,’Civil’,’Delhi’,’Delhi’,9123456780),

(204008,’Rahul’,’Textile’,’Kolkata’,’Kolkata’,9234567812);

Table after insert

Rollback to Savepoint

ROLLBACK TO s1;

Remaining rows

Aggregate Functions Example

SELECT

 MIN(price) AS Minimum_price,

 MAX(price) AS Maximum_price,

 AVG(price) AS Average_price,

 SUM(price) AS Total_price

FROM Part;

Output

Count Example

SELECT COUNT(PID) FROM Part;

Quick Revision Points

• TCL = COMMIT, ROLLBACK, SAVEPOINT
• COMMIT = permanently save
• ROLLBACK = undo changes
• SAVEPOINT = rollback partially
• AUTOCOMMIT must be OFF for manual control
• Aggregate functions = MIN, MAX, SUM, AVG, COUNT

• POP- Introduction to Programming Using ‘C’
• DS – Data structure Using C
• OOP – Object Oriented Programming 
• Java Programming
• DBMS – Database Management System
• RDBMS – Relational Database Management System
• https://defineinfoloop.blogspot.com/?m=1