Difference Between DDL and DML

Difference Between DDL and DML

We have provided the important difference between DDL and DML in DBMS. It is an important topic in the GATE CSE syllabus. In the table given below, the difference between DDL and DML is based on various parameters such as uses, effect, commands, full form, classification, etc.

Key Difference Between DDL and DML

DDL	DML
DDL is the acronym for the data definition language.	DML is the acronym for the data manipulation language.
DDL is used to create database schema and also define constraints as well.	DML is used to delete, update and update data in the database.
DDL commands affect the whole table of the database.	DML commands affect the one or two rows of the table.
The DDL language is used to change the structure of the database.	This language is used to manage the data in the database.
DDL does not have further classification.	DML is further classified as procedural DML and non-procedural DML
DDL has basically defined the column(attributes) of the table.	DML adds or updates the row of the table. These rows are called tuples.
DDL statement cannot be rolled back.	DML statement can be rolled back.

Also, check the difference between DBMS and RDBMS.

What is DDL?

Before knowing the difference between DDL and DML, let us discuss DDL. A DDL(Data Definition Language) is a computer language. Basically, it is a component of SQL used to create and modify the structure of database objects in a database. Therefore, it is also known as Data Description Language. The different DDL commands are as follows:

Formulas for GATE Computer Science Engineering – Discrete Mathematics

CREATE

This command is used to create the new database table.

Syntax:

CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,….]);

Example:

CREATE TABLE Doctor(Name VARCHAR3(30), Email VARCHAR3(100), DOB DATE);

Formulas for GATE Computer Science Engineering – Algorithms

DROP

This statement is used to drop the existing table or a database.

Syntax:

DROP TABLE;

Example:

DROP TABLE EMPLOYEE;

ALTER

This command is used to change the database’s configuration or alter the structure of the database. This change could involve either changing the characteristics of an existing attribute or adding a new one.

Syntax:

To add a new column to the table, 

use ALTER TABLE table_name ADD column_name COLUMN-definition; 

To modify an existing column in the table, 

use ALTER TABLE table_name CHANGE column name COLUMN-definition.

ALTER TABLE MODIFY(COLUMN DEFINITION….);

TRUNCATE

The TRUNCATE TABLE command deletes the data inside a table, but not the table itself.

Syntax:

TRUNCATE TABLE table_name;

Example:

TRUNCATE TABLE EMPLOYEE;

What is DML? 

Before knowing the difference between DDL and DML, let us discuss DML. The database is modified using DML commands. It is in charge of all database changes of some kind. Data Manipulation Language (DML) is a programming language for manipulating data. It’s used to get data out of a database and manipulate it. It responds to user requests. The different DML commands are as follows:

INSERT

The INSERT argument is a SQL query. It is used to insert the data into the row of a table.

Syntax:

INSERT INTO TABLE_NAME (column1, column2, col3,…. col N) VALUES (value1, value2, value3, …. valueN);

Or

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

Example:

INSERT INTO gradeup (Author, Subject) VALUES (Peter, DBMS);

UPDATE

This command is used to update or change the value of a table column.

Syntax:

UPDATE table_name SET [column_name1= value1,…column_nameN = valueN] [WHERE CONDITION]

Example:

UPDATE students SET User_Name = ‘Peter’ WHERE Student_Id = ‘4’

DELETE

It’s used to get rid of one or more rows in a table.

Syntax:

DELETE FROM table_name [WHERE condition];

Example:

DELETE FROM Gradeup WHERE Author=Peter;

Further, let us see some other related articles.