Data manipulation

✅ What is Data Manipulation?

Data Manipulation refers to adding, updating, deleting, and retrieving data from database tables.

In SQL, Data Manipulation Language (DML) provides commands that allow users to manage the data stored in tables.

---

🎯 Key SQL DML Commands:

Command	Description
SELECT	Retrieve data from one or more tables.
INSERT	Add new records into a table.
UPDATE	Modify existing records in a table.
DELETE	Remove records from a table.

---

📊 Detailed Explanation with Examples:

---

1. SELECT - Retrieve Data

Use SELECT to query data from tables.

sqlCopyEditSELECT first_name, last_name FROM employees;

• Retrieves first and last names of all employees.

✅ Add conditions:

sqlCopyEditSELECT * FROM employees
WHERE department = 'IT';

• Fetch employees in the IT department.

---

2. INSERT - Add New Data

Use INSERT INTO to add new records.

sqlCopyEditINSERT INTO employees (first_name, last_name, department, salary)
VALUES ('Alice', 'Johnson', 'Marketing', 60000);

• Adds a new employee to the employees table.

🚨 Note: Make sure to respect data types and constraints like NOT NULL or UNIQUE when inserting.

---

3. UPDATE - Modify Existing Data

Use UPDATE to change existing records.

sqlCopyEditUPDATE employees
SET salary = 65000
WHERE employee_id = 10;

• Updates salary for employee with ID = 10.

⚠️ Warning: Without WHERE, it will update all rows in the table. Always use WHERE carefully.

---

4. DELETE - Remove Data

Use DELETE to remove rows from a table.

sqlCopyEditDELETE FROM employees
WHERE employee_id = 10;

• Deletes employee record with ID = 10.

⚠️ Warning: Like UPDATE, avoid running DELETE without a WHERE clause unless you intend to delete all records.

sqlCopyEdit-- Dangerous command, deletes all records:
DELETE FROM employees;

---

⚙️ Combined Example (Full Data Manipulation Workflow):

sqlCopyEdit-- 1. Add a new employee
INSERT INTO employees (first_name, last_name, department, salary)
VALUES ('John', 'Doe', 'HR', 50000);

-- 2. Give a salary raise to HR employees
UPDATE employees
SET salary = salary + 5000
WHERE department = 'HR';

-- 3. List HR employees
SELECT * FROM employees
WHERE department = 'HR';

-- 4. Remove an employee who left the company
DELETE FROM employees
WHERE employee_id = 15;

---

📦 Use Cases of Data Manipulation:

Situation	SQL Command Used
Add new employee	INSERT
Update salary for specific department	UPDATE
Delete terminated employee record	DELETE
List all employees in specific department	SELECT with WHERE

---

🏷️ Common Clauses with DML Commands:

Clause	Purpose	Example
WHERE	Filter rows for action	DELETE FROM employees WHERE id = 10;
ORDER BY	Sort the output	SELECT * FROM employees ORDER BY salary;
LIMIT	Limit number of rows returned	SELECT * FROM employees LIMIT 5;
SET	Define column updates in UPDATE	UPDATE employees SET salary = 60000;

---

✅ Good Practices for Data Manipulation:

1. Always use WHERE with UPDATE and DELETE to avoid affecting unintended rows.

2. Test queries with SELECT before running destructive actions.

3. Use transactions (BEGIN, COMMIT, ROLLBACK) for critical operations to ensure safe execution.

4. Backup important tables before large-scale updates/deletes.

5. Check constraints (like foreign keys, NOT NULL) to avoid insertion errors.

---

⚙️ Transactions for Safe Data Manipulation:

sqlCopyEditBEGIN;

UPDATE employees
SET salary = salary + 1000
WHERE department = 'Sales';

-- If something goes wrong
-- ROLLBACK;

-- If everything is fine
COMMIT;

---

🔑 Summary of DML Commands:

Command	Syntax Example
SELECT	SELECT col1, col2 FROM table WHERE condition;
INSERT	INSERT INTO table (col1, col2) VALUES (val1, val2);
UPDATE	UPDATE table SET col1 = val1 WHERE condition;
DELETE	DELETE FROM table WHERE condition;