DDL Commands in SQL

DDL stands for Data Definition Language.

Data Definition Language is a bunch of SQL commands used to create, modify and delete Database schema but not the records or data. Data Definition Language includes Structured Query Language Commands used to define the Database structure. The Data Definition Language commands in Structured Query Language define the schema and modify the Database's schema and its objects as well.

List of Data Definition Language commands in Structured Query Language are:

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

Let’s understand each Data Definition Language commands.

1. CREATE Command

In SQL, CREATE command in Data Definition Language is used to create Database and Database objects like to create Table, Index, Views, Triggers, Stored Procedure, and other Database Objects.

Syntax of CREATE Command in Structured Query Language:

CREATE OBJECT OBJECT_NAME; 

Here Object can be Table, Index, View, Trigger, or any other Database Object.

Examples of CREATE Command

Example 1: This example helps us how to create a database using the CREATE Command of the Data Definition Language in SQL:

Syntax to Create a DB (Database):

CREATE DATABASE DATABASE_NAME; 

Now, we will create one Database, and the Database name is School in the SQL Database. To Create a School Database, we will write the following Data Definition Language Command:

CREATE DATABASE SCHOOL;

We defiined the above query to create a database, but how did we know it created a database. To crosscheck whether the Database is created, we will use the SHOW keyword, which will display the entire Database present in the system.

The output of the above query is as follows:

SHOW Databases;

Database

Company

demo

e

information_schema

Mysql

performance_schema

Phpmyadmin

ranking

School

Student

Output 

Now, we will create a table inside this School Database. For this, we will use the following command:

USE SCHOOL;

Now, whatever Object we create, modify or delete will be inside this school Database.

Example 2:  This example helps us how to create a table using the CREATE Command of the Data Definition Language in SQL:

Syntax to create Table:

CREATE TABLE TABLE_NAME (
COLUMN NAME1 DATA_TYPE (COLUMN SIZE),
COLUMN NAME2 DATA_TYPE (COLUMN SIZE),
COLUMN NAME N DATA_TYPE (COLUMN SIZE)
);

Now, we will create one Table, and the Table name is Stud in the SQL Database. To Create Stud Table with some columns, we will write the following Data Definition Language Command:

CREATE TABLE STUD (
STUD_ID INT,
STUD_NAME VARCHAR (30),
STUD_AGE INT,
STUD_CITY VARCHAR (20),
STUD_MARKS INT
);

To crosscheck whether Table is created or not, use DESC table - name

DESC STUD;

2. ALTER Command

Another Data Definition Language command is Alter Command in the Structured Query Language. Alter command is used to add the column, delete the column, or modify a column in an existing table. Alter command is also used to add or drop constraints in an existing table.

Examples of Alter Command:

Example 1: This example helps us with how to add a column to the existing Table:

Syntax to add column in the existing Table:

ALTER TABLE TABE_NAME ADD COLUMN_NAME DATA_TYPE (COLUMN SIZE);

Now we will add the last name column to the existing stud table. To add a column in an existing table, we will write the following Data Definition Language Command:

ALTER TABLE STUD ADD STUD_LAST_NAME VARCHAR(20);

Use the DESC STUD command to check whether the query is executed properly or not

Field	Type	Null	Key	Default	Extra
STUD_ID	int(11)	YES		NULL
STUD_NAME	varchar(30)	YES		NULL
STUD_AGE	int(11)	YES		NULL
STUD_CITY	varchar(20)	YES		NULL
STUD_MARKS	int(11)	YES		NULL
STUD_LAST_NAME	varchar(20)	YES		NULL

Output

Example 2: These examples help how to drop the existing field from the Table.

Syntax to drop a column from the Table:

ALTER TABLE TABLE_NAME DROP COLUMN_NAME;

Now we will drop the last name column from the existing stud table. To drop a column from the existing Table, we will write the following Data Definition Language Command:

ALTER TABLE STUD DROP STUD_LAST_NAME;

Use the DESC STUD command to check whether the query is executed properly or not

Field	Type	Null	Key	Default	Extra
STUD_ID	int(11)	YES		NULL
STUD_NAME	varchar(30)	YES		NULL
STUD_AGE	int(11)	YES		NULL
STUD_CITY	varchar(20)	YES		NULL
STUD_MARKS	int(11)	YES		NULL

3. TRUNCATE Command

The TRUNCATE command is used to delete or remove all the records from the table. These commands only remove data, but the table's structure still exists. And these commands can be rollback after executing the command.

Syntax of TRUNCATE Command:

TRUNCATE TABLE TABLE_NAME;

Example: We will remove all the records from the Stud table, and there is some dummy or temporary data inserted into the stud table.

Table Name: Stud.

STUD_ID	STUD_NAME	STUD_AGE	STUD_CITY	STUD_MARKS
1	Manan	16	Dhule	85
2	Monika	15	Dhule	80
3	Chirag	16	Chalisgaon	95

We will remove all the records of the Stud table using the below query:

TRUNCATE TABLE STUD;  

This query will delete the records; the structure remains to exist.

After executing the above query, execute a SELECT query to check whether data is deleted or not.

SELECT * FROM STUD;

The output shows the empty table.

4. DROP Commands: 

DROP Commands are Data Definition Language Commands used to drop the Database and the Database objects from the Structured Query Language. Using the DROP command, we can easily drop view, Index, and table from the Database.

Example of DROP Commands in Structured Query Language:

Example 1: These examples help how to drop existing Index from the Structured Query Language Database:

Syntax to drop an Index:

DROP INDEX NAME_OF_INDEX;

We want to drop the Stud_Index from the Structured Query Language Database. For this, we will execute the following query:

DROP INDEX STUD_INDEX;

Example 2: These examples help how to drop existing tables from the Structured Query Language Database:

Syntax to drop a table:

DROP TABLE TABLE_NAME

We will drop the Stud table and delete all the table structure records in this example.

DROP TABLE STUD;

Example 3: These examples help how to drop existing Database from the Structured Query Language Database:

Syntax to drop a Database:

DROP TABLE DATABASE_NAME; 

In this example, we will drop School Database.

DROP DATABASE SCHOOL;