Data Definition Language

Data Definition Language commands are used to define the structure of the table. Data Definition Language commands are:
CREATE
ALTER
DROP
TRUNCATE

CREATE : This is used to create database objects like tables, views, indexes, sequences, procedures, packages, functions, etc.

Syntax:
CREATE tbale table_name(columnname1 datatype, columnname2 datatype, ………..)

Example:

create table pinnapa(tech_name varchar2(30), author_name varchar2(50));

To view the structure of database
desc object_name;
desc pinnapa;

ALTER: This is used to change the structure of the existing table. We can add, modify or drop table structure.

Add: It is used to add column to existing table.
Syntax: alter table tabl_name add(column_name datatype);

Example:

alter table pinnapa add(published_date date);

Modify: It is used to change column datatype or column data.
Syntax: alter table table_name modify(column_name datatype(size));

Example: alter table pinnapa modify(tech_name varchar2(25));

Adding column to table:
alter table pinnapa add address varchar2(30);
desc pinnapa;

Truncate: Truncate us used to delete total data permanently from a table. From Oracle 7.0 onwards truncate is introduced. Only data will be deleted. But, remains there in the database.

Syntax: TRUNCATE table table_name;

Example:

create table pinn as select*from emp;
select* from pinn;

Truncate table pinn;

Testing:
desc pinn; or select * from pinn;

Rename: Rename is used to rename a table and column.

Syntax: rename oldtable_name to newtablename;

Example: rename pinn to pinna;

Renaming a column: We can rename a column name using rename keyword.
Syntax:
alter table table_name rename column old_column_name to new column_name;

Example: alter table pinnapa rename column author_name to author_name1;

DROP: It is used to remove columns from the table
Method 1: If you want to drop a single column at a time. we use following syntax.
Syntax:
alter table table_name drop column column_name;

Example:

alter table pinnapa drop column published_date;

Method2: If you want to drop single or multiple columns with using () when we are using following Syntax: alter table table_name drop(column_name1, column_name2,…);

Example:

alter table pinnapa drop(published_date);

Drop is also used to remove database objects from database.
Syntax: drop objecttype objectname;

Example:

drop table pinnapa;
drop index tech_Index1;

If we want to get it back from recycle bin:
Syntax: flashback table table_name to before drop;

Example: table

flashback table pinnapa to before drop

To drop permanently: We can delete table permanently by appending the merge to drop statement.
drop table pinnapa purge;

We can also drop all tables from recyclebin using purge recyclebin;

Powered by k2schools