Data Manipulation Language

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";

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')

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;

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')

After removing the entry with id 1:

DELETE FROM demo
WHERE ID=1;

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

Data Manipulation Language (DML) is the main component of SQL and database management, enabling users to interact and modify data within a database. Its commands, such as SELECT, INSERT, UPDATE, and DELETE, provide powerful tools for querying and manipulating data effectively. Mastery of DML is essential for anyone working with databases, as it allows for efficient data retrieval, accurate updates, and seamless integration of new information.

Recommended Readings:

• Database Languages
   
• Data Manipulation Instruction
   
• SQL Introduction
   
• Difference between sql and nosql

Check out some amazing Guided Paths on topics such as Data Structure and Algorithms, Competitive 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 Amazon, Adobe, Google, etc.

Cheers!