DDL Full Form and How is it used in SQL?

If you are a beginner or intermediate then this article gives you a good overview of DDL, DDL full form, and if you are an expert then it may help you to get something new on the same or help to refresh your knowledge. Well, in this article post, you will learn all about DDL full form, the definition of DDL, types of DDL commands and their uses, and its advantage, etc.

What is DDL Full Form?

Guys, DDL full form is Data Definition Language. For in-depth tutorials of each DDL command, please refer to other post tutorials across the SQLPOST Academy website, like “Create Database, Table, Index, Sequence, Aliases, Views, and Users,” etc. You will learn here basic syntaxes of data definition language etc.

Definition of DDL in SQL

Well, DDL is an abbreviation for Data Definition Language. It is used to create, modify, and remove the structure of database objects in a database, such as the creation, modification, or removal of a database or schema, a table, an index, a sequence, an alias, a view, or a user.

Type of DDL Commands in SQL

DDL (Data Definition Language) is a standard command or syntax similar to various programming languages to define the structure of the database objects (schema) in a database –

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

Common use of DDL Commands in DBMS

Data definition language commands in DBMS are described below with sample syntax and examples for database and table objects. Detailed syntax and examples for other database objects will be covered in separate posts. 

1. DDL Create Command

The Create command is used to create new database objects. We have to use the semicolon at the end of the command to execute the query. Database objects will be created and stored in the predefined active location (the root directory). A database normally has a sub-directory that holds the physical files of database objects like – database (or schema), table, index, sequence, alias, view, users, etc. Refer to the below

Syntax:

CREATE Object_Type Object_Name;

Create Database

Create database command is used to create a new database. Database physical file will be created and stored in the predefined active location. Refer to the below syntax and example for more understanding – 

Syntax:

CREATE DATABASE Database_Name;

Example:

CREATE DATABASE SQLPOST_Academy;

Screenshot: Below screenshot for reference –

Create Table

The create table command is used to create a new table in a specific database. In this command, the user must specify the required column names with their data types and default values. Refer to the below syntax and example for more understanding – 

Syntax:

CREATE TABLE Table_Name
(
Column_Name1 Datatype1,
Column_Name2 Datatype2,
Column_Name3 Datatype3,
Column_NameN DatatypeN
);

Example:

CREATE TABLE Employee 
(
Employee_ID Bigint,
Salutation VARCHAR(6),
First_Name NVARCHAR(15),
Middle_Name NVARCHAR(15),
Last_Name NVARCHAR(15),
Full_Name NVARCHAR(50),
Department_ID VARCHAR(10),
Gender VARCHAR(15),
Date_Of_Birth DATE,
Mobile_No VARCHAR(10),
);

Screenshot: Below screenshot for reference –

2. DDL Alter Command

Alter command is used to modify the structure of existing database objects. Refer to the below syntax – 

Syntax:

ALTER Object_Type Object_Name Parameters;

Alter Database

Alter Database command is used to alter or edit the structure of an existing database. Refer to the below syntax and example for more understanding – 

Syntax:

ALTER DATABASE Database_Name Parameters;

Example:

USE master;
GO
ALTER DATABASE SQLPOST_Academy MODIFY NAME = SQLPOST;
GO

Screenshot: Below screenshot for reference –

As per the above screenshot, the database name has not changed and a message displayed by SQL as ‘The database could not be exclusively locked to operate’ means someone is accessing this database. So we have to put this database into single-user mode to rename the database using the below query –

Example:

USE master;
GO
ALTER DATABASE SQLPOST_Academy SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

Screenshot: Below screenshot for reference –

As per the above screenshot, we have done the database into single-user mode. Now we can run the below alter statement to rename the database –

Example:

USE master;
GO
ALTER DATABASE SQLPOST_Academy MODIFY NAME = SQLPOST;
GO

Screenshot: Below screenshot for reference –

Wow! Now the database name is changed. Refer to the below screenshot –

Alter Table

Alter Table command is used to modify the structure of an existing table and its columns in a specific database. Refer to the below syntax and example for more understanding – 

Syntax:

ALTER TABLE Table_Name Parameters;

Alter Table Using ‘Add’ Command

Alter table – Add command is used to add a new column into a specific existing table. Refer to the below syntax and example for more understanding –

Syntax:

ALTER TABLE Table_Name ADD New_Column Data_Type;

Example:

ALTER TABLE Employee ADD Email varchar(50);

Screenshot: Below screenshot for reference –

Alter Table Using Drop Command

Alter Table – Drop command is used to delete an existing column from a table. Refer to the below syntax and example for more understanding – 

Syntax:

ALTER TABLE Table_Name DROP COLUMN Column_Name;

Example:

ALTER TABLE Employee DROP COLUMN Email;

Screenshot: Below screenshot for reference –

3. DDL Drop Command

Drop command is used to remove or delete existing database objects permanently. Refer to the below syntax – 

Syntax:

DROP Object_Type Object_Name;

Drop Database

The drop database command is used to remove or delete an existing database permanently. Refer to the below syntax and example for more understanding – 

Syntax:

DROP DATABASE Database_Name;

Example:

USE master;
GO
DROP DATABASE SQLPOST;
GO

Screenshot: Below screenshot for reference –

Drop Table

Drop table command is used to remove or delete an existing table in a specific database permanently. Refer to the below syntax and example for more understanding – 

Syntax:

DROP TABLE Table_Name;

Example:

DROP TABLE Employee;

Screenshot: Below screenshot for reference –

4. DDL Truncate Command

Truncate command is used to remove all data or records from a table permanently, even resetting the identity columns. The truncate command deletes all data from a specific table, but the structure remains the same. Refer to the below syntax and example for more understanding – 

Syntax:

TRUNCATE TABLE Table_Name;

Example:

TRUNCATE TABLE Employee;

Screenshot: Below screenshot for reference –

Advantage of DDL Commands in SQL

Below are the advantage of DDL commands in SQL for reference –

1. DDL commands are easy to understand, memorize and write at any point in time
2. DDL commands are as per the standard of SQL and ANSI (American National Standards Institute)
3. All schema and database objects can be written and stored easily using DDL commands in SQL
4. Data Definition Language is uniform and this is the advantage of it

FAQs

Conclusion

This tutorial post described basics and advanced knowledge of DDL and DDL full form. DDL stands for Data Definition Language which is a set of SQL commands which is being used to create and manage database objects in an RDBMS. It will provide enough understanding for beginners and professionals. We hope you would have liked this tutorial very well.

Please share this on your social media network so that it can be available to even more needy persons which may be beneficial for them. If you have any questions, feedback, or suggestions regarding this tutorial, please contact us. Do comment right in the comments section below. We will consider your valuable input and try to give you a response asap.

Recommended Article Posts

What is SQL Full Form and Its Key Feature?	What is RDBMS Full Form and Its Key Feature?
DML Full Form and How is it used in SQL?	DCL Full Form and How is it used in SQL?
TCL Full Form and How is it used in SQL?	What is DQL in SQL?
What is Database Language?	What is Data Integrity in SQL?
What is Integrity Constraints in DBMS?	Key Constraints in DBMS With Fundamentals
Primary Key Constraint in SQL	Unique Key Constraint in SQL