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.
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:
- 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
- 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.
it was good and useful, but you can add up some more contents relating to it 🙂
Yes it’s true
Its very easy to understand thank you but add some more commands to it so, that it becomes more useful to us.
yeah ! explanation is very simple way thnxxxxxxx .!!