Blog

You are currently viewing DML statements in SQL

DML statements in SQL

SQL lets us interact with the database using a set of commands and instructions for performing specific tasks and queries. In this blog, we continue with our discussion on SQL statements, and moving forward this blog introduces the DML statements or Data Manipulation language statements in SQL.

Introduction to SQL DML

DML is a subset of SQL statements that allows interactions with relational databases and consists of a set of statements that lets us retrieve, insert, delete, and update our data within the dataset. DML is essential for managing and manipulating the data stored in our database tables. SQL DML helps us to interact with the data which allows them to perform multiple operations on the data. These statements operate on tables within the database using the SELECT, INSERT, UPDATE, and DELETE statements. DML also encompasses transactions providing a way to group multiple SQL statements into a logical unit. This ensures that data inconsistency and integrity are maintained for rollbacks if an error occurs.

Importance of SQL DML in database management

DML statements play a crucial role in the management and manipulation of data within the database and several reasons highlight the importance of DML statements in database management. DML allows us to retrieve specific data from the database through a SELECT statement which is vital for the generation of reports, analysis of trends, and business decisions through the stored data. Similarly, other DML statements help us in the insertion, updating, and deletion of data from the database. DML helps in ensuring data integrity and consistency in conjunction with transactions which helps to group multiple SQL statements and ensures that the changes are committed successfully. DML statements also provide data validation and constraints which helps maintain the integrity of data and enhances the performance of operations through different strategies through utilization of the optimization techniques.

INSERT Statement

The INSERT statement is used for adding new records or new rows to the table present in the database. This statement also allows us to specify the column values that need to be inserted for new records, either explicitly or through the selection of values through another table. Example syntax of how INSERT statement works with the other key components is:

INSERT INTO table_name (column1, column2, …)

VALUES (value1, value2, …);

The components of the statement are:

  1. INSERT INTO: The table name is used to indicate which table should receive the new records.
  2. table_name: The name of the table into which the new data will be entered.
  3. VALUES: In this sentence, the keyword “insert” designates the beginning of the values to be inserted.

Now that we are familiar with the correct format for adding new rows to a table, let’s look at some real-world instances of the INSERT command in action.

  1. Inserting a new record with explicit column values
INSERT INTO employees (first_name, last_name, age)
VALUES ('John', 'Doe', 30);

  • Inserting a new record with values that are selected from another table
INSERT INTO employees (first_name, last_name, age)
SELECT first_name, last_name, age
FROM temp_employees;
  • Insertion of multiple records at once
INSERT INTO employees (first_name, last_name, age)
VALUES ('Alice', 'Smith', 25), ('Bob', 'Johnson', 35), ('Emma', 'Davis', 28);
  • Inserting a record with default values for auto-incrementing primary key
INSERT INTO orders (order_date, total_amount)
VALUES ('2023-06-01', 500.00);

  • Inserting a record within all columns of the table
INSERT INTO products
VALUES ('P001', 'Product A', 'Category A', 100, '2023-06-01');

  • Inserting the records using a subquery
INSERT INTO orders (order_date, customer_id, total_amount)
SELECT order_date, customer_id, total_amount
FROM temp_orders
WHERE order_date >= '2023-01-01';

With the help of these examples, the INSERT stamen can be used for different purposes of adding new records to the table, which provides flexibility and usefulness to the SQL.

UPDATE Statement

The UPDATE statement in SQL DML is used for modifying the existing records or rows present in the table and through this, we are capable of updating specific columns with new values and applying the conditions which determine the rows needed to be updated. An overview of the syntax representing the UPDATE statement and key components is:

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

The components of this syntax are:

  1. UPDATE: It is the statement that specifies the table in which the records are to be updated.
  2. table_name: It is the name of the table in which the records needs to be updated.
  3. SET: It is the keyword that indicates the start of column-value assignments.
  4. WHERE: It’s a clause which allows us to specify conditions for determining the rows that need to be updated. If this condition is not given then all the rows present in the table will be updated.
  5. condition: This represents the condition specifying which rows need to be updated including the functions, comparison operators, and the logical operators.

Some of the examples of how the UPDATE statement can be applied for updating the records are:

  1. Updating a single column in every row.
UPDATE employees
SET salary = 5000;

  • Updating multiple columns in a single row.
UPDATE customers
SET first_name = 'Jane', last_name = 'Smith'
WHERE customer_id = 12345;

  • Updating a column while calculating the value and then replacing it.
UPDATE products
SET price = price * 1.1;

  • Updating the rows based on their conditions.
UPDATE orders
SET status = 'Completed'
WHERE total_amount > 1000;

  • Updating the rows through a subquery.
UPDATE inventory
SET quantity = quantity - 1
WHERE product_id IN (
  SELECT product_id
  FROM out_of_stock_products
);

  • Updating the rows with another table based on a join.
UPDATE employees
SET department_id = departments.department_id
FROM departments
WHERE employees.department_name = departments.department_name;

These are a few examples with the help of which we can easily modify existing records of a table and it also provides us flexibility for updating specific columns having new values and then applying the conditions for determining the rows that need to be updated.

DELETE Statement

The DELETE statement in the SQL DML is used for removing the existing records or rows from the table and allows us to put conditions for determining the rows that need to be deleted or if all the rows from the table need to be deleted. An overview of the DELETE statement through the syntax is:

DELETE FROM table_name
WHERE condition;

The components present in this syntax are:

  1. DELETE FROM: It is the statement that specifies the table from which the records are to be deleted.
  2. table_name: It denotes the table from which the records are to be deleted.
  3. WHERE: It is an optional clause that specifies the conditions determining rows to be deleted and if this clause is not used, then all the rows from the table will be deleted.
  4. Condition: The condition is used for specifying the rows that are to be deleted through the use of logical or comparison operators and functions.

Some of the usage examples of the DELETE statement are:

  1. Deleting all the rows present in the table:
DELETE FROM employees;

  • Deleting specific rows based on a condition:
DELETE FROM customers
WHERE last_purchase_date < '2022-01-01';

  • Deleting a single row:
DELETE FROM orders
WHERE order_id = 12345;

  • Deleting rows through a subquery:
DELETE FROM products
WHERE category_id IN (
  SELECT category_id
  FROM obsolete_categories
);

  • Deleting rows based on a join with another table:
DELETE FROM employees
USING departments
WHERE employees.department_id = departments.department_id
  AND departments.location = 'New York';

These are some examples that demonstrate how a DELETE statement can be used for removing the records from a table and also provides us the flexibility for deleting specific rows based on the condition or deleting the whole table. Using the DELETE statement helps us effectively manage our data by removing unwanted records which ensures cleanliness of data and optimizes the performance of the database.

Conclusion

In this article, we get to discuss the SQL DML statements which include the INSERT, UPDATE, and DELETE statements. These are very important statements that help us deal with all the functionalities of handling the data within the database, such as the insertion, updating, and deletion of records. These statements are very important in SQL queries and provide a lot of usefulness to SQL users. The use cases of the statements and how they are applied in different situations have been discussed in detail in this article along with examples. Thus, this article will help beginners to SQL as well as it will also help experienced users to update their concept in this language.

If you like the article and would like to support me, make sure to: