Data Manipulation and Transactions

Objective: Learn how to manipulate data and work with transactions in SQL.

1. Inserting Multiple Rows at Once

When you need to add multiple records to a table at the same time, SQL allows you to do this efficiently with a single INSERT statement. This saves time and reduces the number of commands needed.

• Syntax:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES 
     (value1, value2, value3, ...),
     (value4, value5, value6, ...),
     (value7, value8, value9, ...);

• Example:

INSERT INTO students (name, age, grade)
VALUES 
     ('John Doe', 18, 'A'),
     ('Jane Smith', 19, 'B'),
     ('Mark Brown', 17, 'A');

This command inserts three students into the students table in one go, which is more efficient than inserting each student individually.

2. Bulk Updates and Deletions

SQL allows you to update or delete multiple records at once based on a condition. This is useful when you need to make the same change to many rows.

• Syntax for Bulk Update:

UPDATE table_name
SET column1 = value1, column2 = value2, …
WHERE condition;

• Example:

UPDATE students
SET grade = 'A'
WHERE grade = 'B';

This command changes the grade of all students who currently have a ‘B’ to an ‘A’.

• Syntax for Bulk Deletion:

DELETE FROM table_name
WHERE condition;

• Example:

DELETE FROM students
WHERE grade = 'C';

This command deletes all students who have a grade of ‘C’.

3. Understanding Transactions and ACID Properties

A transaction in SQL is a group of operations that are executed together. If any part of the transaction fails, the whole transaction is rolled back, ensuring the database remains consistent. Transactions are guided by the ACID properties:

• Atomicity: All operations within a transaction are completed; if one fails, the entire transaction is rolled back.
• Consistency: A transaction brings the database from one valid state to another.
• Isolation: Transactions are executed independently of one another.
• Durability: Once a transaction is committed, the changes are permanent.
  
  

• Syntax:

BEGIN TRANSACTION;
     -- SQL statements
COMMIT;

• Example:

BEGIN TRANSACTION;
     UPDATE accounts
     SET balance = balance - 100
     WHERE account_id = 1;
     UPDATE accounts
     SET balance = balance + 100
     WHERE account_id = 2;
COMMIT;

This transaction transfers $100 from account 1 to account 2. If any step fails, the entire operation is rolled back, ensuring no partial changes are made.

4. Implementing Savepoints and Rollbacks

Savepoints allow you to set a point within a transaction to which you can roll back if needed. This is useful when you want to undo specific parts of a transaction without rolling back the entire transaction.

• Syntax for Savepoints:

SAVEPOINT savepoint_name;

• Syntax for Rollback to a Savepoint:

ROLLBACK TO savepoint_name;

• Example:

BEGIN TRANSACTION;
    UPDATE students SET grade = 'B' WHERE name = 'John Doe';
    SAVEPOINT sp1;
    UPDATE students SET grade = 'C' WHERE name = 'Jane Smith';
    ROLLBACK TO sp1;
COMMIT;

In this example, the grade for ‘John Doe’ is updated, but the change for ‘Jane Smith’ is rolled back, leaving her grade unchanged.

5. Managing Constraints

Constraints are rules that you can apply to your table columns to ensure the accuracy and reliability of the data in your database. Common constraints include:

• PRIMARY KEY: Ensures each record in a table is unique.
• FOREIGN KEY: Ensures data integrity between related tables.
• UNIQUE: Ensures that all values in a column are different.
• NOT NULL: Ensures that a column cannot have a NULL value.
  
  
  
• Syntax for Adding Constraints:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_type (column_name);

• Example:

ALTER TABLE students
ADD CONSTRAINT pk_student_id PRIMARY KEY (student_id);
ALTER TABLE enrollments
ADD CONSTRAINT fk_student_id FOREIGN KEY (student_id) REFERENCES students(student_id);

The first command adds a primary key to the student_id column in the students table. The second command adds a foreign key constraint to the student_id column in the enrollments table, linking it to the students table.

Conclusion

In this lesson, you learned how to efficiently manipulate data and manage transactions in SQL, ensuring data accuracy and consistency. You also explored how to enforce rules with constraints, making your database more robust.

In the next lesson, we’ll dive into indexing and performance optimization techniques to help you write faster, more efficient SQL queries.