DML (Data Manipulation Language) IN SQL

DML is stands for Data Manipulation Language.

Using DML commands, we can easily access, store, modify, update and delete the existing records in the SQL database.

Follwoing are the four main DML commands in SQL:

1. SELECT command in SQL
2. INSERT command in SQL
3. UPDATE command in SQL
4. DELETE command in SQL

SELECT DML COMMAND IN SQL

SQL's SELECT command is the most important data manipulation command. It displays the records of the a specified table and also shows specific records by using WHERE clause.

Syntax of SELECT DML command

SELECT column_name1, column_name2,..., column_name3 FROM table_name;

Here, column_name1, column_name2 are the names of attributes whose data we want to retrieve from the table.

If we want to retrieve the data from the all attributes(column) from the table, we have to use the following SELECT command.

Syntax of SELECT COMMAND

SELECT * FROM name_of_table;

Example of SELECT command

Example1: To shows all the values of every column from the table.

SELECT * FROM student;

student_id	student_name	student_course	student_address
B123T01	John Doe	BCA	America
B123T02	Denver Musk	B.com	Russia

Example2: To shows the value of specific attributes from the table.

SELECT student_id, student_name from student;

This SELECT statement displays all the values of only student_id and student_name column(attribute) from the student table.

student_id	student_name
B123T01	John Doe
B123T02	Denver Musk

Example3: To describe how to use the where clause withe the SELECT DML command.

Let's assuming the following student table.

student_id	student_name	student_marks
101	John Doe	89
102	Aditya Kumar	75
103	Tushar Chauhan	60

If you want to access all the records of those we find the student all info from the table whose marks above the 70, then you have to write the following DML statement command in sql.

SELECT * FROM student where student_marks >= 70;

student_id	student_name	student_marks
101	John Doe	89
102	Aditya Kumar	75

INSERT DML Command

INSERT is the most DML (Data Manipulation Language) command in Structured Query Language, which allows to insert the data in the database tables.

Syntax of INSERT command.

INSERT INTO name_of_table (column_name1, column_name2, column_name3...) VALUES (value1, value2, value3....);

Example: To insert the data in the database using the INSERT DML SQL command.

student_id	student_name	student_marks
101	Aditya Kumar	88
102	Gaurav Kumar	60

Suppose, you want to data to the another student in the student table. For this you have to write the following the DML INSERT SQL command.

INSERT INTO student (student_id, student_name, student_marks) VALUES (103, "Tushar Chahuan", 77);

student_id	student_name	student_marks
101	Aditya Kumar	88
102	Gaurav Kumar	60
103	Tushar Chahuan	77

 UPDATE DML COMMAND

UPDATE is the most DML command in the SQL, which allows to update the data in the database table. UPDATE DML command allows user to update or modify the stroed (existing) data in the database tables.

Syntax of UPDATE COMMAND

UPDATE name_of_table SET (column_name=value1, column_name2=value2....) WHERE condition;

In this Statement the UPDATE, SET, WHERE are the SQL Keywords.

Example of the UPDATE command.

Example1: Example how to update the value of the a single field.

student_id	student_name	student_marks
101	Aditya Kumar	88
102	Gaurav Kumar	60
103	Tushar Chahuan	77

 Let's suppose , you want to update the marks of the student whose student_id is 101. To do this, for this you have to write the following the statement in SQL.

UPDATE student SET(student_marks = 99) where student_id = 101;

 

student_id	student_name	student_marks
101	Aditya Kumar	99
102	Gaurav Kumar	60
103	Tushar Chahuan	77

 Example2: To update the value of multiple fields of the database table.

student_id	student_name	student_marks
101	Aditya Kumar	88
102	Gaurav Kumar	60
103	Tushar Chahuan	77

Suppose, you want to update student_name and student_marks whose student_id = 102. The statement is following DML UPDATE command.

UPDATE student SET (student_name = "Gaurav Singh", student_marks = 72) where student_id = 102;

 

student_id	student_name	student_marks
101	Aditya Kumar	88
102	Gaurav SIngh	72
103	Tushar Chahuan	77

 DELETE DML COMMAND IN SQL

DELETE is a DML (Data Manipulation Language) command which allows to perform delete operation in SQL user to remove single and multiple existing records from the database tables. This command of DML DELETE command deleted the data from the table permanently. To use the DELETE DML command where clause with the select specific rows form the table.

Syntax of DELETE command

DELETE FROM name_of_table where condition;

To delete the single record from the table column.

student_id	student_name	student_marks
101	Aditya Kumar	88
102	Gaurav Kumar	60
103	Tushar Chahuan	77
104	Viaks Kumar	78

Let's suppose you want to delete the data of (select the table) from the table in the database.

DELETE FROM student where student_id = 102;

student_id	student_name	student_marks
101	Aditya Kumar	88
103	Tushar Chahuan	77
104	Viaks Kumar	78

 Let's suppose you want to delete the multiple data from the table in the database.

DELETE FROM student WHERE condition;

student_id	student_name	student_marks
101	Aditya Kumar	88
102	Gaurav Kumar	60
103	Tushar Chahuan	77
104	Viaks Kumar	78

 

DELETE FROM student where student_marks > 70;

student_id	student_name	student_marks
102	Gaurav Kumar	60

 

Please Comment...