Mastering SQL: The Ultimate Guide to Querying and Managing Relational Databases

📘 Chapter 3: Data Manipulation Language (DML)

🧠 What is DML?

Data Manipulation Language (DML) is a core component of SQL used to manage data within relational databases. It includes commands that let you:

• Insert new data
• Update existing data
• Delete records
• Retrieve data (via SELECT)

DML commands operate on table data, not the structure (which is handled by DDL).

🔧 1. INSERT – Adding New Data

✅ Basic Syntax

sql

INSERT INTO table_name (column1, column2, ...)

VALUES (value1, value2, ...);

✅ Example

sql

INSERT INTO Students (StudentID, Name, Age)

VALUES (1, 'Alice', 22);

🔹 Insert Multiple Rows

sql

INSERT INTO Students (StudentID, Name, Age)

VALUES

(2, 'Bob', 20),

(3, 'Carol', 23);

📋 Table: Students

⚠️ Best Practices

• Ensure data types match column types.
• Use explicit column lists to avoid errors.
• Use constraints to avoid inserting invalid data.

📝 2. SELECT – Retrieving Data

The SELECT statement is the most used SQL command to query data.

✅ Select All Columns

sql

SELECT * FROM Students;

✅ Select Specific Columns

sql

SELECT Name, Age FROM Students;

✅ Filtering with WHERE

sql

SELECT * FROM Students

WHERE Age > 21;

✅ Sorting Results

sql

SELECT * FROM Students

ORDER BY Age DESC;

✅ Using Aliases

sql

SELECT Name AS StudentName FROM Students;

📋 SELECT with Clauses

✅ Aggregate Functions

sql

SELECT COUNT(*), AVG(Age)

FROM Students;

🔄 3. UPDATE – Modifying Data

✅ Syntax

sql

UPDATE table_name

SET column1 = value1, column2 = value2

WHERE condition;

✅ Example

sql

UPDATE Students

SET Age = 24

WHERE StudentID = 1;

⚠️ Important Note

Always use a WHERE clause unless you want to update all records.

✅ Update Multiple Columns

sql

UPDATE Students

SET Name = 'Alice Smith', Age = 23

WHERE StudentID = 1;

📋 Table After Update

🗑️ 4. DELETE – Removing Data

✅ Syntax

sql

DELETE FROM table_name

WHERE condition;

✅ Example

sql

DELETE FROM Students

WHERE StudentID = 2;

⚠️ Without a WHERE clause, all rows will be deleted.

🔁 5. DML vs DDL – Quick Recap

🧪 Real-World Use Case

Let’s say you’re managing an e-commerce database.

✅ Products Table

sql

CREATE TABLE Products (

    ProductID INT PRIMARY KEY,

    Name VARCHAR(50),

    Price DECIMAL(10, 2),

    InStock INT

);

✅ Insert Data

sql

INSERT INTO Products VALUES

(1, 'Laptop', 799.99, 5),

(2, 'Mouse', 25.00, 30),

(3, 'Keyboard', 45.00, 15);

✅ Increase Price of All Products by 10%

sql

UPDATE Products

SET Price = Price * 1.10;

✅ Delete Out-of-Stock Items

sql

DELETE FROM Products

WHERE InStock = 0;

✅ View Expensive Products

sql

SELECT * FROM Products

WHERE Price > 100

ORDER BY Price DESC;

🔍 Additional Filtering Techniques

✅ BETWEEN

sql

SELECT * FROM Products

WHERE Price BETWEEN 30 AND 100;

✅ LIKE (Pattern Matching)

sql

SELECT * FROM Products

WHERE Name LIKE 'K%'; -- Starts with K

✅ IN

sql

SELECT * FROM Students

WHERE Age IN (20, 22, 23);

🔐 Transaction Support with DML

DML statements can be wrapped in transactions for safety.

sql

START TRANSACTION;

UPDATE Products

SET Price = 0

WHERE ProductID = 1;

ROLLBACK; -- Undo changes

Use COMMIT to save changes permanently.

📋 Transaction Commands

✅ Summary Table