Once your database structure is defined using Data Definition Language (DDL), the next logical step is interacting with the data it stores. This is where Data Manipulation Language (DML) comes into play.
DML allows you to perform operations that modify, retrieve, and manage data within your database tables. These operations are essential for day-to-day business logic execution in transactional and analytical systems.
Table of Contents
In this chapter, we’ll explore DML commands in great detail — including SELECT, INSERT, UPDATE, and DELETE — along with practical examples, use cases, performance tips, and common pitfalls.
What is DML?
Data Manipulation Language (DML) refers to the SQL subset that is used to:
- Retrieve data (
SELECT) - Insert new records (
INSERT) - Modify existing records (
UPDATE) - Remove records (
DELETE)
DML operations often happen within transactions, allowing rollback and commit behavior to maintain data integrity.
Unlike DDL commands, DML commands do not change the database structure — only the contents of the tables.
1. SELECT – Retrieving Data from Tables
The SELECT statement is arguably the most used SQL command. It allows users to query one or more tables to retrieve data.
Basic Syntax:
SELECT column1, column2, ... FROM table_name;
Example:
SELECT Name, Email FROM Customers;
Filtering Data:
SELECT * FROM Orders WHERE OrderDate >= '2024-01-01';
Sorting Results:
SELECT * FROM Products ORDER BY Price DESC;
Limiting Results:
SELECT * FROM Employees LIMIT 5;
Using Aliases:
SELECT Name AS FullName, Salary * 12 AS AnnualSalary FROM Employees;
Aggregate Functions:
SELECT COUNT(*) AS TotalOrders FROM Orders;
SELECT AVG(Salary) AS AvgSalary FROM Employees;
Grouping and Filtering Groups:
SELECT Department, COUNT(*) AS TotalEmployees
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 5;
Joins:
SELECT Orders.OrderID, Customers.Name
FROM Orders
JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
Subqueries:
SELECT Name FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products);
The SELECT command is incredibly powerful and forms the foundation of SQL-based data analysis.
2. INSERT – Adding New Records
The INSERT command is used to add new rows of data to a table.
Basic Syntax:
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
Example:
INSERT INTO Products (Name, Price, Stock)
VALUES ('Wireless Mouse', 999.00, 50);
Insert Multiple Rows:
INSERT INTO Products (Name, Price, Stock)
VALUES
('Laptop Stand', 1200.00, 30),
('Webcam', 2200.00, 20);
Insert from Another Table:
INSERT INTO ArchivedOrders (OrderID, CustomerID, OrderDate)
SELECT OrderID, CustomerID, OrderDate FROM Orders WHERE OrderDate < '2023-01-01';
Inserting with DEFAULT values:
INSERT INTO Customers (Name, Email) VALUES ('John Doe', DEFAULT);
Best Practices:
- Specify column names for clarity
- Avoid hardcoding too many values in application code
- Validate data before inserting (e.g., via constraints or application logic)
3. UPDATE – Modifying Existing Records
The UPDATE command changes existing data in a table. It’s a powerful but dangerous command if used without caution.
Syntax:
UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
Example:
UPDATE Employees SET Salary = Salary * 1.10 WHERE Department = 'Marketing';
Conditional Updates:
UPDATE Orders SET Status = 'Cancelled' WHERE OrderDate < '2023-01-01';
Updating Using Subquery:
UPDATE Products
SET Price = Price * 1.05
WHERE ProductID IN (
SELECT ProductID FROM Sales WHERE Year = 2023
);
Warning:
UPDATE Employees SET Salary = 0; -- Updates all records!
Avoid missing WHERE clauses in UPDATE or you might update all rows.
Tips:
- Always preview changes using a
SELECTfirst - Use transactions for large updates
- Use indexes on columns used in
WHEREfor performance
4. DELETE – Removing Records
The DELETE command is used to remove rows from a table based on a condition.
Syntax:
DELETE FROM table_name WHERE condition;
Example:
DELETE FROM Customers WHERE LastLogin < '2022-01-01';
Delete All Rows:
DELETE FROM Employees; -- Caution: Deletes everything
For faster performance with large tables, use TRUNCATE instead (discussed in DDL).
Delete with Join:
DELETE Orders FROM Orders
JOIN Customers ON Orders.CustomerID = Customers.CustomerID
WHERE Customers.Region = 'North';
DELETE vs TRUNCATE:
| Feature | DELETE | TRUNCATE |
|---|---|---|
| Row by row logging | Yes | Minimal |
| Can be rolled back | Yes | No (in some systems) |
| Triggers fire? | Yes | No |
| Resets identity? | No | Yes (in most systems) |
Transactions in DML
DML statements are often used in transactions to group multiple operations.
Example:
START TRANSACTION;
UPDATE Accounts SET Balance = Balance - 1000 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 1000 WHERE AccountID = 2;
COMMIT;
If any step fails, use ROLLBACK; to undo the entire operation.
Transactions provide Atomicity, Consistency, Isolation, and Durability (ACID).
Real-World Example: Employee Leave Management
Imagine managing a system where employees apply for leave:
-- Insert a new leave request
INSERT INTO LeaveRequests (EmployeeID, StartDate, EndDate, Status)
VALUES (102, '2024-06-01', '2024-06-07', 'Pending');
-- Approve the leave
UPDATE LeaveRequests SET Status = 'Approved' WHERE RequestID = 15;
-- Delete expired leave requests
DELETE FROM LeaveRequests WHERE EndDate < '2023-01-01';
-- View upcoming leaves
SELECT e.Name, l.StartDate, l.EndDate
FROM Employees e
JOIN LeaveRequests l ON e.EmployeeID = l.EmployeeID
WHERE l.Status = 'Approved';
DML commands are at the heart of such systems — making them dynamic, responsive, and user-driven.
Best Practices for DML
- Always use WHERE with UPDATE/DELETE
- Preview your data using SELECT before modifying it
- Use transactions for multiple changes
- Index columns frequently used in WHERE clauses
- Log DML activity for audit purposes
- Validate inputs before insertion to prevent SQL injection
- Use stored procedures for complex operations
Common Mistakes to Avoid
| Mistake | Consequence |
|---|---|
| No WHERE clause in UPDATE/DELETE | All rows affected |
| Using NULLs without checks | May break business logic |
| Inserting invalid data types | Errors or truncation |
| Overwriting fields accidentally | Loss of original values |
| Ignoring transactions | Partial or inconsistent updates |
Summary
DML commands form the lifeblood of SQL-based systems. They allow you to:
- Fetch data (
SELECT) - Add new records (
INSERT) - Update existing values (
UPDATE) - Remove obsolete data (
DELETE)
These operations must be used carefully, with transactions, conditions, and validation to ensure data integrity and system stability.
In the next chapter, we’ll turn our focus to SQL SELECT in detail, breaking down all its clauses — including WHERE, GROUP BY, HAVING, ORDER BY, and JOINs — into powerful querying techniques for extracting insights from your data.