Table of contents
1.
Introduction
2.
What is SQL?
3.
What is Data Definition Language (DDL)?
4.
Examples of DDL in SQL
4.1.
CREATE
4.2.
ALTER
4.3.
DROP
5.
What is Data Manipulation Language (DML)?
6.
Examples of DML in SQL
6.1.
INSERT
6.2.
UPDATE
7.
Comparison Table between DDL and DML
8.
Frequently Asked Questions
8.1.
What is the difference between DDL and DML?
8.2.
What are the types of DML?
8.3.
What is the difference between TRUNCATE and DROP commands?
9.
Conclusion
Last Updated: Mar 27, 2024
Easy

Difference between DDL and DML in SQL

Career growth poll
Do you think IIT Guwahati certified course can help you in your career?

Introduction

In database management, DDL (data definition language) and DML (data manipulation language) are two important concepts. Both of these are used to create and manage databases, but they differ in their functions and usages. In this blog, we will discuss the difference between DDL and DML.

Difference between DDL and DML in SQL

A database is a collection or list of data stored in a computer electronically. Database Management System (DBMS) is the most common way to access and manage these databases. SQL is needed to interact with these databases.  In this blog, we will explain the difference between DDL and DML. But, before starting our main topic, let’s understand what SQL is.

Refer this to know about, sql functions

What is SQL?

SQL is an acronym for Structured Query Language. It lets you access and manipulate databases. SQL commands are used to create tables, insert data, update data, etc. These SQL commands are categorised into DDL, DML, and DCL.

SQL is a query language frequently used in all types of applications. Programmers use it as it integrates well with different programming languages. We can embed SQL queries to build high-performing data processing applications.

What is Data Definition Language (DDL)?

DDL stands for Data Definition Language. It is used to define and create the structure of the databases. We can perform different actions like create, alter, drop, truncate, etc. 

Different commands in DDL

The following list explains these commands in brief : 

  • CREATE: used to create databases.
     
  • ALTER: used to make changes in the structure of databases.
     
  • TRUNCATE: used to delete all data of the table in the databases but not the table.
     
  • RENAME: To rename databases.
     
  • DROP: To drop objects from a database, such as tables.
     
  • COMMENT: To Comment.

Also see, SQL EXCEPT

Examples of DDL in SQL

Following are some examples of DDL commands with their syntaxes:

CREATE

CREATE statements are used to create tables and databases.

Syntax:

CREATE DATABASE DATABASE_NAME;
CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPE);

 

The first syntax statement is for creating databases. The following is to create tables in the database. You can add a comma after every column statement to create a table with multiple columns. Don’t add a comma at the end of the last statement.

For example,

CREATE DATABASE Music;

 

The above SQL query creates a database named Music in the SQL database.

Let’s create a table named songs in the database.

CREATE TABLE Songs
(
Name varchar(200),
Artist varchar(200),
Current_Ranking int
);

Output:

Output for create command

The above query created an empty table named “Songs” in our database.

ALTER

ALTER command allows you to alter the structure of the database.

Syntax:

ALTER TABLE TABLE_NAME
ADD COLUMN_NAME DATATYPE;

 

For example,

ALTER TABLE Songs
ADD Album varchar(20);

Output:

Output for alter command

The above command adds a column name “Album” of varchar data type in the Songs table.

DROP

DROP statements are used to remove database instances.

For example,

DROP DATABASE Music;

This SQL command removes the Music database from SQL database. Let’s see how to remove the Songs table we created above.

DROP TABLE Songs;

What is Data Manipulation Language (DML)?

DML stands for data manipulation language. It is a programming language that gives users the access to modify the data stored in the database. It is used to get data from the database and make changes. This is the main difference between ddl and dml. 

commands in Data Manipulation Language (DML)

Following are some of the actions we perform with DML:

  • SELECT
  • INSERT
  • DELETE
  • UPDATE, etc.
     

It is further classified into Procedural and Non - Procedural DMLs.

Examples of DML in SQL

Following are some examples of DDL commands with their syntaxes:

INSERT

INSERT command is used to add one or more rows of data into a table in the SQL database.

Syntax:

INSERT INTO TABLE_NAME    
VALUES (value1, value2, value3, .... valueN);

For example,

INSERT INTO Songs (Name, Movie, Artist, release_date) 
VALUES ('Tere Liye', 'Prince', 'Atif Aslam', '2010-04-09');

In the above example, the INSERT command is used to add “Tere liye” song details ( Name, Movie, Artist, release_date) in the table Songs.

Output:

Output for insert command

UPDATE

UPDATE command is used to modify the existing data in a table in the database.

Syntax:

UPDATE TABLE_NAME
SET column1 = value1, ...
WHERE CONDITION;

For example,

Output for update command

The above image shows the table before using the UPDATE statement on this table.

UPDATE Products
SET in_stock = false
WHERE product_brand = 'Apple';

In this example, the UPDATE statement is used to update the availability of stock of the ‘Apple’ brand in the “Products” table.

OUTPUT:

Output for update command after updation

Must Read SQL Clauses

Comparison Table between DDL and DML

In this section of the blog, we will compare the two languages we learned in detail until now. Now it's time to discuss the difference between ddl and dml.

DDL

DML

DDL stands for Data Definition Language. DML stands for Data Manipulation Language.
It is used to create the structure of the database objects. It is used to modify the data in the databases.
It is not further classified. It is further classified into Procedural and Non - Procedural DMLs.
It only affects the structure of database instances. It affects the content or the data stored in the database. 
The changes made with DDL are permanent i.e. they are auto-committed. The changes made with DML are not permanent i.e. they are not auto-committed.
There is no use of the WHERE clause in DDL. It uses the WHERE clause while modifying the data.
Examples of DDL commands are CREATE, ALTER, RENAME, etc. Examples of DML commands are UPDATE, SELECT, INSERT, etc.

Must recommended topics, Schema in DBMS and Tcl Commands in SQL

Frequently Asked Questions

What is the difference between DDL and DML?

DDL stands for Data Definition Language, and DML stands for Data Manipulation Language. DDL is used to create the structure of the database whereas DML is used to change the data stored in the databases.

What are the types of DML?

DML is classified into two types: Procedural (or Low Level) and Non-Procedural (or High Level). Procedural DML clearly mentions which and how the data needs to be accessed. Non-Procedural DML does not state how and when to access the data.

What is the difference between TRUNCATE and DROP commands?

DROP is used to remove the whole table or database with its data. Whereas TRUNCATE is used to only remove the data stored in that table or database, not affecting the structure of our database object.

Conclusion

We hope this article was insightful and you learned something new. In this blog, we learned in detail about two of the database languages. We learned about DDL(data definition language) and DML(data manipulation language). We also discussed their syntax with some examples. In the end, we saw the difference between ddl and dml.

If you want to learn about database languages, do visit Database Languages

You also check the following links to know more.

Do visit our website to read more such blogs. You can take mock testssolve problems, and interview puzzles. You can also visit interview experiences and interview bundles for exciting placement preparations. 

Do upvote our blogs if you find them helpful and engaging!

Happy Learning!!

Live masterclass