What is DDL (Data Definition Language) in DBMS

What are DDL Commands?

DDL (Data Definition Language) commands in a database management system are a set of SQL (Structured Query Language) statements used for defining, managing, and manipulating the structure and organization of a database. DDL commands focus on creating, altering, and deleting database objects like tables, indexes, constraints, and views. 

DDL commands are crucial for database administrators and developers as they define the structure and organization of a database. They are typically executed by users with administrative privileges, and improper use of DDL commands can have a significant impact on a database's integrity and functionality.

Here’s a detailed, SEO-friendly, and plagiarism-free explanation of the Difference Between DDL, DML, DQL, and SQL in DBMS, including a comparison table and structured descriptions for each type:

Difference Between DDL, DML, DQL, and SQL in DBMS

1. DDL (Data Definition Language)

DDL refers to the set of SQL commands used to define and manage database structure or schema. It deals with the creation, alteration, and deletion of tables and database objects like indexes or views. These operations directly affect the structure of the database and are auto-committed, meaning changes are saved immediately.

Example Commands:

• CREATE TABLE students (...)
   
• ALTER TABLE students ADD age INT;
   

DDL commands are essential for initial setup and structural updates of a database.

2. DML (Data Manipulation Language)

DML commands are used to manipulate data within the database. They allow users to insert, update, delete, and retrieve records from tables. Unlike DDL, changes made using DML are not auto-committed and require explicit save (COMMIT) or rollback actions.

Example Commands:

• INSERT INTO students VALUES (1, 'John', 20);
   
• UPDATE students SET age = 21 WHERE id = 1;
   

DML operations impact the actual data stored in the database.

3. DQL (Data Query Language)

DQL is a subset of SQL used solely to fetch data from databases. It includes only one major command, SELECT, which is used to query information from tables based on specific conditions or filters.

Example Command:

• SELECT * FROM students WHERE age > 18;
   

DQL does not modify any data or structure; it only retrieves data for reporting or analysis purposes.

4. SQL (Structured Query Language)

SQL is the standard language used to interact with relational databases. It encompasses all other types: DDL, DML, DQL, and also includes DCL (Data Control Language) and TCL (Transaction Control Language). SQL provides a complete set of tools to define, manipulate, control, and query data.

Example Commands:

• CREATE, INSERT, SELECT, GRANT, COMMIT
   

SQL is the overarching framework for database communication and control.

Comparison Table: DDL vs DML vs DQL vs SQL

Types of DDL Commands

The DDL Commands are as follows -

1. CREATE Command
2. DROP Command
3. ALTER TABLE Command
4. TRUNCATE Command
5. RENAME Command
6. COMMENT Command

Keep in mind that DDL Commands care about the structure of the database. They do not alter or play with the data present in the database.

1. CREATE Command

This command is used to create new databases, tables, indexes, views, and schemas. For example, CREATE TABLE, CREATE INDEX, CREATE VIEW.

Examples

Using the following syntax, we can create a new database.

Syntax

CREATE DATABASE name_of_database;

For example, we can use the following command if you want to create a new database name, student_info_db.

CREATE DATABASE student_info_db;

Output

We can use the following syntax to create a new table in our student_info_db database.

Syntax

CREATE TABLE name_of_table (
	name_of_column1 datatype_of_column1     other_constraints,
	name_of_column2 datatype_of_column2     other_constraints,
	name_of_column3 datatype_of_column3     other_constraints,
	name_of_column4 datatype_of_column4     other_constraints,
	….
)

For example, you can create a new table, student_details using the following SQL code.

CREATE TABLE student_details (
	ID int(6)   PRIMARY KEY,
	Name varchar,
	Mobile int(10)
)

Output

Here, we are creating a table student_details having three columns: ID, Name, and Mobile.

2. DROP Command

This command is used to delete a database or delete a column from a database.

Remember that you will lose all your data if you delete the table.

Examples

You can delete a database using the drop command following the following syntax.

Syntax

DROP DATABASE name_of_database;

Let’s create a new database, delete_me_db, to delete it using the drop command.

CREATE DATABASE delete_me_db;

We must write the following command to delete it using DROP Command.

DROP DATABASE delete_me_db;

Output

You can delete a table using the drop command following the following syntax.

Syntax

DROP TABLE name_of_table;

Let’s create a new table, delete_me, to delete it using the drop command.

CREATE TABLE delete_me (
	Id int
);

Our table contains only one column. I.e. Id. We can delete it using the following command.

