DML Commands in SQL with Examples

According to JetBrains’ Stack Overflow Developer Survey 2021, SQL consistently ranks among the most popular programming languages. It is the second most commonly used programming language worldwide after JavaScript. If your interest lies in building a career in business and management or becoming an IT professional dealing with databases, learning SQL commands is crucial. In this blog, we will explore various SQL commands, in particular, the DML commands in SQL.

Introduction to SQL 

SQL, or Structured Query Language, is a standard language used to construct, access, and manipulate databases. It is an essential part of database management systems (DBMS). To modify these databases, SQL requires a variety of SQL commands, like DQL, DDL, DCL, TCL, and DML.

SQL is one of the core languages required to become a data analyst. If you wish to build a career in this field, consider taking a data analyst course with placement. With this course, you get government-approved certifications and complete placement assistance to become job-ready. 

What are DML Commands in SQL?

DML stands for Data Manipulation Language. The purpose of DML is to select, insert, update, and delete data in a database. The DML commands in SQL modify or change the data present in the database. 

These commands are not auto-committed. This means they can’t permanently save all the changes in the database. However, they can roll back using the DML commands themselves.

Following are the four main commands in DML:

• SELECT
• INSERT
• UPDATE
• DELETE

Types of DML Commands in SQL with Syntax and Examples

The DML commands in SQL are explained in detail below:

1. INSERT Command 

It is a command to insert data into the table’s columns. The user must check the table to see whether it has any integrity constraints, such as PRIMARY KEY, UNIQUE, NOT NULL, etc., and enter records accordingly. 

Things to keep in mind while inserting data into the table using the INSERT command: 

1. Enclose the string within a single quote, ‘ ‘. 
2. Write the date as YYYY/MM/DD and enclose it within a single quote. 
3. Mention the data type along with the column name. 
4. Follow all INTEGRITY constraints. They are the rules for data being stored in the table.

Syntax- 

INSERT INTO table_name (column_1 data type, column_2 data type,...,column_N data type)

VALUES (value 1, value 2, value 3,..., value N);

Example 1 – Consider an empty table called “Employee” that will contain details of all employees. The structure of the Employee table is as follows:

| Emp_ID | Emp_Name | Emp_Age | Emp_City | Salary |

Let us add a record of an employee to this table using the DML command INSERT.

INSERT INTO Employee (Emp_ID int, ‘Emp_Name’ varchar(20), Emp_Age int, ‘Emp_City’ varchar(20), Salary int)

VALUES (1, ‘Satish’, 25, ‘Pune’, 20000);

INSERT INTO Employee (Emp_ID int, ‘Emp_Name’ varchar(20), Emp_Age int, ‘Emp_City’ varchar(20), Salary int)

VALUES (2, ‘Ram’, 26, ‘Pune’, 21000);

Similarly, you can add any number of records.

To display the record, use the code – 

SELECT * FROM Employee;

Output –

	Emp_ID	Emp_Name	Emp_Age	Emp_City	Salary
1	1	Satish	25	Pune	20000
2	2	Ram	26	Pune	21000

You can also simply insert values into the table.

Example 2-  You can also insert values into the table using another INSERT command format. 

No columns need to be specified if you want to insert a record that includes data from every column in the table. The values can be directly inserted with the INSERT command, which by default takes into account all columns.

INSERT INTO Employee

VALUES (3, ‘Chirag’, 27, ‘Jaipur’, 25000);

To display the result, use the code –

SELECT * FROM Employee;

Output – 

	Emp_ID	Emp_Name	Emp_Age	Emp_City	Salary
1	1	Satish	25	Pune	20000
2	2	Ram	26	Pune	21000
3	3	Chirag	27	Jaipur	25000

Example 3 – While inserting the record into the table, you may not have data for all the columns. In this case, a column that does not contain information will insert a NULL value.

INSERT INTO Employee (Emp_ID int, ‘Emp_Name’ varchar(20), Emp_Age int, Salary int)

VALUES (4, ‘Pankaj’, 24, 15000);

Use the code below to display the result –

SELECT * FROM Employee;

Output –

	Emp_ID	Emp_Name	Emp_Age	Emp_City	Salary
1	1	Satish	25	Pune	20000
2	2	Ram	26	Pune	21000
3	3	Chirag	27	Jaipur	25000
4	4	Pankaj	24	NULL	15000

Here, for the column in which no data value was provided in the INSERT command, it inserts a NULL value by default.

2. SELECT Command

