What is DDL? / Data Definition Language / Role of Data Definition Language / What can a DDL statement do? / Important aspects of DDL statements
Data Definition Language (DDL)
As specified in the name, DDL is used to define database schemas. With DDL statements we can do the following;
- To create databases.
For example we can create a database named University in MySQL as follows;
CREATE DATABASE University;
- To create table structures.
For example, we can create a table named Student in Oracle as follows;
CREATE TABLE student (Regno NUMBER(10), Name VARCHAR(30));
- To change the structure of the tables.
We can alter the structure of student table by adding/dropping columns, by changing the size of the accepted values etc. For example, the following statement adds a new column with Student table in Oracle;
ALTER TABLE student ADD COLUMN Phone Number(10);
- To remove tables.
We can delete the whole table structure Student using the following statement in Oracle;
DROP TABLE student;
- To rename tables.
To rename an existing table, we can use the following query in Oracle;
RENAME student TO student_table;
- To define referential integrities.
We can define referential integrities like primary key constraints, check constraints, specific type etc using DDL statements. For example, the following query creates a table Teacher with primary key constraint.
CREATE TABLE Teacher(Id NUMBER(3) PRIMARY KEY, Name VARCHAR(30));
- To analyze information.
We can analyze a table, index or clusters for handling performance related issues.
- To add comments to the Data Dictionary.
We can add comments to tables, table columns when they are stored in Data Dictionary. For example, the statement will add a comment ‘Name of the teacher’ with the column definition Name of Teacher table.
COMMENT ON COLUMN Teacher.Name IS ‘Name of the teacher’;
When we execute DDL statements, it does the following things;
1. It does the required things as specified through query. [For example, as mentioned above]
2. Update the special table called Data Dictionary. The Data Dictionary gets updated every time you execute one of the DDL statements.
No comments:
Post a Comment