# Data Manipulation Language

Data Manipulation Language (DML) is a subset of SQL (Structured Query Language) that deals with retrieving, inserting, updating, and deleting data in a database. DML commands allow you to interact with the data stored in the database tables, making it possible to modify, add, or retrieve information as needed.

The primary DML commands in SQL are:

1. **SELECT**: Used to retrieve data from one or more tables based on specified criteria.This is called as filtering based on columns.
    
    Example:
    
    ```sql
    SELECT column1, column2 FROM table_name WHERE condition;
    ```
    
2. **INSERT**: Used to add new records (rows) into a table.
    
    Example:
    
    ```sql
    INSERT INTO table_name (column1, column2) VALUES (value1, value2);
    ```
    
3. **UPDATE**: Used to modify existing records in a table.
    
    Example:
    
    ```sql
    UPDATE table_name SET column1 = new_value1, column2 = new_value2 WHERE condition;
    ```
    
4. **DELETE**: Used to remove records from a table.
    
    Example:
    
    ```sql
    DELETE FROM table_name WHERE condition;
    ```
    

## Filtering Rows in SQL

Filtering rows in SQL is accomplished using the `WHERE` clause in combination with the `SELECT`, `UPDATE`, `DELETE`, or other SQL commands. The `WHERE` clause allows you to specify conditions that must be met for rows to be included in the result set or affected by data manipulation operations.

Here's the basic syntax of the `WHERE` clause:

```sql
SELECT column1, column2, ...
FROM table_name
WHERE condition;
```

The `WHERE` clause is placed after the `FROM` clause in the `SELECT` statement and is followed by the condition that determines which rows should be included in the query result.

Examples:

1. **SELECT with WHERE**:
    
    ```sql
    -- Retrieve employees with age greater than 30
    SELECT emp_id, emp_name, emp_age
    FROM employees
    WHERE emp_age > 30;
    ```
    
2. **UPDATE with WHERE**:
    
    ```sql
    -- Update salary for employees in the IT department
    UPDATE employees
    SET emp_salary = emp_salary * 1.1
    WHERE emp_department = 'IT';
    ```
    
3. **DELETE with WHERE**:
    
    ```sql
    -- Delete inactive users
    DELETE FROM users
    WHERE last_activity_date < '2023-01-01';
    ```
    

In each of these examples, the `WHERE` clause filters the rows based on specific conditions. Only rows that meet the specified conditions are included in the result set for the `SELECT` statement or affected by the `UPDATE` or `DELETE` operation.

The conditions in the `WHERE` clause can involve one or more columns, constants, and comparison operators (e.g., `=`, `<>`, `>`, `<`, `>=`, `<=`). You can also use logical operators (e.g., `AND`, `OR`, `NOT`) to create more complex conditions.

Filtering rows is a powerful feature in SQL that enables you to extract or manipulate specific subsets of data based on various criteria, allowing you to work with the data more efficiently and effectively.