Difference Between DDL and DML

quiz
Challenge Inside! : Find out where you stand! Try quiz, solve problems & win rewards!

Overview

A database system contains a list of records. The SQL commands are categorized as DML, DDL, DQL, and DCL. Here, we will discuss DML and DDL, the difference between DDL and DML. DDL(Data Definition Language), DML(Data Manipulation Language). The difference between DDL and DML is that DDL focuses on structuring the database, whereas DML focus on manipulating the database.

structured query language

Scope of Article

  • This article defines DDL and DML and explains the commands used in DDL and DML.
  • We will also discuss commands used in DDL, DML along with the syntax.
  • This articles also show the difference between DDL and DML.

SQL commands

DDL

DDL stands for Data Definition Language. The DDL commands help us to define the structure of the database or schema and other database objects. DDL commands are auto-committed, which means the changes made in the database using DDL commands are saved permanently.

The commands that come under DDL language are:

  • CREATE
  • ALTER
  • TRUNCATE
  • DROP
  • RENAME

Why DDL language?

  • DDL allows us to create and structure our database.
  • DDL improves the security efficiency of the data access.
  • Multiple users can work on the same database.

1. CREATE STATEMENT

This statement is used to create the database and its objects like creating tables, functions, views, etc.

Syntax:

CREATE OBJECT_TYPE OBJECT_NAME;

Example:

Creating Database

CREATE DATABASE ABCCOMPANY;

Creating Table

CREATE TABLE CUSTOMER_ID(ID INT);
CREATE TABLE SALES(ID INT);

2. ALTER STATEMENT

This statement is used to alter the existing structure/objects in the database.

Syntax for adding a column in the existing table:

ALTER TABLE TABLE_NAME ADD COLUMN COLUMN_NAME DATATYPE;

Example:

ALTER TABLE SALES ADD COLUMN PURCHASE_ID INT; 

3. TRUNCATE STATEMENT

Truncate statement is used to remove the data from the table, as well as it removes the entire data including objects, structure, and continues to have the table as an object in the database.

Syntax:

TRUNCATE OBJECT_TYPE OBJECT_NAME;

Example:

TRUNCATE TABLE SALES;

4. DROP STATEMENT

It is used for deleting a database object permanently.

Syntax:

DROP OBJECT_TYPE OBJECT_NAME;

Example:

DROP TABLE SALES;

It will delete the table sales completely, if we want the sales again, we have to re-create it using the CREATE statement.

5. RENAME STATEMENT

This statement is used to rename the database and its objects.

Syntax:

RENAME OBJECT_TYPE OBJECT_NAME TO NEW_OBJECT_NAME;

Example:

RENAME TABLE CUSTOMER_ID TO ALT_CUSTOMER_ID;

DML

DML stands for Data Manipulation Language. As its name says, it manipulates the existing data in the database. The changes made by DML commands aren't saved permanently. In DML, we can roll back our changes. Hence, DML commands aren't auto committed.

The commands that come under DML language are:

  • SELECT
  • INSERT
  • UPDATE
  • DELETE

Why DML language:

  • It helps us to manipulate the data.
  • It helps us to specify which data to be manipulated.

1. SELECT STATEMENT

This statement is used to retrieve data from the database objects like tables.

Syntax:

SELECT COLUMN_NAME FROM TABLE_NAME;

Example:

SELECT ID FROM ALT_CUSTOMER_ID;

Here, we extract 'ID' column from table 'ALT_CUSTOMER_ID' which was changed while renaming our 'CUSTOMER_ID' table.

2. INSERT STATEMENT

This statement is used to insert the data into rows of the table.

Syntax:

INSERT INTO OBJECT_NAME(COL1,COL2,...) VALUES(VALUE1,VALUE2,...);

Example:

INSERT INTO ALT_CUSTOMER_ID(ID) VALUES(1);
INSERT INTO ALT_CUSTOMER_ID(ID) VALUES(10);

3. UPDATE STATEMENT

It allows us to update the existing data of the tables.

Syntax:

UPDATE TABLE_NAME
SET [COL1 = VALUE1, COL2 = VALUE2]
WHERE CONDITION;

Example:

UPDATE ALT_CUSTOMER_ID;
SET ID = 2
WHERE ID = 10;

4. DELETE STATEMENT

This is similar to the truncate statement, in the truncate statement, it will delete all the records in the table and only leave with an empty table, whereas in the delete statement, it can either delete all the records or any number of records.

Syntax:

DELETE FROM TABLE_NAME
WHERE CONDITION;

Example:

DELETE FROM ALT_CUSTOMER_ID
WHERE ID = 2;

In this statement, it will delete only that record that has 'ID' = 2.

DELETE FROM ALT_CUSTOMER_ID;

In the above statement, we have not mentioned any condition, so it will delete all the existing records in the entire table.

Difference between DDL and DML

DDLDML
DDL stands for Data Definition Language.DML stands for Data Manipulation Language.
It helps us to define the structure of the database.It helps us to manage the data.
It affects the whole table.It only affects the specified row(s) of the table.
Changes are permanent.We can roll back our changes in the DML language.
It helps in defining the fields or columns of the tables.It helps us in defining rows or records of the tables.
They are auto-committed (changes made are permanent).They are not auto-committed (changes are not permanent).
They don't have further classification.They are classified into procedural and non-procedural.
Commands used: CREATE, ALTER, TRUNCATE, RENAME, DROP.Commands used: SELECT, UPDATE, DELETE, INSERT.

Conclusion

  • The SQL commands are divided into four categories DML, DDL, DCL, and DQL.
  • In this article, we have discussed DDL (Data Definition Language) and DML (Data Manipulation Language). DDL help to define the structure of the database whereas DML helps to manage the data.
  • Commands used in DDL are CREATE, ALTER, TRUNCATE, RENAME, DROP.
  • Commands used in DML are SELECT, INSERT, UPDATE, DELETE.
  • DDL is auto-committed, whereas DML isn't auto-committed.
  • In DML, we can revert the changes that have been made in tables, but in DDL we can't revert the changes.
Challenge Time!
quiz
quiz
Time to test your skills and win rewards! Note: Rewards will be credited after the next product update.
Free Courses by top Scaler instructors
rcbGet a Free personalized Career Roadmap from