DDL Commands & Syntax

In this article, we will discuss the overview of DDL commands and will understand DDL commands like create, alter, truncate, drop. We will cover each command syntax with the help of an example for better understanding. Let's discuss it one by one.

Overview :
Data Definition Language(DDL) is a subset of SQL and a part of DBMS(Database Management System). DDL consist of Commands to commands like CREATE, ALTER, TRUNCATE and DROP. These commands are used to create or modify the tables in SQL.

DDL Commands :
In this section, We will cover the following DDL commands as follows.

1. Create
2. Alter 
3. truncate
4. drop
5. Rename 

Let's discuss it one by one.

Command-1 :
CREATE :
This command is used to create a new table in SQL. The user has to give information like table name, column names, and their datatypes.

Syntax -

CREATE TABLE table_name
(
column_1 datatype,
column_2 datatype,
column_3 datatype,
....
);

Example -
We need to create a table for storing Student information of a particular College. Create syntax would be as below.

CREATE TABLE Student_info
(
College_Id number(2),
College_name varchar(30),
Branch varchar(10)
);

Command-2 :
ALTER :
This command is used to add, delete or change columns in the existing table. The user needs to know the existing table name and can do add, delete or modify tasks easily.

Syntax -
Syntax to add a column to an existing table.

ALTER TABLE table_name
ADD column_name datatype;

Example -
In our Student_info table, we want to add a new column for CGPA. The syntax would be as below as follows.

ALTER TABLE Student_info
ADD CGPA number;

Command-3 :
TRUNCATE :
This command is used to remove all rows from the table, but the structure of the table still exists.

Syntax -
Syntax to remove an existing table.

TRUNCATE TABLE table_name;

Example -
The College Authority wants to remove the details of all students for new batches but wants to keep the table structure. The command they can use is as follows.

TRUNCATE TABLE Student_info;

Command-4 :
DROP :
This command is used to remove an existing table along with its structure from the Database.

Syntax -
Syntax to drop an existing table.

DROP TABLE table_name;

Example -
If the College Authority wants to change their Database by deleting the Student_info Table. 

DROP TABLE Student_info;

Command -5

RENAME:

It is possible to change name of table with or without data in it using simple RENAME command.

We can rename any table object at any point of time.

Syntax -

RENAME TABLE <Table Name> To <New_Table_Name>;

Example:

If you want to change the name of the table from Employee to Emp we can use rename command as 

RENAME TABLE Employee To EMP;