SQL (DML) data handling commands

Introduction

This article shows an extensive explanation of SQL (DML) data handling commands. DML commands are essential for managing and changing data on databases. It is an invaluable tool for users of novice databases and experts, explaining the functions of DML commands such as how to insert, select, update and delete, as well as their examples of syntax and transaction control.

Overview

• Understand DML commands and define the purpose and types of DML commands in SQL.
• Make data handling how to insert, select, update and delete data in a database using SQL commands.
• Explain the difference between the high -level DML (non -procedure) and low level DML (procedural).
• Use transaction control commands (Commit, Rollback, SavePoint) to maintain data consistency.

What are SQL (DML) data handling commands?

The data can be added, delete, update and select within the database instance using SQL data handling language. DML Manages all kinds of data modification within a database. The following commands are included in the SQL DML part:

• Insert
• Select
• Update
• Eliminate
• DML types

There are two main types of DML:

High -level DML or not

Alternatively called declarative DML or established in time, it allows users to specify the data they require without providing specific instructions on how to obtain it. Select and other SQL commands are some instances.

DML of low level or procedural

Also known as Imperative DML or registration DML in time, ask users to specify the data they need and the best way to get it. This class is usually included in general purpose programming languages. Examples of procedural extensions are PL/SQL of Oracle.

Before examining the DML commands, we first create the tables we will be using for the examples below

Creating a table to implement DML commands

Use the command below 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’s now explore the DML commands in detail.

Insert command

The data can be inserted into a table using the insertion instruction, which is provided by SQL. Using the insertion statement, you can:

• Create a table with only one row in it.
• Add more than one row to a table
• Copy rows from another table

Insertion of a single row

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

• The number of specified columns must match the number of values ​​indicated.
• The database system ensures that all integrity restrictions (for example, 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);

Insertion of multiple rows

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

Example: Insert values ​​both in the table of departments and of the employees

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);

Copy rows from another table

You can use the insertion statement to consult 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 consult data from a table, use the SQL Select instruction, which has the syntax to group data, join tables, select rows, select columns and make simple calculations.

SELECT column1, column2, ...
FROM table_name;

To recover all columns, use select *.

Example

SELECT first_name, last_name
FROM employees;

Complete 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;

Consultation execution order:

1. Of and joins
2. Where
3. Group by
4. Have
5. Select
6. Different
7. Order by
8. Limit/compensation

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, use the update instruction.

Syntax:

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

In this syntax:

• Indicate the table you want to update in the update clause.
• Specify the columns to modify in the set clause. The columns that do not appear in the SET clause will save original values.
• Specify which rows will be updated in the where clause, any row that causes the condition in which to evaluate the true will be modified.
• Because the Where clause is optional, therefore, if it omits it, all the ranks of the table will be affected.

Example of a single row update

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 subcontracting 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;

Example of update of multiple rows

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

Use select command to view the table

SELECT * FROM employees
WHERE department_id = 2;

Remove the command

To remove one or more rows from a table, you can use the deleted instruction.

Syntax:

DELETE FROM table_name
WHERE condition;

In this syntax:

• Provide the name of the table where you want to remove the rows.
• Specify the condition in the Where clause to identify the rows to be eliminated.
• If you omit the Where clause, the system will remove all the rows in the table. Therefore, you should always use the removal statement with caution.
• Delete statement returns the number of rows removed.

Example of a single row removal

DELETE FROM employees
WHERE employee_id = 3;

You will get a mistake about it because it is removed.

Example of removal of multiple rows

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

Automatically remove rows of related tables (equivalent)

Most database systems support foreign key restriction, automatically removing rows in foreign key tables when you remove a row from a table.

Example

DELETE FROM departments
WHERE department_id = 4;

You can see that the system has removed it from both tables.

DML features

• Data queries: Perform data queries only interpretation.
• Data Handling: It is used to select, insert, delete and update data in a database.
• Integration: It can be integrated with the control of transactions to ensure the integrity of the data.

Transaction Control

Any modification made by a DML statement is considered a transaction and must be controlled by transaction control language instructions (TCL) to ensure data integrity and coherence. TCL commands include commitment, setback and savepoint.

Advantages of DML

• Data modification: It allows for efficient data handling within the database.
• User Interaction: Provides an easy -to -use interface for data operations.
• Variety of vendors: It offers various features depending on the database seller.

DML disadvantages

• Structure limitation: cannot alter the database structure.
• See Limitation: You can hide certain columns in the tables.
• Data Access: Limited in the creation or removal of lists or sections.

Conclusion

DML commands in SQL are crucial to data handling and recovery. By dominating, updating, delete and selecting commands, database users and programmers can manage and interact efficiently with your data. Understanding the syntax, the use cases and the best practices of these commands ensures precise and effective database operations.

Frequent questions