Data Definition language(DDL) in DBMS with Examples: Data Definition Language can be defined as a standard for commands through which data structures are defined. It is a computer language that is used for creating and modifying structure of the database objects, such as schemas, tables, views, indexes, etc. Additionally, it assists in storing metadata details in the database.

Also See: What is SQL, Its Application, Advantages and Disadvantages

Data Definition language(DDL) in DBMS with Examples

Some of the common Data Definition Language commands are:

  1. CREATE- Data Definition language(DDL)

The main use of create command is to build a new table and it comes with a predefined syntax. It creates a component in a relational database management system. There are many implementations that extend the syntax of the command to create additional elements, like user profiles and indexes.

The general syntax for create command in Data Definition Language is mentioned below:

CREATE TABLE tablename
Column1 DATATYPE,
Column2 DATATYPE,
Column3 DATATYPE, ……..
ColumnN DATATYPE)

For Example

CREATE TABLE PUPIL
PUPIL_ID CHAR (10),
STUDENT_Name Char (10);

Pupil Table with his ID and name is created by the DDL statement

Generally, the data types often used consists of strings and dates while creating a table. Every system varies in how to specify the data type.

Also See: Explain Relational Database System RDBMS

2. ALTER- Data Definition language(DDL)

An existing database object can be modified by the ALTER statement. Using this command, the users can add up some additional column and drop existing columns. Additionally, the data type of columns involved in a database table can be changed by the ALTER command.

The general syntax of the ALTER command is mentioned below:

For adding a new column

ALTER TABLE table_name ADD column_name

(for renaming a table)

ALTER TABLE table_name RENAME To new_table_name

(for modifying a column)

ALTER TABLE table_name MODIFY column_name data type

(for deleting a column)

ALTER TABLE table_name DROP COLUMN column_name

For Example

Add column to the pupil table

ALTER TABLE PUPIL ADD PHONE NUMBER varchar 97

Before Adding Column

Pupil ID PUPIL_Name
97 Albert
98 Sameer

After Adding Column

PUPIL_ID STUDENT_NAME MOBILE NUMBER
97 ALBERT  
98 SAMEER  

3. Drop- Data Definition language(DDL)

By the use of this command, the users can delete the database, table or view. A component from a relational database management system can be removed by a DROP statement in SQL.  There are many systems that allow the DROP and some other Data Definition Language commands for occurring inside a transaction and then it can be rolled back.

Also See: Explain Data Mining and its Architecture

The object will not be available for use once the DROP statement executed

The General syntax of Drop command is mentioned below:

If you want to delete a table then its syntax and example will be like this

DROP TABLE table_name;

For Example

DROP TABLE Student;

If you want to delete a database then its syntax and example will be like this

DROP DATABASE database_name;

For Example

DROP DATABASE CollegeDB
  1. Truncate- Data Definition language(DDL)

By using Truncate command, users can remove table content, but structure of the table is kept. In simple language, it removes all the records from the table structure. Users can’t remove data partially through this command. In addition to this, every space allocated for the data is removed by Truncate command.

The syntax of the Truncate command is mentioned below:

TRUNCATE TABLE table_name;

For Example

TRUNCATE TABLE Student;

Also See: Explain Normalization in DBMS

So it was all about Data Definition language(DDL ) in DBMS with Examples, if you liked this article then please share it with your friends.

Data Definition language(DDL ) in DBMS with ExamplesSumit ThakurUncategorizedWhat Is DBMSData Definition language(DDL) in DBMS with Examples: Data Definition Language can be defined as a standard for commands through which data structures are defined. It is a computer language that is used for creating and modifying structure of the database objects, such as schemas, tables, views, indexes, etc. Additionally,...Let's Define DBMS