DDL Full Form

Clients can store, direct, and recover information from data sets utilizing programming known as a data set administration framework (DBMS). DBMS languages and interfaces, an essential component of a DBMS, enable user interaction with the database system.

Data Definition Language is known as DDL in SQL. It is a portion of SQL (Structured Query Language) that is used to specify and control a database's structure. Database objects like tables, indexes, views, and constraints are created, modified, and deleted using DDL commands.

DDL Commands

There are several DDL commands, such as −

• CREATE − The create command is used to create a new database object, such as a table, view, or index. The syntax of the CREATE command will change based on the kind of object that is being formed.

Example

For instance, the SQL query that follows creates the "employees" database with the columns "id," "name," and "salary" −

CREATE TABLE employees (
   id INT PRIMARY KEY,
   name VARCHAR(50) NOT NULL,
   salary DECIMAL(10,2)
);

Output

+----+--------+---------+
| id |  name  |  salary |
+----+--------+---------+

• ALTER − ALTER is used to change a database object's existing structure. You can create, alter, or delete columns, constraints, or indexes with the ALTER command.

Example

For instance, the "employees" database gets a new field called "department" thanks to the SQL query that follows −

ALTER TABLE employees ADD department VARCHAR(50);

Output

+----+--------+--------+-------------+
| id |  name  | salary |  department |
+----+--------+--------+-------------+

• DROP − DROP is used to remove a table, view, or other existing database object. The object and any related data are permanently deleted with the DROP command.

Example

For instance, the "employees" table is deleted with the SQL query that follows −

DROP TABLE employees;

Output

The "employees" table and all of its data would be permanently wiped following the execution of the "DROP TABLE employees" command, and there would be no input or output table to show.

• TRUNCATE − Every piece of data from a table that already exists can be removed using TRUNCATE. The Shorten order, as opposed to the DROP order, basically eliminates the information from a data set, not the actual table.

Example

Let’s say this was the employee's table −

+----+--------+--------+-------------+
| id |  name  | salary |  department |
+----+--------+--------+-------------+
| 1  | Alice  | 50000  | Engineering |
| 2  | Bob    | 60000  | Marketing   |
| 3  | Charlie| 75000  | Sales       |
| 4  | Dave   | 65000  | Engineering |
| 5  | Eve    | 55000  | Marketing   |
+----+--------+--------+-------------+

For instance, the "employees" table is truncated by the SQL expression that follows −

TRUNCATE TABLE employees;

Output

Output Table:
+----+------+-------+------------+
| id | name | salary| department |
+----+------+-------+------------+
|    |      |       |            |
+----+------+-------+------------+

The "employees" table's data is completely removed by the TRUNCATE command, but the table itself is left intact. The table would still be there once the command was run, but it would be empty of data rows. The output table displays the "employees" table's structure after the command has been run but without any data.

• RENAME − This command is used to rename a table or column in an existing database.

Example

Input Table

+----+--------+--------+-------------+
| id |  name  | salary |  department |
+----+--------+--------+-------------+
| 1  | Alice  | 50000  | Engineering |
| 2  | Bob    | 60000  | Marketing   |
| 3  | Charlie| 75000  | Sales       |
| 4  | Dave   | 65000  | Engineering |
| 5  | Eve    | 55000  | Marketing   |
+----+--------+--------+-------------+

For instance, the "staff" table is renamed in the following SQL query from the "employees" table −

RENAME TABLE employees TO staff;

Output

+----+--------+--------+-------------+
| id |  name  | salary |  department |
+----+--------+--------+-------------+
| 1  | Alice  | 50000  | Engineering |
| 2  | Bob    | 60000  | Marketing   |
| 3  | Charlie| 75000  | Sales       |
| 4  | Dave   | 65000  | Engineering |
| 5  | Eve    | 55000  | Marketing   |
+----+--------+--------+-------------+

The CQL command "RENAME TABLE employees TO staff" just renames the existing "employees" table to "staff" in the current key space, therefore there is no change to the input and output tables. The table's columns and contents stay the same.

As a result, the data and structure of the input and output tables are identical. The sole distinction is the name of the table, which has been changed from "employees" to "staff".

Conclusion

The management of a database's structure depends on DDL instructions. DBAs may build, alter, and destroy database objects including tables, views, and indexes using DDL commands. Additionally, data integrity restrictions like primary keys, unique constraints, and foreign keys are enforced via DDL commands.