Data Definition language (DDL ) in DBMS with Examples
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.
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 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)
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
Add column to the pupil table
ALTER TABLE PUPIL ADD PHONE NUMBER varchar 97
Before Adding Column
After Adding Column
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;
DROP TABLE Student;
If you want to delete a database then its syntax and example will be like this
DROP DATABASE database_name;
DROP DATABASE CollegeDB
- 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;
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.