SQL Data Manipulation Language (DML) Commands

Introduction

An extensive explanation of SQL Data Manipulation Language (DML) commands is given in this article. DML commands are essential for managing and changing data in databases. It is an invaluable tool for novice and expert database users alike, explaining the functions of DML commands like as INSERT, SELECT, UPDATE, and DELETE as well as their syntax examples and transaction control.

Overview

• Understand DML commands and define the purpose and types of DML commands in SQL.
• Perform Data Manipulation like Insert, select, update, and delete data in a database using SQL commands.
• Explain the difference between high-level (non-procedural) and low-level (procedural) DML.
• Use transaction control commands (COMMIT, ROLLBACK, SAVEPOINT) to maintain data consistency.

What is SQL Data Manipulation Language (DML) Commands?

Data can be added, removed, updated, and selected within the database instance using SQL Data Manipulation Language. DML manages all kinds of data modification within a database. The following commands are included in the DML part of SQL:

• INSERT
• SELECT
• UPDATE
• DELETE 
• DML Types

There are two main types of DML:

High-Level or Non-Procedural DML

Alternatively referred to as declarative or set-at-a-time DML, it allows users to specify the data that they require without providing specific instructions on how to obtain it. SELECT and other SQL commands are a few instances.

Low-Level or Procedural DML

Also known as imperative DML or record-at-a-time DML, it prompts users to specify the data they require and the best way to obtain it. This class is often included in general-purpose programming languages. Examples of procedural extensions are Oracle’s PL/SQL.

Before looking into DML commands let’s first create the tables which we will be using for the examples below

Creating a Table to Implement DML Commands

Use the below command to create a table:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    department_id INT,
    salary DECIMAL(10, 2),
    FOREIGN KEY (department_id) REFERENCES departments(department_id) ON DELETE CASCADE
);


CREATE TABLE employees_backup (
    employee_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    department_id INT,
    salary DECIMAL(10, 2)
);

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

Now using this table let’s see all DML commands

DML Commands

Let us now explore DML Commands in detail.

INSERT Command

Data can be inserted into a table using the INSERT statement, which is provided by SQL. Using the INSERT statement, you can:

• Create a table with just one row in it.
• Add more than one row to a table
• Copying Rows from Another Table

Single Row Insertion

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

• The number of columns specified must match the number of values provided.
• The database system ensures all integrity constraints (e.g., foreign keys, primary keys, NOT NULL) are satisfied before inserting the row.

Example: Inserting values in both tables

INSERT INTO departments (department_id, department_name)
VALUES (1, 'HR');
INSERT INTO employees (employee_id, first_name, last_name, email, department_id, salary)
VALUES (1, 'John', 'Doe', '[email protected]', 1, 50000.00);

Multiple Row Insertion

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

Example: Insert values into both departments and employees table

INSERT INTO departments (department_id, department_name)
VALUES 
    (2, 'Finance'),
    (3, 'IT'),
    (4, 'Sales'),
    (5, 'Marketing'),
    (6, 'Support'),
    (8, 'Content');

INSERT INTO employees (employee_id, first_name, last_name, email, department_id, salary)
VALUES 
    (2, 'Jane', 'Doe', '[email protected]', 2, 55000.00),
    (3, 'Mike', 'Smith', '[email protected]', 1, 60000.00),
    (4, 'Anna', 'Taylor', '[email protected]', 3, 70000.00),
    (5, 'Bob', 'Brown', '[email protected]', 4, 45000.00),
    (6, 'Alice', 'White', '[email protected]', 5, 48000.00),
    (7, 'Charlie', 'Black', '[email protected]', 6, 47000.00);

Copying Rows from Another Table

You can use the INSERT statement to query data from one or more tables and insert it into another table as follows:

INSERT INTO table2 (column1, column2)
SELECT column1, column2
FROM table1
WHERE condition;

Example

INSERT INTO employees_backup
SELECT * FROM employees
WHERE department_id = 1;

SELECT Command

To query data from a table, use the SQL SELECT statement, which has the syntax for grouping data, joining tables, selecting rows, selecting columns, and performing simple calculations.

SELECT column1, column2, ...
FROM table_name;

