DML in DBMS

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 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: