Difference Between DDL and DML

Key Difference – DDL vs DML
 

A database is used to store data. There are various types of databases. One common database type is Relational Databases. In these databases, data is stored in tables. Tables consist of rows and columns. A row is a record, and a column is a field. The tables are connected using constraints such as primary keys and foreign keys. Relational Database Management Systems are used to store, retrieve and manipulate data. Some of them are MSSQL, Oracle, MySQL. The language which is using to perform operations on relational databases is called  Structured Query Language (SQL). Data Definition Language (DDL) and Data Manipulation Language (DML) are subcategories of SQL. The key difference between DDL and DML is that DDL is used to change the structure of the database while DML is used to manage the data in the database.

CONTENTS

1. Overview and Key Difference
2. What is DDL
3. What is DML
4. Similarities Between DDL and DML
5. Side by Side Comparison – DDL vs DML in Tabular Form
6. Summary

What is DDL?

DDL stands for Data Definition Language. This language is used to change the structure of the database. Create, Alter, Drop, Truncate are some DDL commands.

Examples of DDL Commands

Refer the following  DDL examples written in TSQL (MSSQL server);

Below statement will create a database named “employee”.

create database employee;

Below statement will delete the existing database employee.

drop database employee;

Below DDL statement is used to create a table.

create table tbl_employee

(

id int not null,

firstName varchar(30),

department varchar(30),

primary key(id)

);

Alter command can be used to add columns, modify existing columns and to drop columns.

An example to add a new column payment to the table tbl_employee is as follows.

            alter table tbl_employee add payment numeric (4,2);

Below statement can be used to drop table.

            drop table tbl_employee;

It is also possible to keep the table structure and to delete the details in the table. This can be done using the truncate command. It can delete all the records in the table and also it will clear the data in the memory. So, it is not possible to roll back the operation.

            truncate table tbl_employee;

What is DML?

DML stands for Data Manipulation Language. DML is used to manage the data in the database. Common DML commands are:  insert, delete, update.

Examples of DML Commands

Following are some DML examples written using TSQL (MSSQL server)

Below statement is used to insert values into the table tbl_employee.

Insert into tbl_employee (id, firstName, department) values (1, “Ann”, “Human Resources”);

Below statement is used to delete the record. Delete command can delete data in the table but it does not completely delete from memory. Therefore, it is possible to roll back the operation.

delete from tbl_employee where id=1;

The update command given below is used to modify a particular row.

update tbl_employee set department = ‘Accounting’ where id=1;

What is the Similarity Between DDL and DML?

• Both are types of Structured Query Language (SQL).

What is the Difference Between DDL and DML?

Summary -DDL vs DML

Relational Database is a common database type. Structured Query Language (SQL) is used to perform operations such as storing, manipulating and retrieving data from relational databases. SQL has main three subcategories. They are DDL, DML and DCL. The difference between DDL and DML is that DDL is used to change the structure of the database and DML is used to manage the data in the database.

Download the PDF Version of DDL vs DML

You can download PDF version of this article and use it for offline purposes as per citation note. Please download PDF version here Difference Between DDL and DML

Reference:

1.Point, Tutorials. “SQL Overview. , Tutorials Point, 15 Aug. 2017. Available here  
2.Point, Tutorials. “T-SQL – DML Statements.”, Tutorials Point, 31 Dec. 1969. Available here 

Image Courtesy:

1.’SQL’By Bagok – Own work, (CC BY-SA 3.0) via Commons Wikimedia