Skip to main content

SQL DML Commands: Mastering Data Manipulation in SQL

Learn more about Data Manipulation Language (DML), how it compares to DQL and DDL, and master DML commands including SELECT, INSERT, DELETE, and UPDATE.
May 17, 2024  · 6 min read

In this tutorial, I will provide a clear overview of Data Manipulation Language (DML) commands in SQL, including definitions, examples, and use cases. I’ll also highlight the differences between DML commands and other SQL command types. Whether you're an experienced database administrator or new to databases, this guide will help you understand and use DML to optimize your database operations. Let’s get started!

Associate Data Engineer in SQL

Gain practical knowledge in ETL, SQL, and data warehousing for data engineering.
Explore Track

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.

Command

Description

SELECT

SELECT is an access command. It allows you to access data within a database.

INSERT

INSERT is a change command. It allows you to input rows of data within an existing table.

DELETE

DELETE is a change command. It allows you to remove rows of data within an existing table.

UPDATE

UPDATE is a change command. It allows you to change data within the rows of a table.

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!

Topics

Continue Your SQL Learning Journey Today!

course

Intermediate SQL

4 hr
302.5K
Accompanied at every step with hands-on practice queries, this course teaches you everything you need to know to analyze data using your own SQL code today!
See DetailsRight Arrow
Start Course
See MoreRight Arrow
Related

tutorial

SQL DDL Commands: The Definitive Guide

Learn about SQL DDL commands and how they help define and manage the structure of database objects.
Deandra Cutajar's photo

Deandra Cutajar

11 min

tutorial

SQL: Reporting and Analysis

Master SQL for Data Reporting & daily data analysis by learning how to select, filter & sort data, customize output, & how you can report aggregated data from a database!
Hafsa Jabeen's photo

Hafsa Jabeen

37 min

tutorial

SQL Commands Glossary—An Overview of 25 Most Popular SQL Commands

Learn the basics of the 25 most popular SQL commands and how to use them.
Bekhruz Tuychiev's photo

Bekhruz Tuychiev

8 min

tutorial

SQL Commands for Data Scientists

Learn the basic and essential commands for SQL with examples and a workspace notebook to refer to.

Emiko Sano

12 min

code-along

Getting Started in SQL

Learn how to write basic queries in SQL and find answers to business questions.
Kelsey McNeillie's photo

Kelsey McNeillie

code-along

SQL for Absolute Beginners

Start from the very basics of what SQL is and why it's essential, move through key components such as retrieving data from databases, manipulation of data, and basic SQL queries.
Adel Nehme's photo

Adel Nehme

See MoreSee More