DML Commands in SQL

DML is an abbreviation of Data Manipulation Language.

Data Manipulation Language commands in Structured Query Language manipulate the data in the database. DML commands are used to retrieve records, add records, update the records, as well as remove the data from the existing table.

The SQL commands fall under the DML commands is as follows:

1. SELECT Command.
2. INSERT Command.
3. UPDATE Command.
4. DELETE Command.

1. INSERT DML Command

INSERT is a DML command in the SQL. The INSERT command is used to add a new record to the table.

The syntax of the INSERT command is as follows:

INSERT INTO TABLE_NAME VALUES (Data_1, Data_2, Data_3, Data_4);

The above syntax adds records in all the table fields without mentioning the field name, but the column sequence must be correct.

INSERT INTO TABLE_NAME (COLUMN_NAME1, COLUMN_NAME2) VALUES (Data_1, Data_2);

The above Syntax is used to add records for the selected columns of the table.

Examples of INSERT Command

Example 1: The below example adds a record to the table

Consider the newly created table name stud, which has no data.

We will execute the INSERT command to add data to the stud table

INSERT INTO STUD VALUES (1, ‘Shivani Agrawal’, 15, ‘Mumbai’, 85, 9);

The above query adds a single record to the table. We can insert multiple values into the tables, taking multiple value insertion.

Example 2: These examples describe how to INSERT multiple values into the table.

Now we will store multiple new records on the stud table. To store multiple records in a table, we will execute the following Data Manipulation Language INSERT command:

INSERT INTO STUD VALUES (2, 'Pranoti Shende', 16, 'Kohlapur', 95, 9), (3, 'Vaibhav Sharma', 15, 'Noida', 90, 16);

2. SELECT DML Command

The Most important DML command in the SQL is the SELECT Command. We execute the SELECT query to display data from the table.

The Syntax of the SELECT command is as follows:

SELECT * FROM TABLE_NAME;

The above syntax retrieves all the records from the mentioned table name

SELECT COLUMN_NAME_1, COLUMN_NAME_2, COLUMN_NAME_N FROM TABLE_NAME;

The above syntax is used to retrieve records from the mentioned column name

SELECT * FROM TABLE-NAME WHERE COLUMN_NAME;

The above syntax is used to display the specific records from the table

Examples of SELECT Command

Example 1:  This example fetches all the data of each column from the table:

SELECT * FROM STUD;

The output of the above query is as follows:

STUD_ID	STUD_NAME	STUD_AGE	STUD_CITY	STUD_MARKS
1	Shivani Agrawal	15	Mumbai	85
2	Pranoti Shende	16	Kolhapur	95
3	Vaibhav Sharma	15	Noida	90

Example 2:  This example fetches all the data of the selected column from the table:

SELECT STUD_ID, STUD_NAME, STUD_AGE FROM STUD;

The above query retrieves records from the selected columns

The output of the above query is as follows:

STUD_ID	STUD_NAME	STUD_AGE
1	Shivani Agrawal	15
2	Pranoti Shende	16
3	Vaibhav Sharma	15

Example 3:  These examples fetch a single row of all data using the WHERE clause specified by column name from the table:

SELECT * FROM STUD WHERE STUD_ID = 1;

The above query retrieves records of student information whose student id is 1.

The output of the above query is as follows:

STUD_ID	STUD_NAME	STUD_AGE	STUD_CITY	STUD_MARKS
1	Shivani Agrawal	15	Mumbai	85

3. UPDATE Data Manipulation Language Command

UPDATE Command is a DML Command in the SQL. The UPDATE commands are     used to update the existing records in the table.

The syntax of the UPDATE command is as follows:

UPDATE TABLE_NAME SET COLUMN_NAME1 = VALUES, COLUMN_NAME2 = VALUES WHERE CONDITION;

The SET keyword is used to update the column in the above syntax

Examples of UPDATE Command

Example 1: The below example helps you with how the UPDATE query works:

Consider the already existing table with the following data

Table Name: Stud

STUD_ID	STUD_NAME	STUD_AGE	STUD_CITY	STUD_MARKS
1	Shivani Agrawal	15	Mumbai	85
2	Pranoti Shende	16	Kolhapur	95
3	Vaibhav Sharma	15	Noida	90

I want to modify the student city whose student id is 3. To update records, we will execute the following Data Manipulation Language UPDATE command:

UPDATE STUD SET STUD_CITY = ‘PUNE’ WHERE STUD_ID = 3;

We will execute the SELECT query to verify whether the student information is successfully updated or not

UPDATE STUD SET STUD_CITY = ‘PUNE’ WHERE STUD_ID = 3;

The output of the above query is as follows:

STUD_ID	STUD_NAME	STUD_AGE	STUD_CITY	STUD_MARKS
3	Vaibhav Sharma	15	Pune	90

Example 2: The below example describes how to modify the multiple values.

We need to modify the student marks and student age whose student city is ‘Pune’. To update records, we will execute the following Data Manipulation Language UPDATE command:

UPDATE STUD SET STUD_MARKS = 93, STUD_AGE = 16 WHERE STUD_CITY = 'PUNE';

We will execute the SELECT query to verify whether the student information is successfully updated or not

SELECT * FROM STUD WHERE STUD_CITY = ‘PUNE’; 

The output of the above query is as follows:

STUD_ID	STUD_NAME	STUD_AGE	STUD_CITY	STUD_MARKS
3	Vaibhav Sharma	16	Pune	93

4. DELETE DML Command

The DELETE command is a DML command that removes the data from the existing table. The WHERE clause conditions are used in the queries to remove data from the table.

The syntax of the DELETE command is as follows:

DELETE FROM TABLE_NAME WHERE CONDITION;

Examples of DELETE Command

Example 1: The below example describes how to remove the records from the existing table.

Consider the already existing table with the following data

Table Name: Stud

STUD_ID	STUD_NAME	STUD_AGE	STUD_CITY	STUD_MARKS
1	Shivani Agrawal	15	Mumbai	85
2	Pranoti Shende	16	Kolhapur	95
3	Vaibhav Sharma	16	Pune	93

For example, we want to delete the records from the stud table whose city name is 'Mumbai'. To remove records, we will execute the following Data Manipulation Language DELETE command:

DELETE FROM STUD WHERE STUD_CITY = ‘MUMBAI’;

We will execute the SELECT query to verify whether the student information is successfully deleted or not

SELECT * FROM STUD;

The output of the above query is as follows:

STUD_ID	STUD_NAME	STUD_AGE	STUD_CITY	STUD_MARKS
2	Pranoti Shende	16	Kolhapur	95
3	Vaibhav Sharma	16	Pune	93