DDL commands of SQL | Create Table | Alter Table | Drop Table

Preview
You must first complete SQL and its types | Structured Query Language Types before viewing this Lesson

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;

 

Lesson tags: alter table command of sql, create table command of sql, drop table command of sql
Back to: SQL using Oracle