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:
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.
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.
Types of DDL Commands
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:
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;
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.
Query:
SELECT Emp_ID, NAME FROM employee;
Output:
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:
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
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
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.
| Aspect | Data Definition Language (DDL) | Data Manipulation Language (DML) |
| Objective | Concerned with defining and managing schema objects | Focused on manipulating data within the schema objects |
| Action | Involves creating, modifying, or dropping database objects like tables, views, indexes, etc. | Includes retrieving, inserting, updating, or deleting data within these objects |
| Transaction Control | Often auto commits change or may require explicit transaction handling in certain situations | Can be part of explicit transactions and rolled back if needed |
| Scope | Deals with the structure or metadata of the database | Deals with the contents or data values within the database objects |
| Usage | Primarily used in database design, schema changes, and administration tasks | Utilized for data retrieval and modification purposes |
| Examples | CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX | SELECT, 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.