DML in DBMS

DML(Data Manipulation Language) in DBMS is one of the types of SQL queries which helps you in performing all types of data modification in a database like inserting, modifying, and deleting its data.

DDL (Data Definition Language) is another type of SQL query that helps in creating or modifying the structure or the schema of the table. DDL consists of Commands like CREATE, ALTER, TRUNCATE, and DROP.

In this article, we will focus on learning DML in DBMS and its commands like INSERT, UPDATE, DELETE, and SELECT, along with their syntaxes and examples.

DML Commands in DBMS

Let's see various DML commands in dbms, along with their examples:

SELECT:

SELECT is the most widely used data manipulation command in Structured Query Language for retrieving data from the table.

It is used to fetch the data in a set of records from a table, view, or a group of tables, or views by making the use of SQL joins.

The returned data from the SELECT command is stored in a new table called the result set.

Syntax of SELECT command:

The SELECT query is used when we want to retrieve the data from all the columns of the table.

Here, columnName1, and columnName2 are the names of those columns whose data we want to show.

This syntax for the SELECT command is used when we want to retrieve the data from some specific columns of the table.

Examples:

Let's say we have a table student_details, having ROLL_NO, FIRST_NAME, LAST_NAME, and MARKS as the fields, and some data has been stored in these fields.

Now, if we want to access all the data stored in the table, we will make use of the SELECT command.

OUTPUT:

Now, if we want to only show the ROLL_NO and the marks of a student. Then we can write:

OUTPUT:

Here, we have specifically mentioned the column names separated by a comma, for which we want to access the data.

INSERT:

INSERT is another important data manipulation command in Structured Query Language used to insert data into the rows of a table.

Syntax of INSERT command:

Another way is to only specify the value of data to be inserted without the column names.

Here, col_1, col_2 .. col_n represents the name of the columns, and value_1, value_2, ... value_n represents the value to be inserted in the corresponding columns.

Examples:

Taking our previous example of the student_details table, let's say we want to add the 4th entry of a student. We can write a query:

OUTPUT:

As we can see from the output, the 4th row for the newly added student has been created.

Let's say we want to add the 5th record in our table, using the second syntax in which we only specify the value of data to be inserted without the column names.

We can write a query;

OUTPUT:

As we can see from the output, the 5th row for the newly added student has been created.

UPDATE:

UPDATE command is another important data manipulation command in Structured Query Language, it allows us to update or modify the existing data in tables. It changes the data from one or more records in a table.

Syntax of UPDATE command:

Here, the specified condition helps us to select the rows for which the value of columns needs to be updated.

Examples:

Again, coming to our previous example of the student_details table, let's say, we want to change the marks of a student whose roll_no is given to us.

We can write the query to update the marks, by specifying the roll_no for which we are updating the marks.

Now, the updated table looks like this.

OUTPUT:

We can also update the value of multiple fields in a table by setting the values separated by commas.

Now, the updated table looks like this.

OUTPUT:

DELETE:

DELETE is another useful data manipulation command in Structured Query Language, which allows us to remove single or multiple records from the database tables depending upon the condition we specify in the WHERE clause.

If no condition is specified in the WHERE clause, then it will delete all the rows of a table.

Delete command keeps the record for each deleted row and so it does not remove the stored data permanently from the database and we can always roll back to the previous stage using the ROLLBACK command as the log for that last deleted entry has already been stored in the DELETE command logs.

Syntax of DELETE command:

As we can see here no condition has been specified so it will delete all the rows from the table.

OR

Here, the condition is specified using the WHERE clause and only those records that fulfill the condition are deleted.

Examples:

Coming to our example of the student_details table, let's say we want to delete all the records of the students whose marks are less than 80. Here, only one record for ROLL_NO 1 will be deleted

Original table:

OUTPUT:

Now, let's say we want to delete all the records from the table, so we can simply write.

Its output will be an empty table with no data.

• As we discussed above the DELETE command in SQL maintains transaction logs for each deleted record and so it allows us to restore the deleted data by using the ROLLBACK command.

Let's discuss the example for the same.

We will first delete all the rows in the student_details table.

Let's try to display the data from the table.

OUTPUT: It's an empty table.

Now, let's use the Rollback command to restore the data.

Now, let's try to retrieve the data from the table again.

OUTPUT:

Read More:

• Views in DBMS.
• Record in DBMS.