Data manipulation operations

📌 What is Data Manipulation?

Data Manipulation refers to the process of inserting, updating, retrieving, and deleting data in a relational database. In SQL, these operations are part of DML (Data Manipulation Language) commands, which operate on the data stored within the database tables.

---

🔑 Key Data Manipulation Operations (DML Commands)

Operation	SQL Command Used	Purpose
Insert Data	INSERT INTO	Add new records to a table
Retrieve Data	SELECT	Query and fetch records from a table
Update Data	UPDATE	Modify existing records in a table
Delete Data	DELETE FROM	Remove records from a table

---

✅ Detailed Overview of Each Operation

---

1. INSERT INTO - Add New Records

• Purpose: Insert new data rows into a table.

Syntax:

sqlCopyEditINSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

Example:

sqlCopyEditINSERT INTO employees (name, age, position, salary)
VALUES ('David Brown', 35, 'Project Manager', 90000);

---

2. SELECT - Retrieve Data

• Purpose: Retrieve data from one or more tables.

Syntax:

sqlCopyEditSELECT column1, column2 FROM table_name WHERE condition;

Example:

sqlCopyEditSELECT name, position FROM employees WHERE salary > 50000;

• Fetch all columns:

sqlCopyEditSELECT * FROM employees;

---

3. UPDATE - Modify Existing Records

• Purpose: Update existing data in a table.

Syntax:

sqlCopyEditUPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

Example:

sqlCopyEditUPDATE employees
SET salary = 95000
WHERE name = 'David Brown';

⚠️ Note: Always use WHERE to target specific records and avoid updating the entire table.

---

4. DELETE FROM - Remove Records

• Purpose: Delete records from a table.

Syntax:

sqlCopyEditDELETE FROM table_name WHERE condition;

Example:

sqlCopyEditDELETE FROM employees WHERE name = 'David Brown';

⚠️ Warning: Without WHERE, all records in the table will be deleted:

sqlCopyEditDELETE FROM employees; -- Deletes all data

---

🔍 Additional Data Manipulation Concepts

✅ Filtering Data (WHERE Clause)

sqlCopyEditSELECT * FROM employees WHERE position = 'Developer';

✅ Sorting Data (ORDER BY Clause)

sqlCopyEditSELECT * FROM employees ORDER BY salary DESC;

✅ Limiting Results (LIMIT Clause - MySQL, PostgreSQL)

sqlCopyEditSELECT * FROM employees LIMIT 5;

✅ Aggregating Data (GROUP BY and Aggregate Functions)

sqlCopyEditSELECT position, AVG(salary) AS average_salary
FROM employees
GROUP BY position;

✅ Joining Data from Multiple Tables (JOINs)

sqlCopyEditSELECT employees.name, departments.department_name
FROM employees
JOIN departments ON employees.department_id = departments.id;

---

📊 Example Table: employees

id	name	age	position	salary
1	John Doe	28	Developer	60000
2	Jane Smith	32	Manager	75000
3	Alice Johnson	30	Data Analyst	50000

---

DML Task	SQL Example
Add a new employee	INSERT INTO employees (name, age, position, salary) VALUES ('Bob Martin', 29, 'Tester', 45000);
Get all managers	SELECT * FROM employees WHERE position = 'Manager';
Increase salary	UPDATE employees SET salary = 80000 WHERE name = 'Jane Smith';
Remove an employee	DELETE FROM employees WHERE id = 3;

---

🎯 Why Data Manipulation is Important for Business Analysts

Reason	Explanation
Access and Analyze Data	Retrieve data to generate reports and insights.
Validate Requirements	Check existing data to ensure system compliance.
Support Testing and QA	Create and manipulate test data.
Facilitate Decision Making	Provide accurate data for strategic decisions.

---

🚀 Conclusion

• CRUD Operations form the foundation of interacting with relational databases.

• Knowing DML commands helps Business Analysts work effectively with technical teams.

• Essential for data analysis, system understanding, and requirement validation.