DDL Commands - Create - Drop - Alter - Rename - Truncate in SQL

                                           Data Definition Language (DDL)

                                                                                               

                This is the first sub-language of SQL which is used to define the database objects such as tables, synonyms, sequences, indexes, views etc

DDL Contains 5 commands

               

                * Create

                * Alter

                * Drop

                * Truncate

                * Rename

1. Create:

 * This command is used for to create the database objects .

Syn: Create table <T.N>(CN1 Data type(size),CN2 Data type(Size)............)

Ex:   Create table emp (empid int,ename varchar(20))

2. Alter:

  * This command is used to modify the structure of a table or to change the business logic of any object. 

  * Using this command we can perform 4 different operations

                                * Adding the new column to the existing table .

                                * Change the column name in the existing table .

                                * Remove column from the table .

                                * Increase / Decrease the column size and change the data type of the column .

Note: We can increase the size of column at any time, but decreasing size depends on data present in the column .

  

1. Alter - Add :

  *  Used to add a new column to a table

               

SYN: alter table <T.N> add <C.N> <Data Type>(Size)

               

   Ex: alter table emp add address varchar (100)

2. Alter-Drop :

                Used to drop the column in the table

Syn:

                Alter table <t.n> drop column <column-name>

 Ex:         Alter table emp drop column deptno

3. Alter-Alter :

              

  *  Used to increase / decrease the column size and to change the datatype of column in the table

Syn: Alter table <t.n> alter column <c.n> datatype(size)

 Ex:  Alter table emp alter column address varchar(50)

Sp_Rename:

   *  To change the column name in the table

SYN: sp_rename 'Tablename.old-Columnname','newcolumn-name'

 EX:  sp_rename 'emp.sal','salary'

Truncate:

   * This command is used to remove all the records except structure from the existing table.

    Syn: Truncate table <T.N>

    Ex:   Truncate table emp