Data Definition language(DDL) Overview

---

Data Definition language(DDL) :

This is the first sub language in SQL, which is used to define any data type object such as table, view, synonyms etc.

• It Contains total five commands. They are

                1) CREATE
                2) ALTER
                3) RENAME
                4) TRUNCATE
                5) DROP

CREATE:
             This command is used to create any database object such as table, view, synonym,index,sequence etc.
Syntax:
            Create Table Table_Name (Col1 Datatype(Size),……..Coln Datatype(Size));

Example:
          Create table Emp(Eid Number(5), Ename Varchar2(15),Sal Number(6,2));

Output: Table created.

• see the example in SQL command prompt :

   Rules for naming a table:

• Table name should be start with an alphabet; it contains minimum 1 Char, Maximum 30 Characters.
• It does not allow any spaces or nay special character except - , #, @,$ and 0-9.
• Should not give the SQL keywords as table name.
• A table can have min of 1 Column and max 1000 columns.
• A table can have max infinite records up to hard disk capacity and min 0 records.
• The rules for table names applicable for column names.

SQL * PLUS Command:
DESC : Describe an oracle Table,View,Synonym,Package and Function.

Syntax: Desc Table_name
Example: Desc Emp

• See the output in the above SQL command prompt.

ALTER:
             This command is used to modify the structure of the base table, using this command we can perform four different operations.

ALTER-MODIFY:
                This command is used to increase or decrease the size of the datatype and also we can change the datatype from old datatype to new datatype.

Syntax: Alter table table_name modify column_name datatype(size);
Example: Alter table Emp modify Sal number(8,2);

Syntax to modify more than one column:
Syntax: Alter table table_name modify(column_name datatype(size),…..,column_name datatype(size)); 

Example:

ALTER-ADD: 

        This command is used to add new column for existing table. 

Syntax: Alter table table_name add column_name datatype(size); 

Example:

 Syntax to add more than one column: 

 Syntax: Alter table table_name  add(column_name datatype(size),…..,column_name datatype(size));

 Example:

NOTE: Whenever we want to add new column, the new column always added to end of the table only.

ALTER-RENAME:

                  This command is used to change the column name from old column name to new column name. 

Syntax: Alter table <table_name> rename column <old_column_name> to <new_column_name>; 

Example:

NOTE: We can’t change more than one column name at the same time. 

Syntax to change the table name: 

Syntax : Alter table <table_name> rename to <new_table_name>; 

Example:

ALTER-DROP:

        This command is used to remove the column from existing table. 

Syntax: Alter table <table_name> drop column <column_name> ; 

Example:

Syntax to drop more than one column:

Syntax: Alter table <table_name> drop(list of columns); 

Example:

RENAME:

   This command is used to change the table name from old table name to new table name.

Syntax: Rename <old_table_name> to <new_table_name>;

Example:

TRUNCATE:

                This command is used to delete all the records permanently from the existing table.

Syntax: truncate table <table_name>;

Example:

DROP :

            This command is used to drop the database object permanently from the database.

Syntax: Drop table <table_name>;

• Flashback(from oracle 10G):This command is used to take back the deleted table from the recycle bin.

          Syntax:  Flashback table <table_name> to before drop;

• Syntax to drop the table permanently:

          Syntax : drop table <table_name> purge;

• Example : Observe the changes in SQL command prompt

NOTE: All DDL commands are auto commit.

---