Sunday, October 14, 2012

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

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

          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.
             This command is used to modify the structure of the base table, using this command we can perform four different operations.

                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)); 

        This command is used to add new column for existing table. 
Syntax: Alter table table_name add column_name datatype(size); 

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

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

                  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>

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>

        This command is used to remove the column from existing table. 
Syntax: Alter table <table_name> drop column <column_name> 

Syntax to drop more than one column:
Syntax: Alter table <table_name> drop(list of columns); 

   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>;

                This command is used to delete all the records permanently from the existing table.
Syntax: truncate table <table_name>;

            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.

No comments:

Post a Comment

back to top