• Articles

What are DDL and DML Commands in Structured Query Language (SQL)?

This blog thoroughly explores DDL and DML definitions, purposes, various command types, and important differences, providing a comprehensive idea of how these commands shape database structures and manage data manipulation within these structures.

Table of Contents

Watch this YouTube video tutorial to understand the basics of SQL:

Video Thumbnail

What are DDL Commands?

DDL stands for Data Definition Language. DDL commands are a set of statements used to manage the structure of databases in structured query language (SQL). These commands allow users to create, modify, and delete database objects such as tables, indexes, views, and schemas. Common DDL commands include CREATE, ALTER, DROP, TRUNCATE, and RENAME, each serving specific purposes in managing the database schema and its components.

Why Do We Use DDL Commands? 

  • DDL commands define and create the structure of a database.
  • They help create and modify database objects like tables, views, indexes, etc.
  • DDL commands establish the blueprint for how data will be stored in the database.
  • They set constraints and rules to maintain data accuracy and consistency.
  • DDL commands allow for easy categorization of data for efficient storage and retrieval.

Enroll in our Database Certification Courses to learn more about databases.

What are DML Commands?

DML stands for Data Manipulation Language. DML commands are used to interact with and manipulate data stored within the database. These commands enable users to perform various operations, such as inserting new records, retrieving specific data, updating existing records, and deleting unwanted data from tables. Common DML commands include SELECT, INSERT, UPDATE, and DELETE, providing the tools necessary to manage and modify the data within a database.

Why Do We Use DML Commands?

  • DML commands help in adding/removing new data to the database, which helps in keeping the records up-to-date and accurate.
  • They allow us to find and fetch specific information from the database, helping us get exactly what we need.
  • DML commands enable us to modify or update existing data. This ensures that the data reflects current changes that might occur.
  • They allow us to delete or remove unwanted information from the database.
  • DML commands give us control over how data moves within the database, allowing for organized and effective management of information.

Intellipaat provides a Microsoft SQL Certification Course for its learners.

Types of DDL Commands

Types of DDL Commands in SQL

Data Definition Language (DDL) commands are crucial in database management, enabling structural modifications. There are five types of DDL commands, such as CREATE, ALTER, DROP, etc. We will discuss each of these commands in detail, with syntax and examples.

CREATE Command

The CREATE command is used to create objects in the database, such as tables, views, stored procedures, and more.

Syntax

Create Database Database_Name;  

Example 

Create Database Intellipaat;  

ALTER Command

ALTER command is used to modify the structure of an existing database object. It is also used to remove/add new constraints in the database.

Syntax

ALTER TABLE name_of_table ADD column_name column_definition;  

Example

ALTER TABLE employee ADD Salary INT;

Output: 

alter command in sql

DROP Command

It is used to delete an entire object or part of an object from the database.

Syntax

DROP DATABASE Database_Name; 

Example

DROP DATABASE Intellipaat;

Note: Using the above syntax we can also drop a table or an index.

TRUNCATE Command

Truncate is used to delete all records from a table without affecting the structure of the table.

Syntax 

TRUNCATE TABLE Table_Name;  

Example

TRUNCATE TABLE employee;

RENAME Command

This command is used to rename an existing database object.

Syntax

RENAME TABLE Old_Table_Name TO New_Table_Name; 

Example

RENAME TABLE employee TO employee_details;

Also, check out our blog on Data Control Language!

Types of DML Commands

Types of DML Commands 

There are four main types of Data Manipulation Language. Let us discuss each of them in detail, with syntax and examples.

SELECT Command

SELECT statement is a Data Definition Language (DDL) command that is used to access particular data from one or multiple database tables. It selects the records based on certain conditions, also using the WHERE condition. The table that stores the output records is referred to as the result-set table.

Syntax:

SELECT column_Name_1, column_Name_2, ….., column_Name_N FROM table_name;

Here, column_Name_1, column_Name_2,…, and column_Name_N are the names of the columns from which data is supposed to be retrieved. 

Note: If we want to select all the records, we use the syntax as discussed below.

SELECT * FROM table_name; 

Example: Suppose we have an employee table as shown below, and we want to fetch particular columns such as Emp_ID and NAME.

Table to show the se of SELECT Command in SQL

Query:

SELECT Emp_ID, NAME FROM employee;

Output:

Output for SELECT Command

INSERT Command

