Machine Learning
Data Manipulation Language

Data Manipulation Language

Jeevan Henry Dsouza's photo
Jeevan Henry Dsouza
·

2 min read

Table of contents

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:

     SELECT column1, column2 FROM table_name WHERE condition;

  2. INSERT: Used to add new records (rows) into a table.

    Example:

     INSERT INTO table_name (column1, column2) VALUES (value1, value2);

  3. UPDATE: Used to modify existing records in a table.

    Example:

     UPDATE table_name SET column1 = new_value1, column2 = new_value2 WHERE condition;

  4. DELETE: Used to remove records from a table.

    Example:

     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:

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:

     -- Retrieve employees with age greater than 30
 SELECT emp_id, emp_name, emp_age
 FROM employees
 WHERE emp_age > 30;

  2. UPDATE with WHERE:

     -- 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:

     -- 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.

SQLDataManipulationDML