DDL(Data definition language)

Data Definition Language(DDL) is used for describing data and its relationship in a database. It is also used to define the database schema. The commands only affect the database structure and not the data.

The main DDL commands are create, alter, drop and truncate.

Create Statement

It is used to create a database or table. While creating the table,we specify table_name,column_name followed by data_types(int,float,varchar,etc) and constraints(primary key,not null,etc)

Syntax

CREATE TABLE table_name
(
   column1 datatype,
   column2 datatype,
   ..... 
)

Example

In this example, we are going to create a table along with the column names and data types.

Algorithm

• Step 1 − Use CREATE to create a table

• Step 2 − Provide the table name

• Step 3 − Provide column name along with their data types

• Step 4 − Provide constraints if any

Code

CREATE TABLE students
(
   id INT PRIMARY KEY,
   Name VARCHAR(20),
   Age INT,
   dob DATE
);

ID	Name	AGE	DOB

Alter Command

It is used to make a change in the structure of a database. Different operations like adding new columns, removing columns, changing data types or adding/removing constraints can be performed.

Syntax

ALTER TABLE table_name ADD column_name datatype;

Here,in place of ADD,we can use remove or modify

Example

In this example, we are going to use ALTER to add a column in a table

Algorithm

• Step 1 − Use ALTER TABLE

• Step 2 − Provide table name

• Step 3 − Provide operation to be performed

Input

student

ID	Name	AGE	DOB

Code

ALTER TABLE students#table on which operation is done
ADD roll_no int;#adding column(roll_no) to table

Output

Students

ID	Name	Age	DOB

Truncate Command

It is used to delete the entities inside the table while holding the structure of the table. It free up the space from the table whereas the column names are left resulting to store new data in the table.

Syntax

TRUNCATE TABLE table_name;

Example

In this example, we are going to use truncate to remove the data inside the table.

Algorithm

• Step 1 − Use truncate table

• Step 2 − Provide table name

Input

Students

ID	Name	AGE	DOB
1	Monu	21	5-12-2000
2	Sonu	22	5-12-1999
3	Aman	23	5-12-1998

Code

TRUNCATE TABLE students;#students table is truncated

Output

Students

ID	Name	Age	DOB

Drop Command

It is used to completely delete a table from the database. the table along with the structure is deleted and can't be found again in the database.

Syntax

DROP TABLE table_name;

Example

In this example, we are going to completely delete a table from the database.

Algorithm

• Step 1 − Use drop table

• Step 2 − Provide table name to be dropped

• Step 3 − Select table to crosscheck

Input

Students

ID	Name	Age	DOB
1	Monu	21	5-12-2000
2	Sonu	22	5-12-1999
3	Aman	23	5-12-1998

Code

DROP TABLE students;#table dropped
SELECT * FROM students;#to show the output

Output

No table is found

Conclusion

DDL is a subset of sql and is used to describe data and the relationship. Some of the DDL commands are create, alter, truncate and drop. create is used to create a table, alter is used to alter the structure of database, truncate is used to remove the entities from the table and drop is used to delete the table completely from the database.