All About DML Commands in SQL

12 mins read2.2K Views Comment

Updated on Oct 9, 2023 18:45 IST

In SQL there are five different types of statements (DDL, DQL, DML, DCL and TCL) that are used while writing the queries. In this article, we will briefly discuss different DML statements in SQL.

DML commands in SQL stand for Data Manipulation Language. Using this, we can manipulate or change data in the tables. After creating a database and tables, we need to fill those tables with the data of our interest. After that, we may have to update or delete the stored data. All these actions come with specific needs. For example, suppose you are in charge of managing a book store. Hence you must have a sheet where you can insert the records for the new books coming in.

You can also explore: What are DCL Commands in SQL?

You can also explore: What are TCL Commands in SQL?

Then you have to update the sheet regularly to keep the current records for the selling of the books and to keep a check on the stock of the books. You may have to delete some of the records for the books that are all sold out. So, all these operations are Data Manipulation operations, where you can change the data in the table. Hence DML commands in SQL are used to insert new data, update the existing data and delete unwanted data.

Must Read: Introduction to SQL

Must Read: SQL ACID Properties

We will look into the concept of DML elaboratively using the example of the bookstore. Before that, let’s look into the DML statements.

The DML statements are –

SELECT – for selecting data from the table (This also comes under DQL – Data Query Language)
INSERT – for inserting data into the table
UPDATE – for updating data in the table
DELETE – for deleting data from a table

Let’s start with the DML statements one by one.

Table of Content

Brief Introduction to DML Commands in SQL
SELECT statement
INSERT statement
UPDATE statement
DELETE statement
Annexure

Brief Introduction to DML Commands in SQL

Let us continue with the example of a Book Store to get a brief about DML commands in SQL. Suppose that we have a database named as Book_Store having a table called Books. The table Books have the columns of name, author, genre, in_stock, sold_out, cost, and date. After creating the database and table, we will have something like this –

(To see the query code for the database and table creation, please refer to the annexure)

Now, the table is empty. Let us assume that we have stocked ten different books in our book store. Then, we need to insert the required information for all ten books in our table. This operation is achieved using the Insert statement. Thus, in our table, we have ten rows, which we call records, and seven columns, which we call fields.

Now suppose we want to apply some discount on a few books. Then using the SELECT statement, we need to select only those books on which we want to apply the discount. Thus, the price of the books will decrease. Hence, we need to change the cost for those books on our table. This operation requires the Update statement of SQL. If a particular book runs out of stock and we do not wish to restock that book, we have to delete the record for that book from our table. This operation uses the Delete statement of SQL.

Must Read: What is the difference between SQL and MySQL?

Must Read: Difference between SQL and NoSQL

Let’s do all the statements one by one.

Insert Statement

Suppose we have brought ten different books in the quantity of 4 each, for our book store. In that case, we need to fill in the details in our table using the INSERT statement. The syntax for insert statement is –

INSERT INTO < table_name >   < coloumn_names >    VALUES  < tuples_of_values > 
Copy code

Writing the column names is optional, however, there are a few things to remember while inserting values in the table; they are –

If you are writing the column names after the table name, the column names should be inside the bracket of (), and each column name should be comma separated.
The column names should not have spaces or other special characters except underscores. However, if you are giving space or special characters while naming then place the name inside backticks.
The order in which you will write the column names should be similar to the order in which you insert the values.
The values are inserted as per individual records or rows, that is, every row should be inside the bracket of ()
If you are not writing the column names after the table name, then you should insert the data in the table as per the order of the columns in your table, that you have done while creating the table.

Don’t get confused. We will go through examples –

So, we have ten different books in the quantity of 4 each. Let’s insert five books using the column names in the INSERT statement first –

INSERT INTO books (name, author, genre, in_stock, sold_out, cost, date) VALUES
Copy code

(‘And Then There Were None’, ‘Agatha Christie’, ‘Mystery’, 4, 0, 850, ’26/07/2022′),

(‘The Big Sleep’, ‘Raymond Chandler’, ‘Mystery’, 4, 0, 1000, ’26/07/2022′);

As you can see, in the above insert statements, we have given the column names inside the bracket and the values for the specific fields in the same order in which we have provided the names of the columns. The values are comma separated for each row. Thus, we have two rows here, and each row has seven comma-separated values for seven fields. These individual rows of data are known as tuples of data. So here we have two tuples of data, which are again comma separated.

