Write about data definition language commands (DDL)

The data definition language commands are used to create objects (tables). Alter the table structure of an object, and also to drop the object, the DDL contains the following commands.

1. Create
2. Alter
3. Drop
4. Truncate

1. CREATE: The create command is used to create database objects such as table, view, index etc. The general syntax for create statement is.

Syntax:

Sql> create table <table name> (<column name1> data type (size), <column name2> data type (size),…………. <column name n>   data type(size));

Example:

Sql> create table emp(eno number(4),ename  varchar2(15), zip number(6), hdate date);

A table is one of the objects of Oracle database. It is the basic unit of data storage in a relational database management system. Every table has a table name and a set of columns (fields) and rows (records) in which the data is stored. Each column is identified by a column name and it is given a data type and width.

 

2. Alter: Sometimes there may be need to change the structure of a table. It is used for to modify structure of existing database table. This modification may be changing the datatype, constraints of the properties or adding new properties to the existing objects. The general syntax for alter command is

Syntax:

Alter table < table name> keyword (<column name1> data type (width));

Keywords

1. Modify       b. Add                c. Rename                 d. Drop

a) Modify:  It is used for

1. To increase or decrease size of the column.
2. To change data type of the column.
3. To change not null to null and null to not null constraint.

Example:

1. Increase the size of ename column name to 30 bytes.

Sql> alter table employee modify ename varchar2(30);

2. Change the data type of salry column to varchar2

Sql>alter table emp modify sal varchar2(20);

3. Change null constraint of job column to not null

Sql>alter table emp modify job not null;

b) Add: It is used for

1. To add new column to the existing table.
2. To add new constraint to the existing columns except not null constraint.

Example

1. Add remarks column to emp table.

Sql> alter table emp add remarks varchar2(40);

2.Add check constraint to the sal column .

Sql> alter table add check (sal between 15000 and 25000);

c) Rename: It is used for to rename a column.

Example: Rename sal column to salary column.

Sql> alter table emp rename column sal to salry;

d) Drop: It is used for to drop a column.

1. To remove a column remarks

Sql> alter table emp drop column remarks;

2. Remove pho and mail -id columns from emp table

Sql>alter table student drop(pho, mail-id);

3. Drop:  It is used for to drop database object (table). Whenever we can use this command we can loss the data permanently we cannot recollect.

Syntax:

Sql> drop table <table name>

Examples

Remove student table from the database.

Sql> drop table student;

4. Truncate: The truncate command is used to delete the records (data) only not table structure. The general syntax for truncate command is.

Syntax: truncate table <table name>

Sql> truncate table student;