SQL INSERT, UPDATE, DELETE, Transactions & ACID Explained

Inserting Data into a Table

To add new records to a table, use the INSERT INTO statement. You can insert values for all columns or only specific columns.

Interview Answer

"The INSERT INTO statement is used to add new records to a table. I usually specify the column names explicitly because it improves readability and prevents errors if the table structure changes."

Syntax

Insert Values into All Columns

INSERT INTO TableName
VALUES (value1, value2, value3, ...);

Insert Values into Specific Columns (Recommended)

INSERT INTO TableName (column1, column2, column3)
VALUES (value1, value2, value3);

Example

Suppose we have a Students table:

Insert a new record:

INSERT INTO Students (StudentID, Name, Marks)
VALUES (1, 'Alice', 85);

Best Practices

• Always specify column names.
• Insert multiple rows in a single query whenever possible.
• Validate data before inserting.
• Use transactions for bulk inserts.

INSERT vs UPDATE

Real-Time Example

During user registration testing, after submitting the registration form, I verified that a new record was inserted into the Users table with the correct username, email, and status.

Updating Specific Rows

The UPDATE statement modifies existing records in a table.

Always use a WHERE clause to specify which rows should be updated.

Interview Answer

"I use the UPDATE statement to modify existing records. I always include a WHERE clause because omitting it updates every row in the table."

Syntax

UPDATE TableName
SET column_name = value
WHERE condition;

Example

UPDATE Students
SET Marks = 70
WHERE Name = 'Bob';

This updates only Bob's marks.

Updating Multiple Columns

UPDATE Students
SET Marks = 90,
    Grade = 'A'
WHERE StudentID = 101;

Warning

❌ Without a WHERE clause:

UPDATE Students
SET Marks = 70;

Every student's marks become 70.

Real-Time Example

After updating a user's profile through the application, I executed an UPDATE query and verified that only the intended user's information had changed.

Deleting Records with a Condition

The DELETE statement removes records from a table.

Always use a WHERE clause to delete only the required records.

Interview Answer

"I use the DELETE statement with a WHERE clause to remove specific records. Omitting the WHERE clause deletes every row in the table."

Syntax

DELETE FROM TableName
WHERE condition;

Example

DELETE FROM Students
WHERE Marks < 40;

This deletes only students whose marks are below 40.

Warning

❌ Without a WHERE clause:

DELETE FROM Students;

This deletes every record from the table.

Best Practices

• Always verify the WHERE condition.
• Run a SELECT query first to confirm the records.
• Use transactions when deleting important data.

Real-Time Example

During cleanup of test data, I deleted only inactive test users by filtering with a WHERE clause to avoid removing valid customer records.

What is a Transaction?

A transaction is a group of one or more SQL statements executed as a single unit of work.

It follows the "All or Nothing" principle:

• If every statement succeeds → save the changes.
• If any statement fails → undo everything.

Transactions ensure data integrity and consistency.

Interview Answer

"A transaction is a group of SQL operations executed together. Either all operations succeed or the entire transaction is rolled back. This ensures the database remains consistent."

Bank Transfer Example

Transfer ₹100 from Account 1 to Account 2.

START TRANSACTION;          -- BEGIN in PostgreSQL / SQL Server

UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;

UPDATE accounts
SET balance = balance + 100
WHERE account_id = 2;

COMMIT;

If any statement fails:

ROLLBACK;

This prevents money from being lost or duplicated — but only if you actually start a transaction and roll it back when something fails. We tested three versions on MariaDB (with a rule that a balance can't go below zero):

The last row is the important one: a failing statement does not automatically undo the rest of the transaction. The application (or your script) must check for the error and issue ROLLBACK. In Java that means con.setAutoCommit(false), then con.commit() in the try and con.rollback() in the catch — see JDBC in Java.

What testers check: after a failed payment or transfer, query both sides and confirm nothing changed — no half-finished orders, no stock deducted without a payment, no credit without a debit.

Why Transactions Matter

Transactions ensure:

• Data consistency
• Data integrity
• Reliable banking operations
• Reliable e-commerce payments
• Safe inventory updates

Real-Time Example

During payment processing, the customer's account is debited and the merchant's account is credited within the same transaction. If either operation fails, the entire transaction is rolled back.

COMMIT and ROLLBACK

Transactions are controlled using COMMIT and ROLLBACK.

COMMIT

COMMIT permanently saves all changes made during the current transaction.

Example

COMMIT;

After committing:

• Changes become permanent.
• They cannot be rolled back.

ROLLBACK

ROLLBACK cancels all uncommitted changes.

Example

ROLLBACK;

After rollback:

• Database returns to its previous state.
• No partial changes remain.

COMMIT vs ROLLBACK

Best Practices

• Commit only after all operations succeed.
• Roll back immediately when an error occurs.
• Disable auto-commit when multiple operations belong to one transaction.

Important Note

Once a COMMIT is executed:

• Changes become permanent.
• They cannot be rolled back.

Real-Time Example

In an online payment system, the transaction is committed only after both debit and credit operations complete successfully. Otherwise, the transaction is rolled back to maintain consistency.

ACID Properties

ACID represents the four fundamental properties that ensure reliable database transactions.

Interview Answer

"ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties guarantee reliable, consistent, and secure database transactions."

1. Atomicity

All or Nothing

Either every operation succeeds, or none of them are applied.

Example

A bank transfer consists of:

• Debit Account A
• Credit Account B

If the credit operation fails, the debit is also rolled back.

2. Consistency

The database must always remain in a valid state before and after every transaction.

No business rules or constraints should be violated.

3. Isolation

Multiple transactions should not interfere with each other.

Concurrent users should not see incomplete changes from other transactions.

4. Durability

Once a transaction is committed, the changes are permanent—even if the database server crashes immediately afterward.

ACID Summary

Real-Time Example

During an online banking transfer, ACID properties ensure that money is never lost or duplicated, even if the server crashes during the transaction.

Practice these questions under interview conditions in the SQL Interview Simulator.

📚 Official documentation: MySQL Reference Manual · Selenium documentation

Frequently Asked Questions (FAQs)

1. How do you insert data into a table?

Use the INSERT INTO statement.

Insert into all columns:

INSERT INTO table_name
VALUES (...);

Insert into specific columns (recommended):

INSERT INTO table_name (column1, column2)
VALUES (value1, value2);

Specifying column names improves readability and prevents errors when the table structure changes.

2. What happens if you don't use a WHERE clause in an UPDATE statement?

Without a WHERE clause, every row in the table is updated.

Always include a WHERE clause when modifying specific records.

3. How do you delete records with a condition?

Use the DELETE statement with a WHERE clause.

DELETE FROM table_name
WHERE condition;

Without a WHERE clause, every record in the table is deleted.

4. What is a transaction in SQL?

A transaction is a group of SQL statements executed as a single unit of work.

It follows the "All or Nothing" principle.

If any statement fails, the transaction can be rolled back to maintain data integrity.

5. What is the difference between COMMIT and ROLLBACK?

6. Can you ROLLBACK after COMMIT?

No.

Once a COMMIT is executed, the changes become permanent and cannot be rolled back.

7. What are ACID properties?

ACID ensures reliable database transactions.

• Atomicity: All operations succeed or none do.
• Consistency: Database remains valid before and after the transaction.
• Isolation: Concurrent transactions do not interfere with each other.
• Durability: Committed changes remain permanent, even after a system failure.