Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
What is DML?
3.
Types of DML Statements
3.1.
SELECT
3.2.
INSERT INTO
3.3.
UPDATE
3.4.
DELETE
4.
Syntax of DML Statements
5.
Examples of DML Commands
5.1.
SELECT
5.2.
INSERT INTO
5.3.
UPDATE
5.4.
DELETE
6.
Frequently Asked Questions
6.1.
What are the 4 commands of DML?
6.2.
What is DML and DDL?
6.3.
What is SQL and its types?
7.
Conclusion
Last Updated: Mar 27, 2024
Easy

Data Manipulation Language

Author Shiva
0 upvote
gp-icon
Dbms - Database management systems
Free guided path
12 chapters
93+ problems
gp-badge
Earn badges and level up

Introduction

If you've ever worked with databases or are learning SQL, you might have heard the term "Data Manipulation Language" or DML before. But what is DML, and how does it work?

data manipulation language

In this article, we'll explore Data Manipulation Language and what it can do for you.

What is DML?

DML (data manipulation language) is part of SQL, the language used to interact with relational databases. It is used to add, update, and remove records from a database and otherwise change the data there. DML enables you to interact with and modify the data in your database, in other words.

Modern computers cannot function without databases. They make it possible for us to store vast volumes of data effectively. Data storage alone, however, is insufficient. Additionally, we need to be able to alter that data in various ways, including by adding new entries, changing current records, and removing irrelevant records. DML steps in to help with that.

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

Types of DML Statements

DML statements have four main types: SELECT, INSERT, UPDATE, and DELETE.

SELECT

The SELECT statement is a fundamental DML statement used to retrieve specific columns or all columns from a table based on specified conditions, allowing you to retrieve and display data stored in a database.

To retrieve the first names and last names of all customers whose last name is "Smith," you would use the following SQL query:

SELECT FirstName, LastName
 FROM Customers
 WHERE LastName = 'Smith';

INSERT INTO

As the name suggests, the INSERT statement adds a new entry to the table. Suppose you have a customer table and there is a new customer to add, you would use the INSERT statement to add their information to the table.  

Here is an example of the same:

INSERT INTO customer (name, email, address, phone) VALUES ('John Doe', 'johndoe@email.com', 'Prime City','2874 001');

 

This statement adds a new customer named John Doe to the customer table, along with his email,  address and phone number.

UPDATE

A table's existing records can be changed using the UPDATE command. You might use the UPDATE statement to update a client's phone number in the customer database, for instance, if they change their phone number. 

A statement that serves as an UPDATE is seen here:

UPDATE customer SET phone=11112222' WHERE name='John Doe';

 

This statement updates the phone number for the customer named John Doe in the customer table.

DELETE

The DELETE statement is used to remove records from a table. For example, if a customer cancels their account, you would use the DELETE statement to remove their information from the customer table. 

Here is an example of a DELETE statement:

DELETE FROM customer WHERE name='John Doe';

 

This statement removes the customer named John Smith from the customer table.

Syntax of DML Statements

The syntax for DML statements is relatively straightforward. 

Here are a few examples:

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


UPDATE table_name SET column1=value1, column2=value2,... 
WHERE some_column=some_value;


DELETE FROM table_name WHERE some_column=some_value;

 

Let's break down the syntax of these statements.

  • INSERT INTO: This keyword is used to specify the table you want to add data to.
     
  • table_name: This is the name of the table you want to add data to.
     
  • column1, column2, column3: These are the names of the columns in the table you want to add data to.
     
  • VALUES: This keyword is used to specify the values you want to add to the table.
     
  • value1, value2, value3: These are the values you want to add to the table.
     
  • UPDATE: This keyword is used to specify the table you want to update.
     
  • SET: This keyword is used to specify the column you want to update and the new value you want to set it to.
     
  • WHERE: This keyword is used to specify which rows you want to update.
     
  • DELETE FROM: This keyword is used to specify the table you want to delete data from.

Examples of DML Commands

We have a table called demo with column ID, Name and About. Let’s apply different DML commands. 

SELECT

Selecting an entry from the table where ‘Name’ is “John Doe”.

Eg.

SELECT from demo where Name="John Doe";
SELECT

INSERT INTO

Inserting a new entry in ‘Name’ and ‘About’ Column. ID starts from 1 by default, you don’t need to specify it. It will update on its own.

Eg. 

INSERT into demo(Name, about)
VALUES('John Doe', 'Placeholder name of an unknwon person')
insert into

UPDATE

Pick the target row using the primary column and update the value using SET. 

Eg.

UPDATE demo
SET Name='Jane Doe'
WHERE ID=1;
update

DELETE

For deletion, let’s add one more entry, and then we’ll delete the first one.

INSERT into demo(Name, about)
VALUES('John Doe', 'Placeholder name of an unknwon person')
delete

After removing the entry with id 1:

DELETE FROM demo
WHERE ID=1;
delete example

Frequently Asked Questions

What are the 4 commands of DML?

The four basic commands of DML include Insert, Update, Delete, and Select. The DML commands are used to manipulate the data and allow you to make changes in your data table, enabling efficiency and providing easy access to make changes. 

What is DML and DDL?

DML is a data manipulation language that is used to manipulate the data. It allows inserting, retrieving, updating, and deleting data using SQL statements. DDL is data definition language that is used to create and define the database. Commands in DDL include create, alter, drop, and truncate. 

What is SQL and its types?

SQL is a structured query language that is used to interact with the database. The language is specially designed to operate on a relational database. The relational database stores the data in the format of rows and columns. To work on the relational database, SQL has various commands and types such as DDL, DML, DCL, DQL, and TCL. 

Conclusion

This article covered the data manipulation language in SQL, including various types and their syntax and examples. We also saw why DML is important in SQL.  

After reading this blog, you will understand the data manipulation language in SQL.

Recommended Readings:


Check out some amazing Guided Paths on topics such as Data Structure and AlgorithmsCompetitive Programming, etc., along with some Contests and Interview Experiences only on Coding Ninjas Studio.

Do check out The Interview Guide for Product Based Companies as well as some of the Popular Interview Problems from Top companies like AmazonAdobeGoogle, etc. on Coding Ninjas Studio.

Cheers!

Previous article
SQL vs MYSQL
Next article
Difference Between Union and Join
Guided path
Free
gridgp-icon
Dbms - Database management systems
12 chapters
93+ Problems
gp-badge
Earn badges and level up
Live masterclass