DDL Commands in SQL

DDL is an abbreviation of Data Definition Language.

The DDL Commands in Structured Query Language are used to create and modify the schema of the database and its objects. The syntax of DDL commands is predefined for describing the data. The commands of Data Definition Language deal with how the data should exist in the database.

Following are the five DDL commands in SQL:

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

CREATE Command

CREATE is a DDL command used to create databases, tables, triggers and other database objects.

Examples of CREATE Command in SQL

Example 1: This example describes how to create a new database using the CREATE DDL command.

Syntax to Create a Database:

Suppose, you want to create a Books database in the SQL database. To do this, you have to write the following DDL Command:

Example 2: This example describes how to create a new table using the CREATE DDL command.

Syntax to create a new table:

Suppose, you want to create a Student table with five columns in the SQL database. To do this, you have to write the following DDL command:

Example 3: This example describes how to create a new index using the CREATE DDL command.

Syntax to Create a new index:

Let's take the Student table:

Suppose, you want to create an index on the combination of the City and State field of the Student table. For this, we have to use the following DDL command:

Example 4: This example describes how to create a trigger in the SQL database using the DDL CREATE command.

Syntax to create a trigger:

DROP Command

DROP is a DDL command used to delete/remove the database objects from the SQL database. We can easily remove the entire table, view, or index from the database using this DDL command.

Examples of DROP Command in SQL

Example 1: This example describes how to remove a database from the SQL database.

Syntax to remove a database:

Suppose, you want to delete the Books database from the SQL database. To do this, you have to write the following DDL command:

Example 2: This example describes how to remove the existing table from the SQL database.

Syntax to remove a table:

Suppose, you want to delete the Student table from the SQL database. To do this, you have to write the following DDL command:

Example 3: This example describes how to remove the existing index from the SQL database.

Syntax to remove an index:

Suppose, you want to delete the index_city from the SQL database. To do this, you have to write the following DDL command:

ALTER Command

ALTER is a DDL command which changes or modifies the existing structure of the database, and it also changes the schema of database objects.

We can also add and drop constraints of the table using the ALTER command.

Examples of ALTER Command in SQL

Example 1: This example shows how to add a new field to the existing table.

Syntax to add a newfield in the table:

Suppose, you want to add the 'Father's_Name' column in the existing Student table. To do this, you have to write the following DDL command:

Example 2: This example describes how to remove the existing column from the table.

Syntax to remove a column from the table:

Suppose, you want to remove the Age and Marks column from the existing Student table. To do this, you have to write the following DDL command:

Example 3: This example describes how to modify the existing column of the existing table.

Syntax to modify the column of the table:

Suppose, you want to change the character size of the Last_Namefield of the Student table. To do this, you have to write the following DDL command:

TRUNCATE Command

TRUNCATE is another DDL command which deletes or removes all the records from the table.

This command also removes the space allocated for storing the table records.

Syntax of TRUNCATE command

Example

Suppose, you want to delete the record of the Student table. To do this, you have to write the following TRUNCATE DDL command:

The above query successfully removed all the records from the student table. Let's verify it by using the following SELECT statement:

RENAME Command

RENAME is a DDL command which is used to change the name of the database table.

Syntax of RENAME command

Example

This query changes the name of the table from Student to Student_Details.