SQL 3: Data Modification and Transactions

30.12.23

Programming

Lesson 3: Data Modification and Transactions

Introduction

Welcome to Lesson 3 of our SQL course. In this lesson, we'll delve into the world of data modification in SQL. You've learned how to query databases; now it's time to understand how to insert, update, and delete records. Additionally, we'll explore the concept of transactions, ensuring data integrity and consistency in the face of various operations. Get ready to manipulate data with precision and implement robust safeguards to maintain the reliability of your databases.

Data Modification

**INSERT Statement:**
The INSERT statement is used to add new records to a table. It allows you to specify the values for each column or insert data from another table.

-- Example: Insert a new employee into the 'employees' table
INSERT INTO employees (name, position, salary) VALUES ('John Doe', 'Developer', 60000);

**UPDATE Statement:**
The UPDATE statement is employed to modify existing records in a table. It specifies the columns to be updated and the new values.

-- Example: Update the salary of an employee in the 'employees' table
UPDATE employees SET salary = 65000 WHERE employee_id = 101;

**DELETE Statement:**
The DELETE statement is used to remove records from a table based on specified conditions.

-- Example: Delete an order from the 'orders' table
DELETE FROM orders WHERE order_id = 5001;

**Modifying Data in a Single Table:**
Data modification can be performed on a single table, and it's crucial to understand the impact of such modifications on the overall database.

Transactions

**ACID Properties:**
Transactions in SQL adhere to the ACID properties—Atomicity, Consistency, Isolation, and Durability. These principles ensure that database transactions are reliable and maintain data integrity.

- **Atomicity:** A transaction is treated as a single, indivisible unit of work. It either completes successfully, making all changes, or fails, leaving the database unchanged.

- **Consistency:** A transaction brings the database from one valid state to another. It ensures that data remains consistent throughout the process.

- **Isolation:** Transactions occur independently of each other. Changes made by one transaction are not visible to other transactions until the first transaction is committed.

- **Durability:** Once a transaction is committed, its changes are permanent and survive system failures.

**BEGIN TRANSACTION, COMMIT, ROLLBACK:**
SQL provides commands to control transactions:

- **BEGIN TRANSACTION:** Marks the beginning of a transaction.
  
  
  -- Example: Begin a transaction
  BEGIN TRANSACTION;
  

- **COMMIT:** Marks the successful end of a transaction, making its changes permanent.

  
  -- Example: Commit a transaction
  COMMIT;
  

- **ROLLBACK:** Reverts the changes made during a transaction if an error occurs or if the transaction needs to be canceled.

  
  -- Example: Rollback a transaction
  ROLLBACK;
  

Constraints

**Primary Key:**
A primary key uniquely identifies each record in a table. It must be unique and cannot contain NULL values.

-- Example: Creating a table with a primary key
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50),
    position VARCHAR(50),
    salary DECIMAL(10, 2)
);

**Foreign Key:**
A foreign key establishes a link between two tables by referencing the primary key of another table. It ensures referential integrity.

-- Example: Creating a table with a foreign key
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

**Unique Constraint:**
A unique constraint ensures that all values in a column are distinct. It can be applied to one or more columns.

-- Example: Creating a table with a unique constraint
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(50) UNIQUE,
    price DECIMAL(10, 2)
);

**NOT NULL Constraint:**
The NOT NULL constraint ensures that a column cannot contain NULL values.

-- Example: Creating a table with NOT NULL constraints
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT NOT NULL
);

Examples

Let's reinforce these concepts with practical examples:

**Example 1: Inserting and Updating Records:**
Consider a 'customers' table with columns: customer_id, name, and email.

-- Insert a new customer
INSERT INTO customers (name, email) VALUES ('Alice Johnson', 'alice@example.com');

-- Update the email of a customer
UPDATE customers SET email = 'alice.new@example.com' WHERE customer_id = 1001;

**Example 2: Using Transactions for Data Integrity:**
Imagine an 'account_transactions' table with columns: transaction_id, account_id, and amount.

-- Begin a transaction
BEGIN TRANSACTION;

-- Insert a new transaction
INSERT INTO account_transactions (account_id, amount) VALUES (500, 1000);

-- Update the account balance
UPDATE accounts SET balance = balance + 1000 WHERE account_id = 500;

-- Commit the transaction
COMMIT;

**Example 3: Implementing Constraints:**
Consider a 'books' table with columns: book_id, title, and author.

-- Create a table with primary and foreign keys
CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    author_id INT,
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

In this lesson, you've learned how to modify data in SQL databases, manage transactions, and implement constraints for data integrity. These skills are essential for maintaining a reliable and efficient database. In the next lesson, we'll explore indexing, optimization techniques, and basic database security to enhance your SQL proficiency further.

Back