Now, you need to write the insert statement again if you want to change the arrangement of column names. However, the order of data insertion should always be the same as the order in which you will place the columns. That is,

INSERT INTO books (author, genre, name, in_stock, sold_out, date, cost) VALUES
Copy code

(‘Truman Capote’, ‘mystery’, ‘In Cold Blood’, 4, 0, ’26/07/2022′, 800),

(‘J. Michael Strazynski’, ‘comic’, ‘Amazing Spider-Man’, 4, 0, ’26/07/2022′, 600),

(‘Mark Millar’, ‘comic’, ‘The Ultimates’, 4, 0, ’26/07/2022′, 530);

So, in the second insert statement, the columns are not in the same order as in the first. Hence, we have to write the insert statement again by matching the order of data in a tuple of records with the column names. In simple terms, the data entered in each tuple will get mapped to the column name corresponding to its position.

Must Read: Subqueries in SQL

Must Read: SQL CREATE TABLE

So after five entries, we get a table like this –

Now let’s insert the remaining 5 data without using the column names. In this case, we have to maintain the order of insertion for the data as per the order of columns in our table. That is –

INSERT INTO books VALUES
Copy code

(‘The Proposal’, ‘Jasmine Guillory’, ‘Romantic’, 4, 0, 480, ’26/07/2022′),

(‘Vision in White’, ‘Nora Roberts’, ‘Romantic’, 4, 0, 520, ’26/07/2022′),

(‘Carrie’, ‘Stephen King’, ‘Horror’, 4, 0, 420, ’26/07/2022′),

(‘Rosemary’s Baby’, ‘Ira Levin’, ‘Romantic’, 4, 0, 600, ’26/07/2022′),

(‘Bared To You’, ‘Sylvia Day’, ‘Romantic’, 4, 0, 450, ’26/07/2022′);

Here we have inserted five tuples of data, and the values in each tuple follow the order of the columns in the table. Finally, the table, after the insertion of ten records, will look like this –

Perhaps it is clear from the table that you have stocked your book store with 40 books in total (10 different books of 4 quantities each) on 26 July 2022.

Must Read: SQL WHERE

Must Read: Introduction to Normalization

Now how can you see the table with the data created by you? Let’s see further.

SELECT statement –

To look into the table with data, we have to write the SELECT statement. So the syntax is –

SELECT  FROM ;
Copy code

Column names are the names of the columns that you want to see from the table. What if you need to see all the columns? In that case, you have to write * (star), that is –

SELECT * FROM books;
Copy code

This statement will return all the columns from the table, and it will look like the figure above. Suppose you need the specific columns of name, in_stock, and cost. Then you have to write –

SELECT name, in_stock, cost FROM books;
Copy code

You will get a table like this –

Must Check: Primary Key in SQL

The table you are getting using the select statement is just a view. It is a view of the data you want to see from the stored table. You will get the order of columns in the view as per the order in which will you will give the column names after the SELECT statement that is, if I want to see the genre first, then the book name, and then the cost, what I will do is –

SELECT genre, name, cost FROM books;
Copy code

The view will be like this –

So the point to remember is that the SELECT statement is for selecting the particular or all columns from the table in the order we want.

We have our book store ready with the records for all the books, and we have opened our book store. Some of the books get sold, and we have to update our dataset accordingly. Let’s see how this is done.

Must Read: SQL LIMITS

Must Check: SQL Online Course and Certifications

UPDATE statement –

To update the changes in the table, we have to use the update statement. The syntax for the update statement is –

UPDATE  < table_name > 
SET  < column_name >  =  < updated_value > ,  < column_name >  =  < updated_value > ….
WHERE  < condition > ,  < condition > …
Copy code

In the UPDATE statement, we have to write UPDATE followed by the table name, a SET keyword, the column name, and the new value we want to insert in that column. This operation will select the column from that table and update the values in that column. If we don’t want to change all the values in a particular column, then we have to give conditions using the WHERE clause. Suppose two books named Carrie are sold. We have to update the in_stock for Carrie from 4 to 2 and sold_out from 0 to 2. If we do not provide the condition using where clause, then all the values in the in_stock and sold_out columns will get changed to 2. That is,


 UPDATE books SET in_stock = 2, sold_out = 2 WHERE name = ‘Carrie’;