DROP TABLE delete_me;

Output

Also see, SQL EXCEPT

3. Alter Table Command

We can use the Alter Table command to add a new column, drop an old column or to modify the existing columns. We can also use the Alter Table command to drop old and add new constraints to the table.

Examples

Following is the syntax to add a new column to the table.

Syntax

ALTER TABLE name_of_table
ADD name_of_column datatype_of_column;

Let’s add a new column, email_address, to the table student_details.

ALTER TABLE student_details
ADD Email char(30);

Output

We can also modify the definition of a column using ALTER Table Command. Following is the syntax for the same.

Syntax

ALTER TABLE name_of_table
MODIFY COLUMN name_of_column  datatype_of_column;

Let’s change the definition of the newly added column email_address.

ALTER TABLE student_details
MODIFY COLUMN Email varchar(30);

Output

We can rename a column using Alter Table Command using the following syntax.

Syntax

ALTER TABLE name_of_table
RENAME COLUMN Old_column_name to New_column_name;

Let’s change the name of the Email column to Email_Address;

ALTER TABLE student_details
RENAME COLUMN Email to Email_Address; 

Output

Following is the syntax to delete a column using Alter Table command.

Syntax

ALTER TABLE name_of_table
DROP COLUMN column_name;

Let’s drop the Email_Address column from student_details.

ALTER TABLE student_details
DROP COLUMN Email_Address;

Output

4. Truncate Command

Truncate Command is used to delete the content of the table. It removes all the rows from a table, but the table structure remains intact. It's faster than the DELETE statement for removing all data from a table.

Examples

Following is the syntax to use the truncate command.

Syntax

TRUNCATE TABLE name_of_table;

Let’s add some entries to the table student_details.

INSERT INTO student_details ( ID, Name, Mobile ) values (1, Ram, 9754156624);
INSERT INTO student_details ( ID, Name, Mobile ) values (2, Shyam, 9754158911);

Let’s delete the content of the table student_details using the Truncate Command.

TRUNCATE TABLE student_details;

Output

5. Rename Command

The Rename command is used to rename the table. It is also used to rename the table name with the Alter Table command. It changes the name of an existing database object. For example, RENAME TABLE, RENAME COLUMN.

Examples

Following is the syntax to rename a database using Rename Command.

Syntax

RENAME TABLE old_table_name1 TO new_table_name1, old_table_name2 TO new_table_name2;

Notice that we can rename multiple tables using one statement with the rename command. Tables are renamed from left to right.

Let’s rename the student_details to student_info

RENAME TABLE student_details TO student_info;

Output

6. Comment Command

This command is used to write comments inside the SQL queries. These comments are for documentation purposes and can help database administrators and developers understand the purpose or usage of specific objects within the database. 

We have two types of comments:

• Single Line comments
• Multi-Line comment
   

Examples

1. Single Line comments

They are written using the “--” operator.

– We are creating a new database 

CREATE DATABASE cricketer_db;

Output

2. Multi-Line comments:

They are written using the “/**/”.

/* We have previously created a database cricketer_db

We will delete that database now */

DROP DATABASE cricketer_db;`

Output

DDL Commands vs Other Types of Commands

Frequently Asked Questions

Why DDL is used in DBMS?

DDL (Data Definition Language) is used in DBMS to define and manage the structure of a database. It allows users to create, modify, and delete database objects such as tables, indexes, and views, facilitating efficient data organization and management.

Why DDL is faster than DML?

DDL (Data Definition Language) is generally faster than DML (Data Manipulation Language) because DDL operations involve changes to the structure of the database, which are often simpler and require fewer resources compared to the complex data manipulation operations performed by DML.

What is TCL and DCL commands?

TCL (Transaction Control Language) commands manage transactions in SQL, ensuring data consistency and integrity. They include commands like COMMIT (to save changes) and ROLLBACK (to undo changes). DCL (Data Control Language) commands manage permissions and access, including GRANT and REVOKE.

Conclusion

DDL commands care for the structure of the database. There are various DDL Commands like CREATE, ALTER TABLE,  DROP, RENAME, TRUNCATE and COMMENTS. They have their specific use cases, which were discussed in the article. DDL commands do not manipulate the data in the database. We should take special care while using commands such as DROP and TRUNCATE since they completely remove data from the table or database.

Following are the articles for further reading:

• Difference between DDL and DML in SQL
• Basic SQL Commands and Queries
• Sql functions
• gzip command in linux