SQL DML commands

Read this article to understand the SQL DML commands (Data Manipulation Language) along with the examples. We have shared few examples and we have shared basic as well as advance level common interview questions also.

In this post, I will provide you a short overview of SQL DML commands with all its options. In addition, we have covered some questions which we receive from the audience. 

First, let’s talk about the theory of DML commands and why/when/how we should use it.

I hope you have enjoyed the DDL commands and now you are ready for data manipulation. As you know, data manipulation means inserting the data, updating the data and deleting it from the table. Lets learn the DML commands.

Definition

As you know we have already created a database learnoffline_db and created a table customers with 5 attributes. This is table which doesn’t have any data. data is also called as records in SQL. For selecting the records from the table, inserting the data into table, updating some records and remove the inserted records such operations we can perform on the data. All these Select, Insert, Update and Delete command comes under DML operation.

Reasons to Use SQL DML commands

As the name of each command suggest us that we can select, insert, update and delete the records from table. All these operations are called as Manipulation. Below are the some key areas where we need to use DML commands.

1. Show the top rated review of the product.
2. Add data to the database for storage or further processing. Add new review in to the system.
3. Allowing you to change data as needed to reflect changes in the business logic or requirements.
4. Remove the unnecessary data from the database.
5. Handle the recent updation on the data.

Select

Select command is used to retrieve the records from the table. We receive the requested data into result set. We can retrieve all the columns or only required columns.

Syntax

SELECT [DISTINCT] column1, column2, ..... FROM table_name;

SELECT column1, column2, ..... FROM table_name 
[WHERE CONDITION];

SELECT * FROM table_name 
[WHERE CONDITION];

Example

Example 1:

Select first name, last name and country column from the table.

customer_id	first_name	last_name	age	country
1	John	Doe	31	USA
2	Robert	Luna	22	USA
3	David	Robinson	32	UK
4	John	Sinha	22	UK
5	James	Doe	19	INDIA

SELECT first_name, last_name, country FROM Customers;

first_name	last_name	country
John	Doe	USA
Robert	Luna	USA
David	Robinson	UK
John	Sinha	UK
James	Doe	INDIA

Example 2:

We can select all the columns using * operator. Lets use it to display whole data of the table.

Example 3:

Can we select few required rows only. Yes! Just use the select query with where operator.

Select command is used to retrieve the data from the table. We can retrieve selected columns or we can retrieve all columns using asterisk (*) sign. Select command with distinct is used to show the unique records based on selected columns. It will check all unique combinations of selected columns only. Select command store the result in result set. Select command is used to retrieve only required records using constraints. Where command can be use in select query to apply the conditions. Select command can be used to aggregate functions. We can use select with order by, group by, having, where, join and limit clause.

Insert

Insert command will help us to insert the records into database. Insert command will insert the data into same sequence in which we have the columns. If we didn’t pass the columns into insert command and just pass the data, it will consider the default column sequence. We can change the column sequence for inserting the database, in that case we need to provide column sequence and then data. We can insert the data for specific columns which we have to pass into the insert command. What will be the general statement for inserting the data into table. I guess….

Insert into table and values will be value1, value2, … etc.

Correct!!

Syntax

INSERT INTO table_name(column1, column2, ...)
VALUES (value1, value2, ...)

INSERT INTO table_name
VALUES (value1, value2, ...)

Example

Example 4:

insert into customers(customer_id, first_name, last_name, age, country)
values (1,'John','Doe',31,'USA')

insert into customers
values (5,'James','Doe',19,'INDIA')

insert into customers(customer_id, first_name, last_name, country)
values (2,'Robert','Luna','USA')

I have added 3 records into the customers table. Lets add few more dummy records into the table for manipulation. See the below tables with all inserted records.

customer_id	first_name	last_name	age	country
1	John	Doe	31	USA
2	Robert	Luna	NULL	USA
3	David	Robinson	32	UK
4	John	Sinha	22	UK
5	James	Doe	19	INDIA

Great!! We have stored 5 records into the table. I have to update 2nd records which has NULL age. By using insert command we can add new records but how I will update same records which has NULL age?

Yeah! You are right. Lets learn about the update command.

Update

Update command is used to update the records based on certain condition. We can update all the records without adding the condition. So it means when we write the update command and didn’t add WHERE condition then it will update all the records. What will be the general statement for updating the data into table?

Let me guess…(Update record whose customer id is 2…….) but how to update the value?

No! I can’t. Lets learn about the update syntax.

Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
[WHERE CONDITION];

Where condition is optional but always provide the criteria while updating the records.

Example

Example 5:

Great!! We successfully updated all the records using different ways. Can we remove the records which are not belongs to specific country? Yes? How?

Delete

We can use the delete command to delete the records and we can apply the condition to identify the particular records. If we didn’t specify the condition then it will delete all the records from the table. Yeah! The sentence is correct. It will delete all the records from table if you didn’t mention the criteria.

Syntax

DELETE FROM table_name 
[WHERE CONDITION];

Example

Example 8:

Delete the records whose customer_id is 5.

We can delete all the records without specifying the condition

DELETE FROM customers;

Above statement will delete all the records from the table.

Linking

DML commands are used to manipulate the data. Select, Insert, Update and Delete are four DML commands. We can use Select command to select the records based conditions. We can use Insert command to insert the records into table. Update command is used to modify the records while Delete command is used to delete the records from the table. You can refer all the interview questions. Please refer all the links for understanding the DML commands in depth.

DML commands with Latest SQL Interview Questions 2023

Basic DML command SQL Interview questions

1. What is the purpose of the Select clause in SQL?
2. What is the purpose of the Insert clause in SQL?
3. What is the purpose of the Update clause in SQL?
4. What is the purpose of the Delete clause in SQL?

Intermediate DML command SQL Interview questions

1. How can you optimise query performance using the Select clause?
2. How to select uniques records from the table?

Advanced DML command SQL Interview questions

1. How to delete the duplicate records from the table?
2. Update the active flag of all records which are duplicate from true to false.
3. Execution sequence of Select query.

Questions covered from comment section

In this section we will cover the new questions as well as answers. Please comment the questions into comment section so we will include it here. Below are few questions which we thought should be covered.

Que : Update the active flag of all records which are duplicate from true to false.

Ans : While updating or deleting the records from the table we need to double-check conditions for accuracy. Lets first select the records which are duplicate and then select oldest records for marking false.

update employee
set flag = false 
where id in (select min(id) from employee group by employee_id having count(*) > 1))

Quiz

Click on this link to solve few MCQ questions on DML commands.

I hope you have enjoyed this session. Happy offline learning… Lets meet in next session.