There are three DDL commands of SQL
1. Create Table
This statement is used to create the structure of a table in Oracle.
We can specify the various fields which are to be the part of the table as well as their data types, size and range of values which can be stored in those fields.
We can also specify the various constraints which can be applied on a particular field of a table.
Syntax:-
|
Create table <table_name> ( Column1 <data_type>(size) constraint, Column2 <data_type>(size) constraint, ……………………………………… ……………………………………… ); |
Here table_name is any valid name of Oracle.
Column1,Column2,…. are the names of various field of table.
data_type is the name of valid data type of Oracle. It is used to specify the type of value which can be stored in a particular column.
size specifies the maximum number of digits or characters which can be stored in a particular field. It is optional in case of number data type.
constraint is the name of constraint that we want to apply on a particular field.
Example
| Create table student(roll number(3) primary key, name varchar(20) not null, dateofbirth date, fees number(7,2)); |
2. Alter Table
This statement is used to modify the structure of a table in Oracle. We can add or modify fields of an existing table.
We can also specify the data type, size, constraints and domain of fields which are to be added or modified in an existing table.
We can increase the size of any field at any time but we can decrease the size of any field only if that field contains no data.
Alter table command can be used in two ways
Syntax 1:-
|
Alter table <table_name> add ( Column1 <data_type>(size) constraint, Column2 <data_type>(size) constraint, ……………………………………… ……………………………………… ); |
Here table_name is name of any existing table.
add specifies that new field is to be added to the table.
Column1,Column2,…. are the names of various field which are to be added to table.
data_type is the name of valid data type of Oracle. It is used to specify the type of value which can be stored in a particular column.
size specifies the maximum number of digits or characters which can be stored in a particular field. It is optional in case of number data type.
constraint is the name of constraint that we want to apply on a particular field.
Example 1:
| Alter table student add (address varchar2(50)); |
Above command will add a new field address to table named student.
Example 2:
| Alter table student add constraint u1 unique(address); |
Above command will add a unique constraint on address field of student table.
Syntax 2:
|
Alter table <table_name> modify( Column1 <data_type>(size) constraint, Column2 <data_type>(size) constraint, ……………………………………… ……………………………………… ); |
modify specifies that an existing field of a table is to be modified.
Example
| Alter table student modify(name varchar2(25)); |
Above command will increase the width of field named “name” to 25.
3. Drop Table
This statement is used to delete the structure of a table as well as data stored in a particular table.
Syntax:-
| Drop table <table_name> |
Here table_name is any valid name of Oracle.
Example
| drop table student; |