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:
- SELECT Command.
- INSERT Command.
- UPDATE Command.
- 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 |
