DML (Data Manipulation Language) in SQL: A Comprehensive Guide

In this post, will deep dive into DML and how to use it. DML stands for Data Manipulation Language, and it’s a category of SQL statements used for managing and manipulating the data within a database. DML statements allow you to perform actions such as inserting, updating, retrieving, and deleting data from database tables. Let’s explore DML statements in more detail with examples:

1. INSERT INTO:
   The INSERT INTO statement is used to add new records to a table. You can specify the values to be inserted into each column or provide a query that generates the values. Example:

   INSERT INTO Employees (FirstName, LastName, DepartmentID, Salary)
   VALUES ('John', 'Doe', 101, 60000);

   In this example, a new record with the given values is inserted into the “Employees” table.

2. UPDATE:
   The UPDATE statement is used to modify existing records in a table. You specify the columns to be updated and the new values. You can also include a WHERE clause to update specific rows based on certain conditions. Example:

   UPDATE Employees
   SET Salary = Salary * 1.10WHERE DepartmentID = 101;

   This example increases the salaries of employees in the “Employees” table who belong to the department with ID 101.

3. DELETE:
   The DELETE statement is used to remove records from a table based on specified conditions. Be cautious when using DELETE, as it permanently removes data. Example:

   DELETE FROM Employees
   WHERE DepartmentID = 102;

   This example deletes all records from the “Employees” table where the department ID is 102.

4. SELECT:
   The SELECT statement is used to retrieve data from one or more tables based on specified conditions. You can use various clauses like WHERE, ORDER BY, GROUP BY, and more to customize the query results. Example:

   SELECT FirstName, LastName, Salary
   FROM Employees
   WHERE DepartmentID = 101ORDER BY Salary DESC;

   This example retrieves the first names, last names, and salaries of employees from the “Employees” table who belong to department 101, ordered by salary in descending order.

DML statements are essential for interacting with the data stored in a database. They allow you to insert, modify, retrieve, and delete records, enabling you to manage and maintain the integrity of your data. It’s important to use DML statements carefully to avoid unintended changes to your data. The syntax and behavior of DML statements are fairly consistent across different database systems, although there might be some variations in advanced features and optimizations.