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 used for creating and modifying the structure of the database objects, such as schemas, tables, views, indexes, etc. Additionally, it assists in storing the metadata details in the database.
Data Definition language(DDL) in DBMS with Examples
Some of the common Data Definition Language commands are:
CREATE- Data Definition language(DDL)
The main use of the 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 the additional elements, like user profiles and indexes.
The general syntax for the create command in Data Definition Language is mentioned below:
CREATE TABLE tablename (Column1 DATATYPE, Column2 DATATYPE, Column3 DATATYPE, …….. ColumnN DATATYPE)
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
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:
ALTER TABLE table_name ADD column_name (for adding a new column)
ALTER TABLE table_name RENAME To new_table_name (for renaming a table)
ALTER TABLE table_name MODIFY column_name data type (for modifying a column)
ALTER TABLE table_name DROP COLUMN column_name (for deleting a column)
Add column to the pupil table
ALTER TABLE PUPIL ADD PHONE NUMBER varchar 97
Before Adding Column
After Adding Column
Drop- Data Definition language(DDL)
By the use of this command, the users can delete an index, 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 the Drop command is mentioned below:
DROP TABLE table_name;
DROP DATABASE database_name;
DROP TABLE Student;
DROP TABLE index_name;
Truncate- Data Definition language(DDL)
By using the Truncate command, the users can remove the table content, but the structure of the table is kept. In simple language, it removes all the records from the table structure. The 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;
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.