Copy code

Now you will not see the result on running the above code, as it will make the changes to the table internally. To view the changing table, you have to do the –


 SELECT * FROM books;

Copy code

This will return you the table with the updated result, that is –

As you can see, the in_stock and sold_out for the book Carrie are changed to 2, while others are the same. So you can update one or more columns together using the UPDATE statement, just as in our example, we have updated two columns together.

Now let’s say that you have to update the date column to keep a record of the number of books you have on the latest day. Suppose you sold only two books of Carrie on 26 July and now it is 27 July. Then you need to change the date to 27 July for all the records. This is done by not using the where condition, that is –

UPDATE books SET date = ‘27/07/2022’
Copy code

This will give you the table of –

All the values in the field date changed to 27 July as no condition is used.

Let’s take another example, suppose a discount of 10% is applied across all the mystery books. Then we have to do –

UPDATE books SET cost = cost - cost*0.01 WHERE genre = 'Mystery';
Copy code

Here we have reduced the cost by 10% for genre Mystery, using the operators. Then you will get the table like this,

We can also place multiple conditions while updating a row. Suppose we have sold one book of The Proposal and two books of Bared to You under Romantic genre. In that case, we have to do –

UPDATE books SET in_stock = 3, sold_out = 1 WHERE genre = 'Romantic' and name = 'The Proposal';
UPDATE books SET in_stock = 2, sold_out = 2 WHERE genre = 'Romantic' and name = 'Bared To You';
Copy code

This will give the table of –

Hence, you can update any column in any way you want. Remember to provide the condition where ever you will need it else the date for that particular column will get changed, and the change is irreversible in this case.

Must Read: How to Create, Insert, and Delete SQL views?

Must Read: Types of Keys in Database

DELETE statement –

Let us assume that all the books under the Mystery genre get sold, and we do not wish to restock them. Then we have to use the delete statement where the syntax is:

DELETE FROM  < table_name > 
WHERE  < condition > ,  < condition > …
Copy code

To delete a record from the table, you have to place the column name and the value to delete in the WHERE condition. Thus we can delete all the data for the mystery genre by doing –

DELETE FROM books WHERE genre = ‘mystery’;
Copy code

Then you will get a table like this, where all the records for mystery will get deleted.

Now, suppose you want to delete the books from the table where the in_stock is less than 4, then –

DELETE FROM books WHERE in_stock<4;
Copy code

You will get a table like this –

Now if you do not give any WHERE conditions, then all the data from the table will get deleted, that is –

DELETE FROM books;
Copy code

You will get a table like this, with just the column names and no values –

Just like the Update statement, you can place multiple where conditions with DELETE. Try it out.

Must Read: Aggregate and Scalar Function in SQL

Summary

So, we have taken a look at all DML commands in SQL. We have seen how to insert data into an empty table, select the required data, update the data, and delete the records from the table. Along with WHERE conditions, you can use operators like +, -, *, /, just as we have used here. To apply more than one condition with WHERE, you can use logical operators of AND, OR, and NOT.

If you use AND, all the conditions have to be satisfied.
If you use OR, any one of the conditions needs to be true.
If you use NOT, the opposite of the conditions in the where clause has to be true.

So, in case of UPDATE and DELETE, you can use the WHERE conditions in any way you want. Try out all the possibilities. Hope that this article has been able to satiate your queries related to DML commands in SQL. Do check out more articles on SQL on our blog page.

Annexure

CREATE DATABASE book_store;
USE book_store;
CREATE TABLE books (
    name VARCHAR(50),  
    author VARCHAR(30),
    genre VARCHAR(30),
    in_stock INTEGER,
    sold_out INTEGER,
    cost FLOAT,
    date VARCHAR(15)
);
Copy code

Author: Nishi Paul

____________________

Recently completed any professional course/certification from the market? Tell us what you liked or disliked in the course for more curated content.

Click here to submit its review with Shiksha Online

About the Author

Shiksha Online

This is a collection of insightful articles from domain experts in the fields of Cloud Computing, DevOps, AWS, Data Science, Machine Learning, AI, and Natural Language Processing. The range of topics caters to upski... Read Full Bio