Data Definition Language(DDL)

Data definition Language:

This is the first sub language in SQL which is used to define the database objects such as table, view, synonym, index and sequence. This language contains five commands those are

i) CREATE.

ii) ALTER.

iii) DROP.

iv) TRUNCATE.

v) RENAME.

i) CREATE:

This command is used to creating the database objects such as Table,View, Synonym, Trigger, Stored, Procedure  etc..,

Syntax: create table <table name>

            (COLUMN1 datatype(Size),

            COLUMN2 datatype(Size),

            COLUMNN datatype(size));

Ex:

SQL>create table emp(empno varhcar(10),

            ename varchar2(10),

            sal number(7,2));

Rules for Creating a table:

• Table name must be unique in schema.
• The table name should begin with a letter ad can be 1-30 characters long.
• Table name should start with alphabets which contains minimum 1 and maximum 30 characters
• it should not allows any spaces or any special characters such as except _, #, $ and 0 to 9.
• A table can have minimum 1 column maximum thousand columns.
• A table can have 0 no of records and maximum n no of records up to hard disk capacity.
• Oracle reserved keywords and words should not be used column names or table names.
• •Names are not case sensitive.

ii) ALTER:

This command is used to modify the structure of the  table. Using this command we can perform four different operations. This command contains four subprograms those are

ALTER- a) Modify

             b) ADD

             c) RENAME

             d) DROP

a) Alter- Modify:

Guidelines for Modifying Columns:

This command is used to increase or decrease the size of the data type and also we can use the data type from all data type to new data type.

Syntax for Modifying Single Column:

ALTER TABLE <Table name> MODIFY (<Column name> data type(size)

EX:

SQL> alter table emp modify ename varchar2(12);

SQL> alter table emp modify(empno number(6));

Syntax for modify more than one column:

ALTER TABLE <Table name> MODIFY (COLUMN1 data type(size), COLUMN2 data type(size)…….,COLUMNN data type(size));

EX:

SQL> alter table emp modify(empno number(6),ename varchar2(12));

SQL> alter table emp modify(ename varchar2(12),deptno number(6));

Guidelines for Modifying Columns:    

• We can increase the width or precision of a numeric column.
• We can decrease the width of a column if the column contains only NULL values and if the table has no rows.
• We can change the data type if the column contains NULL's.
• We can converts a CHER column to the VARCHAR2 data type or convert a VARCHAR2 COLUMN to the CHAR data type if the column contains NULL values or if the size is not changed.

Alter - ADD:

This command is used to add a new column to the existing table.

Syntax for add a single column:

alter table<Table name>add column name datatype(size);

EX:

SQL> alter table emp add deptno number(4);

SQL> alter table employee add(email varchar2(25));

Syntax to add more than one column:

alter table<table name> add (column1 datatype(size), column2 datatype(size)…..,columnn datatype(size));

EX:

SQL> alter table emp add( mobileno number(10), address1 varchar2(30));

SQL> alter table employee add(mobileno number(10),address2 varchar2(30));

Note:

When  we add a new column to a table the new column is always added to the end of the table only.

Alter- RENAME:

This command is used to change the column name from old column name to new column name.

Guidlines for RENAME a Columns: 

• A column can renamed in table by using ALTER RENAME.
• The Feature is enabled from oracle 9.2 onwards.
• We can rename only one column at a time.

Syntax:

alter table <table name> rename COLUMN<old_column_name> to <new_column_name).

EX:

SQL>alter table employe rename column sal to salary;

SQL>alter table emp rename column ename to employe_name;

Syntax for change table name:

ALTER TABLE<old table name> RENAME TO <New table name>

EX:

SQL>alter table emp rename to employee;

SQL>alter table employee rename to emp;

Alter – DROP:

This command is used to remove the column from the existing table.

Guidlines to Drop a Column:

• The column may or may not contain data.
• We can drop more than one column at a time.
• The table must have at least one column remaining in it after it is altered.
• Once a column is droaped it cannot be recovered.

Syntax for Drop a Column:

alter table <table_name> drop column <column_name>;

EX:

SQL>alter table emp drop column empno;

SQL>alter table employee drop column email;

Syntax to drop more than one column:

alter table <table name> drop(<column name1>,<column name2>,....);

Ex:

SQL>alter table emp drop(empno,ename,deptno);

SQL>alter table emp employee(

Note:

Here we can’t drop all columns.

DROP:

• This command is used to drop entire data from database.
• It removes the definition of the oracle table.
• The command not only drops the table and along with the associated INDEXES also.

Guidelines to Drop Table:

• The data is totally deleted from the table.
• Any VIEWS and SYNONYAMS will remain but are dept in invalid state.
• Any pending transactions are committed.
• Only the create or owner of the table or a USER with DROP any table privilege can remove a table from Database.
• The DROP Table statement once executed is irreversible.

Syntax:

drop table <table name> [CASCADE CONSTRAINTS];

Ex:

SQL> drop table emp;

SQL> drop table employee purge;

SQL> drop table temp CASCADE CONSTRAINTS;

TRUNCATE:

• This command is use delete records permanently from the existing table.
• It is used to remove all rows from a table and to release the STORAGE SPACE used by the specific TABLE.
• The TRUNCATE TABLE will not facilitate for ROLLBACK.
• To TRUNCATE a Table must be the WONER of the Table.

Syntax:

Truncate table <table name>

Ex:

SQL>truncate table emp;

SQL>truncate table employee;

RENAME:

This command is used to change the table name from old table name to new table name.

Guidelines for RENAME Columns:

• The RENAME command can be used to change the name of TABLE, VIEW, SEQUENCE, SYNONYM.
• To RENAME the OBJECT we must be the OWNER of the OBJECT.

Syntax:

RENAME <old table name> to <new table name>;

EX:

SQL>rename emp to employee;

SQL>rename employee to emp;