To retrieve all columns, use SELECT *.

Example

SELECT first_name, last_name
FROM employees;

Full Syntax

SELECT DISTINCT column1, AGG_FUNC(column_or_expression), ...
FROM table1
JOIN table2 ON table1.column = table2.column
WHERE condition
GROUP BY column
HAVING condition
ORDER BY column ASC|DESC
LIMIT count OFFSET count;

Order of Query Execution:

1. FROM and JOINs
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. DISTINCT
7. ORDER BY
8. LIMIT/OFFSET

Example with Multiple Clauses

SELECT department_name, AVG(salary) AS avg_salary
FROM employees
JOIN departments ON employees.department_id = departments.department_id
WHERE salary > 50000
GROUP BY department_name
HAVING AVG(salary) > 60000
ORDER BY avg_salary DESC
LIMIT 10;

UPDATE Command

To change existing data in a table, you use the UPDATE statement.

Syntax:

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

In this syntax:

• Indicate the table that you want to update in the UPDATE clause.
• Specify the columns to modify in the SET clause. The columns that are not listed in the SET clause will save original values.
• Specify which rows to update in the WHERE clause, any row that causes the condition in the WHERE to evaluate to true will be modified.
• Because the WHERE clause is optional, therefore, if you omit it, the all the rows in the table will be affected.

Single Row Update Example

UPDATE employees
SET last_name = 'Blue'
WHERE employee_id = 7;

Now let’s see the Updated column

Select*from employees WHERE employee_id = 7;

Using a Subquery in UPDATE

UPDATE employees
SET salary = (SELECT MAX(salary) FROM employees)
WHERE department_id = 3;

See the changes using this:

SELECT * FROM EMPLOYEES WHERE department_id = 3;

Multiple Rows Update Example

UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 2;

Use select command to see the table

SELECT * FROM employees
WHERE department_id = 2;

DELETE Command

To remove one or more rows from a table, you can use the DELETE statement.

Syntax:

DELETE FROM table_name
WHERE condition;

In this syntax:

• Provide the name of the table where you want to remove rows.
• Specify the condition in the WHERE clause to identify the rows that need to be deleted.
• If you omit the WHERE clause, the system will delete all rows in the table. Therefore, you should always use the DELETE statement with caution.
• The DELETE statement does return the number of rows deleted.

Single Row Deletion Example

DELETE FROM employees
WHERE employee_id = 3;

You will get an error about it because its deleted.

Multiple Rows Deletion Example

DELETE FROM employees
WHERE department_id IN (5, 6, 7);

Automatically DELETE rows from related tables (equivalent)

Most database systems support the foreign key constraint, automatically removing rows in the foreign key tables when you delete a row from a table.

Example

DELETE FROM departments
WHERE department_id = 4;

You can see that the system deleted it from both tables.

Characteristics of DML

• Data Queries : Performs interpret-only data queries.
• Data Manipulation : Used to select, insert, delete, and update data in a database.
• Integration : Can be integrated with transaction control for ensuring data integrity.

Transaction Control

Any modification made by a DML statement is considered a transaction and must be controlled by Transaction Control Language (TCL) statements to ensure data integrity and consistency. TCL commands include COMMIT, ROLLBACK, and SAVEPOINT.

Advantages of DML

• Data Modification: Allows for efficient data manipulation within the database.
• User Interaction: Provides a user-friendly interface for data operations.
• Vendor Variety: Offers various functionalities depending on the database vendor.

Disadvantages of DML

• Structure Limitation: Cannot alter the database structure.
• View Limitation: Can conceal certain columns in tables.
• Data Access: Limited in creating or deleting lists or sections.

Conclusion

DML commands in SQL are fundamental for data manipulation and retrieval. By mastering INSERT, UPDATE, DELETE, and SELECT commands, database users and programmers can efficiently manage and interact with their data. Understanding the syntax, use cases, and best practices of these commands ensures accurate and effective database operations.

Frequently Asked Questions

Janvi Kumari

Hi, I am Janvi, a passionate data science enthusiast currently working at Analytics Vidhya. My journey into the world of data began with a deep curiosity about how we can extract meaningful insights from complex datasets.