Data Manipulation Language DML
Data Manipulation Language (DML) is a subset of SQL (Structured Query Language) used to manage and manipulate data stored in a relational database. DML focuses on the operations that can be performed on the data within database tables. It enables users to retrieve, insert, update, and delete data. Here’s a comprehensive overview of DML:
*1. Overview of Data Manipulation Language (DML)*
DML allows users to interact with data in a database in various ways, including querying data, adding new records, modifying existing records, and removing records.
*2. Core DML Operations*
#### *A. SELECT*
**Purpose**: Retrieve data from one or more tables.
**Syntax**:
```sql
SELECT column1, column2, ...
FROM table_name
WHERE condition;
```
**Example**:
```sql
SELECT first_name, last_name
FROM employees
WHERE department = 'Sales';
```
#### *B. INSERT*
**Purpose**: Add new rows to a table.
**Syntax**:
```sql
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
```
**Example**:
```sql
INSERT INTO employees (first_name, last_name, department)
VALUES ('John', 'Doe', 'Marketing');
```
#### *C. UPDATE*
**Purpose**: Modify existing rows in a table.
**Syntax**:
```sql
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
```
**Example**:
```sql
UPDATE employees
SET department = 'HR'
WHERE employee_id = 123;
```
#### *D. DELETE*
**Purpose**: Remove rows from a table.
**Syntax**:
```sql
DELETE FROM table_name
WHERE condition;
```
**Example**:
```sql
DELETE FROM employees
WHERE employee_id = 123;
```
*3. Advanced DML Features*
#### *A. Joins*
**Purpose**: Retrieve data from multiple tables based on related columns.
**Types**:
**INNER JOIN**: Retrieves records with matching values in both tables.
*LEFT JOIN* (or LEFT OUTER JOIN): Retrieves all records from the left table, and matching records from the right table.
*RIGHT JOIN* (or RIGHT OUTER JOIN): Retrieves all records from the right table, and matching records from the left table.
*FULL JOIN* (or FULL OUTER JOIN): Retrieves records when there is a match in one of the tables.
**Example**:
```sql
SELECT employees.first_name, departments.department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.department_id;
```
#### *B. Subqueries*
**Purpose**: Use a query within another query to perform complex data retrieval.
**Example**:
```sql
SELECT first_name, last_name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE department_name = 'Sales');
```
#### *C. Aggregate Functions*
**Purpose**: Perform calculations on data to return summarized results.
**Functions**:
**COUNT()**: Counts the number of rows.
**SUM()**: Sums the values of a numeric column.
**AVG()**: Computes the average value of a numeric column.
**MAX()**: Finds the maximum value.
**MIN()**: Finds the minimum value.
**Example**:
```sql
SELECT department, COUNT(*)
FROM employees
GROUP BY department;
```
#### *D. Transactions*
**Purpose**: Ensure that a sequence of DML operations is executed as a single unit of work.
**Commands**:
**BEGIN TRANSACTION**: Starts a transaction.
**COMMIT**: Saves all changes made during the transaction.
**ROLLBACK**: Reverts changes if an error occurs.
**Example**:
```sql
BEGIN TRANSACTION;
UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Marketing';
DELETE FROM employees
WHERE employee_id = 123;
COMMIT;
```
*4. DML and Data Integrity*
#### *A. Constraints*
**Purpose**: Maintain data integrity and enforce rules on data.
**Types**:
**PRIMARY KEY**: Uniquely identifies each record in a table.
**FOREIGN KEY**: Ensures referential integrity between tables.
**UNIQUE**: Ensures all values in a column are unique.
**CHECK**: Ensures that all values in a column satisfy a specific condition.
**NOT NULL**: Ensures that a column cannot have NULL values.
*5. Best Practices for Using DML*
#### *A. Use Transactions*
Ensure data consistency by using transactions for operations that involve multiple steps.
#### *B. Validate Input*
Always validate user inputs to prevent SQL injection attacks and ensure data integrity.
#### *C. Optimize Queries*
Use indexing and efficient query design to improve performance and reduce execution time.
#### *D. Backup Data*
Regularly back up data to prevent loss and ensure recoverability.