INSERT command in SQL allows users to insert data into the database tables.

Syntax

INSERT INTO TABLE_NAME ( column_Name1 , column_Name2 , column_Name3 , .... column_NameN )  

Example: We will insert one more row in the above employee table.

Query:

INSERT INTO employee (Emp_ID, NAME, AGE, Designation) VALUES (7, ‘Ram’, 38, ‘Manager’);

Execution

Select * from employee;

Output:

Output for INSERT Command

UPDATE Command

UPDATE command is a Data Manipulation Language (DML) command that is used to update or modify the existing data present in the database tables. It is used along with the WHERE clause that decides which records need to be updated.

Syntax

UPDATE Table_name SET [column_name1= value_1, ….., column_nameN = value_N] WHERE CONDITION;

Example: Suppose you want to update the AGE of an employee whose Emp_ID is 5. We will use the query below.

Query

UPDATE employee SET AGE = 28  WHERE Emp_ID = 5;  

Output

Output of UPDATE Command

In the above output, you can see that the age of the employee with Emp_ID = 5 has been updated to 28.

DELETE Command

SQL users can delete one or more existing records from database tables using the DELETE command in the Data Manipulation Language. DELETE command does not permanently remove the stored data from the database. To choose particular rows from the table, we use the DELETE command with the WHERE clause.

Syntax

DELETE FROM Table_Name WHERE condition;

Example: Suppose, you want to delete that employee detail from the employee table whose Emp_ID is 4.

Query

DELETE FROM employee WHERE Emp_ID = 4 ;

Output

output for DELETE command

Wish to crack SQL job interviews? Intellipaat’s Top SQL Interview Questions for Freshers are meant only for you!

Differences Between DDL and DML Commands

Data Definition Language (DDL) commands deal with the structure of a database, creating, modifying, or deleting the database objects like tables or indexes, while Data Manipulation Language (DML) commands handle the data within those structures, allowing insertion, retrieval, updating, or deletion of data in the tables.

Various aspects highlight the difference between DDL and DML commands in SQL, emphasizing their distinct roles in managing database structure and data manipulation within that structure.

Let’s understand each difference in the following table.

AspectData Definition Language (DDL)Data Manipulation Language (DML)
ObjectiveConcerned with defining and managing schema objectsFocused on manipulating data within the schema objects
ActionInvolves creating, modifying, or dropping database objects like tables, views, indexes, etc.Includes retrieving, inserting, updating, or deleting data within these objects
Transaction ControlOften auto commits change or may require explicit transaction handling in certain situationsCan be part of explicit transactions and rolled back if needed
ScopeDeals with the structure or metadata of the databaseDeals with the contents or data values within the database objects
UsagePrimarily used in database design, schema changes, and administration tasksUtilized for data retrieval and modification purposes
ExamplesCREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEXSELECT, INSERT, UPDATE, DELETE

Conclusion

In SQL, Data Definition Language (DDL) shapes the database structure through commands like CREATE and ALTER, whereas Data Manipulation Language (DML) manipulates the data within that structure using commands like SELECT, INSERT, UPDATE, and DELETE. DDL focuses on defining schema elements, while DML deals with data manipulation. Both collectively ensure efficient database management and data integrity.

FAQs

Why are DDL commands crucial in database management?

DDL commands shape the foundation of a database by defining its structure, including tables, relationships, and constraints. They lay the groundwork for storing and organizing data efficiently.

Can one user simultaneously perform DDL and DML commands on a database?

Yes, but typically, DDL commands might require more permissions due to their impact on the database’s structure. DML commands, dealing with data manipulation, might have fewer restrictions.

Is Truncate a DDL or DML Command?

While TRUNCATE TABLE and DELETE have similarities, TRUNCATE is categorized as a DDL statement as opposed to a DML statement. Truncate operations, especially for large tables, drop and recreate the table considerably faster than deleting rows one at a time.

Is the DDL command faster than the DML command?

Yes, the DDL command is faster than DML, as the DDL statements cannot be rolled back and do not copy the actual content to the rollback tablespace.

Course Schedule

Name Date Details
SQL Training 05 Oct 2024(Sat-Sun) Weekend Batch
View Details
SQL Training 12 Oct 2024(Sat-Sun) Weekend Batch
View Details
SQL Training 19 Oct 2024(Sat-Sun) Weekend Batch
View Details