It is used to select and retrieve data from the database table. Note that it does not manipulate data. The SELECT command is also known as data query language because it is used to query information from a database table. 

In the SELECT statement, the table name and column names are specified. It returns data in the form of a result table. These result tables are called “result sets”. 

Various ‘clauses’ that go with the SELECT command are WHERE, GROUP BY, HAVING, ORDER BY, AS, and DISTINCT. These clauses help filter the results stated in the SELECT clauses.

Let’s discuss each of these clauses one by one.

• WHERE Clause: It specifies which row to retrieve from the database.
• GROUP BY Clause: It is used to arrange data into groups so that aggregate functions can be applied.
• HAVING Clause: It selects among groups defined by the Group By clause by specifying conditions.
• ORDER BY Clause: It specifies the order in which to return the rows.
• AS Clause: It is used to give a column or table with a temporary name.
• DISTINCT Clause: Its use is to remove duplicates from the result set of a SELECT statement (SELECT DISTINCT).

Syntax – 

SELECT column_name1, column_name2,...FROM table_name;

Example 1- To see all the records in the table Employee, there is no need to specify every column name in the statement. You can simply use * asterisk with the SELECT command. This * asterisk mark in SQL recognizes all the columns and a complete table will appear.

SELECT * FROM Employee; 

Example 2 – To view only the employees’ IDs and names, you select only Emp_ID and Emp_Name columns in the SELECT command.

SELECT Emp_ID, Emp_Name FROM Employee;

Output – 

	Emp_ID	Emp_Name
1	1	Satish
2	2	Ram
3	3	Chirag
4	4	Pankaj

Example 3 – To retrieve specific records from the table Employee, for instance, you want to see the information of employees who work in Pune.

SELECT * FROM Employee WHERE Emp_City IN (‘Pune’);

Output –

	Emp_ID	Emp_Name	Emp_Age	Emp_City	Salary
1	1	Satish	25	Pune	20000
2	2	Ram	26	Pune	21000

To learn more about SQL and perfect your skills as a SQL developer or business management professional, consider taking a SQL course.

3. UPDATE Command

This DML command in SQL modifies the record present in the existing table. It updates the records by using a WHERE clause that specifies the condition with an UPDATE statement. You need to mention the condition, otherwise, all the rows will be affected. 

Syntax – 

UPDATE table_name

SET column 1 = value 1, column 2 = value 2,..., column N = value N

WHERE CONDITION;

Example – 

UPDATE Employee

SET Address = ‘Chennai’

WHERE Emp_ID = 2;

Output –

	Emp_ID	Emp_Name	Emp_Age	Emp_City	Salary
1	1	Satish	25	Pune	20000
2	2	Ram	26	Chennai	21000
3	3	Chirag	27	Jaipur	25000
4	4	Pankaj	24	NULL	15000

It will update the employee’s address from Pune to Chennai, where the employee ID is 2.

4. DELETE Command

It is used to delete some or all of the records from the existing table. It should contain the WHERE clause to give the condition. It is necessary to mention the WHERE condition to delete the selected rows; otherwise, it will delete all the data.

Syntax –

DELETE FROM table_name

WHERE condition;

Example – 

DELETE FROM Employee

WHERE Address = ‘Pune’;

	Emp_ID	Emp_Name	Emp_Age	Emp_City	Salary
1	2	Ram	26	Chennai	21000
2	3	Chirag	27	Jaipur	25000
3	4	Pankaj	24	NULL	15000

It will delete the employee’s record who resides in Pune.

The use of these DML statements in SQL makes it easy to retrieve and manipulate data in a table according to the query.

Features of DML Commands

DML is the lifeline when it comes to carrying out operations in a structured database. Below are some of the features of DML operations in SQL:

Below are some of the features of DML operations in SQL: 

• By employing the WHERE clause to add conditions, DML commands provide us the freedom to get the data as needed. 
• Using DML commands, it is simple and convenient to modify any data that has been stored in a database. 
• DML makes it possible for users of the system to quickly and effectively interact with the stored database.

Conclusion

With the DML commands in SQL, you can perform simple data retrieval and complex data manipulation operations. With these commands, you can modify the database and ensure it remains accurate and up-to-date based on changing business needs. You can contribute to the overall success of data-based projects by learning about these SQL commands.

Which of the DML commands do you think are the most useful while dealing with large data sets? Share your answers in the comments section below. If you wish to pursue a career as a developer, programmer, data scientist, or related role, check out the top SQL interview questions to ace your technical assessment.

FAQs