SQL DML Commands: Mastering Data Manipulation in SQL

What is Data Manipulation Language (DML)?

Data Manipulation Language, also known as DML, is a set of SQL commands that are used to manipulate data within database tables or query views. Data analysts, scientists, engineers, and anyone using SQL rely on DML in order to access, transform, and analyze data. If you’ve ever worked with SQL, you’ve likely used DML commands. If not, don’t worry—in the next section, I’ll explain DML commands in more detail.

What are DML Commands in SQL?

DML commands all data professionals include are SELECT, INSERT, DELETE, and UPDATE. You can remember these commands by calling them your good friend UDIS (pronounced oodies).

These commands allow you to access and change the data that lives in a database.

Examples of DML commands in SQL

Now that we know about DML commands in SQL, let’s look at examples of these commands and how they can be used in your data projects. SELECT is the most important DML command in SQL. It allows you to access data in a database.

When you make a SELECT statement, you must include the SELECT statement at the beginning, the field that you want to access, and a FROM statement that specifies where you are pulling the data from.

For example, assume you have a table named friends containing the names and birthdays of all your friends. You want to select the name and birthday columns so that you can input your friends’ birthdays correctly into your calendar. You can do this by:

SELECT name, birthday 
FROM friends

This example allows you to select the names and birthdays of all of your friends. If you wish to organize them chronologically, you can use the ORDER BY command.

INSERT command in SQL

The INSERT command allows you to input data rows into an existing table. On the job, you will likely use an ELT tool to input data. However, small-scale projects or independent projects you may do for your portfolio may require you to use this command.

For example, let’s say you want to add a new friend to your friend’s table. You can do this by:

INSERT INTO friends (name, birthday) VALUES (‘Billy Joel’, ‘05-09-1949’)

When you use the INSERT command, you must specify which table and columns you are inserting the data into, as well as the values that will be input.

DELETE command in SQL

DELETE allows you to remove data from your table. Let’s say you want to remove a friend from your friend’s table. You can do this by:

DELETE FROM friends
WHERE name = 'Theresa Guidice' AND birthday = '05-18-1972';

When using the DELETE command, you must specify where you are removing the data from and what data is to be deleted. However, you will rarely delete data from tables or databases. Rather, you will filter your queries to only access the data you need for your purposes.

UPDATE command in SQL

You can modify data in an existing table using the UPDATE command. Let’s say you incorrectly input a friend’s birthday into your friend’s birthday list. You can fix this by:

UPDATE friends
SET birthday = '09-16-1992'
WHERE name = 'Nick Jonas' AND birthday = '08-15-1992';

DML Commands vs. Other Types of Commands

DML commands in SQL are specifically designed to manipulate data stored within a database. These commands insert, update, or delete data within tables.

Many other commands exist in SQL. Some command types you will use in your data journey include Data Definition Language (DDL) and Data Querying Language (DQL).

DML commands vs DDL commands

DDL commands are used to define, modify, and manage the structure of database objects. These commands are responsible for creating, altering, and dropping database objects such as tables, indexes, views, and schemas.

Unlike DML commands, which focus on manipulating and querying the data stored within the database, DDL commands focus on defining and managing the database's structure. Examples of DDL commands that you will use in your data projects include:

• CREATE: Create a table and its columns
• ALTER: Modify column names, add, or delete a column
• TRUNCATE: Remove data from a table without deleting the table
• RENAME: Remove data from a table without deleting the table
• DROP: Delete the data with its table

DML commands vs DQL commands

DQL commands, on the other hand, are used to query and analyze data in stored tables. Examples of DQL commands include SELECT, DISTINCT, and mathematical operators such as COUNT, SUM, AVG, MIN, and MAX. Here’s what these operators allow you to do:

• DISTINCT: Retrieves unique values from a specified column, removing duplicates.
• COUNT: Returns the number of rows that match a specified condition.
• SUM: Calculates the total sum of a numeric column.
• AVG: Computes the average value of a numeric column.
• MIN: Finds the minimum value in a column.
• MAX: Finds the maximum value in a column.

SQL Commands Cheat Sheet

There are many kinds of commands in SQL that you can learn. Over time, you will gain experience using these commands to access, manipulate, and analyze data for your purposes. If you want to learn more about these SQL commands, check out this SQL Basics Cheat Sheet.

SQL Basics Cheat Sheet

Build your SQL skills with DataCamp

In this article, you learned about DML commands in SQL. DML commands are used to manipulate and query data stored in a database. Examples of DML commands in SQL include SELECT, INSERT, DELETE, and UPDATE.

If you want to take your SQL skills to the next level, start learning SQL on DataCamp, or better yet, become SQL Certified!