DDL Commands in oracle

DDL means Data Definition Language.DDL statements are used to alter/modify a database or table structure and schema. These statements handle the design and storage of database objects.

DDL Commands is divided into three categories as follows:
1. CREATE
2. ALTER
3. DROP

1. CREATE

To create a table in the database, a DBA must have certain information in hand - the table name, column name, column data types, and column sizes. All this information can be modified later using DDL commands.

This command is used create basic database objects in database. For creating table we can use below mentioned syntax.

Syntax

CREATE TABLE tablename
( columnname 1 datatype(size),
columnname 2 datatype(size),
........................................
columnname N datatype(size)
);

In the above syntax CREATE and TABLE are keywords, in place of tablename you have to write your table name.
In brackets write columnname of your table and then provide datatype to the columns, write down all the columns and after last column name do not put comma.

Table Naming Conventions -
• The name you choose for a table must follow these standard rules:
• The name must begin with a letter A-Z or a-z
• Can contain numbers and underscores
• Can be in UPPER of lower case
• Can be up to 30 characters in length
• Cannot use the same name of another existing object in your schema
• Must not be a SQL reserved word

2. ALTER

To alter table in database we have to use alter commands. This command will add, delete or modify columns in table.

To ADD column in table

Syntax

ALTER TABLE tablename ADD
( newcolumnname 1 newdatatype(newsize),
newcolumnname 2 newdatatype(newsize),
...........................................
newcolumnname N newdatatype(newsize)
);

In the above syntax ALTER and TABLE are keywords, in place of tablename you have to write your table name.
In brackets write newcolumnname of your table and then provide newdatatype to the columns, write down all the columns and after last column name do not put comma.

To DELETE column in table

Syntax

ALTER TABLE tablename
DROP COLUMN columnname;
In the above syntax ALTER, TABLE, DROP and COLUMN are keywords, in place of tablename you have to write your table name and in place of columnname you have to name column which you want to remove.

To MODIFY column in table

Syntax

ALTER TABLE tablename MODIFY
( columnname 1 newdatatype(newsize),
columnname 2 newdatatype(newsize),
.........................................
columnname N newdatatype(newsize)
);

In the above syntax ALTER, TABLE and MODIFY are keywords, in place of tablename you have to write your table name and in place of columnname you have to name column which you want to modify. Write new datatype and new size in place of newdatatype(newsize) to modify datatype of column.

3. DROP

To remove table from database we have to use DROP command. This will remove table from database permanently.

Syntax

DROP TABLE tablename;

In the above syntax DROP and TABLE are keywords, in place of tablename you have to write your table name to remove that table from database. Now we will study above commands with example.

Example

Creating customer table