Monday, January 5, 2015

Data Definition Language (DDL)

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