SQL Data Manipulation (DML)

Learn to modify data in your tables using INSERT, UPDATE, and DELETE.

SQL Intermediate 45 min

🔐 Introduction: Database Abstraction

We have learned how to query and manipulate data. Now we explore features that improve database **performance**, **security**, and **organizational structure**—namely, Views and Transactions.

A **View** is essential for security, acting as a virtual table that shows only specific columns to specific users. **Transactions** ensure that complex changes are completed fully, or not at all.

👓 Topic 1: SQL Views (Virtual Tables)

A **View** is a virtual table whose content is defined by a query. Unlike actual tables, a View does not store data itself; it simply saves the **query definition**.

✅ Benefits of Views:

Security: Restrict users from seeing sensitive columns (like salary).
Simplicity: Complex join queries can be saved as a single, simple View name.
Reusability: Use the same query logic multiple times without rewriting it.

💻 Example: Creating a View for Public Access

-- Create a View that excludes the Salary column
CREATE VIEW Public_Employee_Info AS
SELECT EmployeeID, Name, Department
FROM Employees
WHERE Department != 'HR';

-- Now query the View just like a normal table
SELECT * FROM Public_Employee_Info;

🔍 Topic 2: SQL Indexes (Performance Booster)

An **Index** is a structure that improves the speed of data retrieval operations on a table. Think of it like the **index in the back of a textbook**: it tells you exactly which page to go to find a topic, rather than reading the whole book.

⚠️ Index Trade-offs:

Indexes speed up SELECT queries but slow down INSERT, UPDATE, and DELETE operations because the index structure must be maintained every time the data changes.

💻 Example: Creating an Index

-- Create an index on the LastName column
CREATE INDEX idx_lastname
ON Customers (LastName);

-- Create a unique index (ensures no duplicate values in that column)
CREATE UNIQUE INDEX idx_email ON Users (Email);

🏦 Topic 3: Transactions & The ACID Test

A **Transaction** is a sequence of SQL statements that are treated as a single logical unit of work. The transaction must either **all succeed** (commit) or **all fail** (rollback).

This is critical for banking: when transferring money, you must debit Account A *and* credit Account B. If the credit fails, the whole transaction must fail (rollback).

🔬 The ACID Properties:

Atomicity: All or nothing (the whole transaction succeeds or fails).
Consistency: The database remains valid (rules are maintained).
Isolation: Concurrent transactions don't interfere with each other.
Durability: Changes are permanent after the transaction commits.

💻 Example: Basic Transaction Syntax

START TRANSACTION; -- Begin the single unit of work
UPDATE Accounts SET Balance = Balance - 100 WHERE ID = 1;
UPDATE Accounts SET Balance = Balance + 100 WHERE ID = 2;
COMMIT; -- Save changes permanently

-- If an error occurs, you run: ROLLBACK;

📚 Module Summary

View: A virtual table (saved query) used for security and simplicity.
Index: Speeds up SELECT queries but slows down DML operations (Insert/Update/Delete).
Transaction: A single, atomic unit of work (All or Nothing).
ACID: The four guarantees (Atomicity, Consistency, Isolation, Durability) that protect data integrity.

🤔 Interview Q&A

Tap on the questions below to reveal the answers.

The primary advantage is Security and Data Abstraction. It allows you to expose complex or filtered data (e.g., hiding salary column) to end-users without giving them direct access to the base tables.

Indexes create a trade-off: they make data retrieval (SELECT) faster but make data modification (INSERT/UPDATE/DELETE) slower because the database has to update the index structure every time data changes.

The Atomicity property is critical. It guarantees that the transfer is treated as a single unit: either the debit from one account and the credit to the other succeed, or the entire transaction fails and rolls back.

COMMIT saves all changes made during the transaction permanently to the database. ROLLBACK undoes all changes since the transaction started, restoring the database to its initial state.