Chapter 6: Data Manipulation Language (DML)
Introduction
In this chapter, we will explore the Data Manipulation Language (DML) in SQL, which allows us to modify and manipulate data within a database. We will cover essential DML statements such as UPDATE, DELETE, and INSERT INTO...SELECT. Additionally, we will delve into the concept of transactions and data integrity to ensure the consistency and reliability of our data.
Section 1: Modifying data with UPDATE and DELETE
The UPDATE statement is used to modify existing data in a table. It allows you to change the values of specific columns in one or more rows. Let's consider an example using the "Customers" table:
sqlCopy code
UPDATE Customers SET City = 'New York' WHERE Country = 'USA';
In this example, the UPDATE statement is used to change the City column value to "New York" for all rows where the Country is "USA". This demonstrates how you can update data based on certain conditions.
The DELETE statement is used to remove data from a table. It allows you to delete specific rows based on specified conditions. Let's consider an example using the "Orders" table:
sqlCopy code
DELETE FROM Orders WHERE OrderDate < '2022-01-01';
In this example, the DELETE statement is used to remove all rows from the "Orders" table where the OrderDate is before January 1, 2022. This illustrates how you can delete data based on specific criteria.
Section 2: Inserting data from one table into another using INSERT INTO...SELECT
The INSERT INTO...SELECT statement allows you to insert data from one table into another. It is useful when you want to populate a target table with data from a source table or perform data transformations during the process. Let's consider an example:
sqlCopy code
INSERT INTO NewCustomers (FirstName, LastName, City) SELECT FirstName, LastName, City FROM Customers WHERE Country = 'USA';
In this example, we are inserting data into the "NewCustomers" table by selecting the FirstName, LastName, and City columns from the "Customers" table. We only insert the data where the Country is "USA". This demonstrates how you can selectively insert data from one table into another based on specific conditions.
Section 3: Transactions and data integrity
Transactions are essential for maintaining data integrity and ensuring the reliability of your database operations. A transaction is a sequence of database operations that should be executed as a single, indivisible unit. If any part of the transaction fails, all changes made within the transaction can be rolled back to maintain the consistency of the data.
To ensure data integrity, you can use the COMMIT statement to permanently save the changes made within a transaction, or the ROLLBACK statement to undo the changes and revert to the previous state.
sqlCopy code
BEGIN TRANSACTION; UPDATE Accounts SET Balance = Balance - 100 WHERE AccountNumber = 12345; INSERT INTO Transactions (AccountNumber, Amount) VALUES (12345, 100); COMMIT;
In this example, we start a transaction using the BEGIN TRANSACTION statement. We then update the balance in the "Accounts" table and insert a new record into the "Transactions" table. Finally, we use the COMMIT statement to save the changes made within the transaction.
Here's an example of using the ROLLBACK statement to undo changes made within a transaction:
sqlCopy code
BEGIN TRANSACTION;
UPDATE Employees SET Salary = Salary * 1.1
WHERE Department = 'Finance';
INSERT INTO AuditLogs (TableName, Operation) VALUES ('Employees', 'Salary update');
-- If an error occurs or a condition is not met, roll back the transaction
IF SomeCondition = 1
BEGIN
ROLLBACK;
END
ELSE
BEGIN
COMMIT;
END;
In this example, we begin a transaction using the BEGIN TRANSACTION statement. We update the salaries of employees in the Finance department and insert a record into the AuditLogs table to track the operation. However, we include a condition using the IF statement to check if a certain condition (represented by SomeCondition) is met. If the condition is true, we execute the ROLLBACK statement to undo the changes made within the transaction. Otherwise, if the condition is false, we execute the COMMIT statement to permanently save the changes.
Using the ROLLBACK statement ensures that if an error occurs or a specific condition is not met, any changes made within the transaction will be rolled back, maintaining data integrity and consistency. Remember to customize the condition and table names based on your specific scenario.
By understanding and effectively using transactions, you can ensure data consistency and integrity within your database.
Throughout this chapter, we have provided detailed explanations and practical examples to help you understand the various aspects of data manipulation in SQL. Practice these examples and experiment with different scenarios to reinforce your learning. Remember to consider data integrity and use transactions when necessary to maintain the reliability of your database operations.