Practicing DDL Commands


AIM: Practicing DDL Commands.

Data Definition Language: 

The data definition language is used to create an object, alter the structure of an object and also drop already created object. The Data Definition Languages used for table definition can be classified into following:

Create table command
Alter table command
Truncate table command 
Drop table command

Creating of Tables:

 Table is a primary object of database, used to store data in form of rows and columns. It is created using following command:
 Create Table <table_name>  (column1 datatype (size), column2 datatype (size), ・_, column (n) datatype (size));

Where, table_name is a name of the table and coulumn1, column2 _ column n is a name of the column available in table_name table. R Each column is separated by comma. Pointes to be remember while creating a table. Table Name must be start with an alphabet. Table name and column name should be of maximum 30 characters long. Column name should not be repeated in same table.

Reserve words of Oracle cannot be used as a table and column name. 

Two different tables should not have the same name. 

Underscores, numerals and letters are allowed but not blank space or single quotes

Example:

create table student(sno int , sname varchar(20) , age int , dob date);


Alteration of Table:

Once Simple Table is created, if there is a need to change the structure of a table at that time alter command is used. It is used when a user want to add a new column or change the width of data type or data type itself or to add or drop integrity constraints or column.
 I.e. table can be altered in one of three ways: by adding column, by changing column definition or by dropping column.

 Addition of Column(s) 

Addition of column in table is done using: 

Alter table<table_name>add (column1 datatype, column2 datatype ・_); 


Add option is used with alter table_ when you want to add a new column in existing table. If you want to Add more than one column then just write column name, data type and size in brackets. As usual Comma sign separates each column. For Example, suppose you want to add column comm in emp_master, then you have to perform the following command.


 Deletion of Column:

 Till Oracle8 it is not possible to remove columns from a table but in Oracle8i, drop option is used withAlter table_ when you want to drop any existing column. 

Alter table<table_name> drop column<column name>;

Using above command you cannot drop more than one column at a time.


Modification in Column:

 Modify option is used with Alter table_ when you want to modify any existing column. If you want to modify data type or size of more than one column then just write column name, data type and size in brackets and each column is separated by comma sign as per follows:

Alter table<table name>modify (column1 datatype, ・_);



Truncate Table: 

If there is no further use of records stored in a table and the structure is required then only data can be deleted using truncate command. Truncate command will delete all the records permanently of specified table as follows. 

Truncate table<table name> [Reuse Storage];

 


Drop Table Command:

The command used to drop the table is as follows.

drop table<table_name>;