Oracle SQL Statement Types and Usage - Part I

Let me introduce SQL by statements type, here statements defines set of commands. Generally statements can fall under the following category

• Data definition Language (DDL)
• Data Retrieval or Query Language (DQL or DRL)
• Data Manipulation Language (DML)
• Data Control Language (DCL)
• Transaction Control Language (TCL)

DDL commands are used to define the data base objects. The following commands are used to define data base objects.

• create
• alter
• truncate
• rename
• drop

CREATE

create command defines a new database object, such as a database, user, table, view, trigger, index, macro, stored procedure, user-defined type, user-defined function, or user-defined macro, depending on the object of the CREATE request.

Syntax:

CREATE TABLE table_name
(column_1 column-definition,
column_2 column-definition,
…
column_n column_definition);

Examples:

CREATE TABLE DEPT
(DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13) );

=======================

ALTER

ALTER statement to add a column, modify a column, drop a column, rename a column or rename a table.

Syntax for Single Column Addition:

ALTER TABLE table_name
ADD column_name column-definition;

Example of Single Column Addition:

alter table dept add dept_type varchar2(10);

alter table emp add end_date date;

Syntax for Multiple Column Addition:

ALTER TABLE table_name
ADD (column_1 column-definition,
column_2 column-definition,
…
column_n column_definition);

Example of Multiple Column Addition:

alter table add(dob date,
gender varchar2(10));

Syntax for Single Column Modification:

ALTER TABLE table_name
MODIFY column_name column_type;

Example of Single Column Modification:

alter table dept modify dept_type varchar2(10) not null;

Syntax for Multiple Column Modification:

ALTER TABLE table_name
MODIFY (column_1 column_type,
column_2 column_type,
…
column_n column_type);

Example of Multiple Column Modification:

alter table modify(dob date NOT NULL,
gender varchar2(6));

Note : We can’t modify any column if table contains data, means if you want to increase range of data then table should be empty.

Syntax for dropping a column from an existing table:

ALTER TABLE table_name
DROP COLUMN column_name;

Example of drop a column in an existing table:

ALTER TABLE emp
DROP COLUMN DOB;

Syntax for renaming a column in an existing table:

ALTER TABLE table_name
RENAME COLUMN old_name to new_name;

Example of rename a column in an existing table:

ALTER TABLE emp
RENAME COLUMN dob to date_of_birth;

Syntax to rename a table:

ALTER TABLE table_name
RENAME TO new_table_name;

Example of rename a table:

ALTER TABLE emp
RENAME TO employee;

=======================

TRUNCATE

By using this command, we can delete data of the table but definition of the object will remain same.

Syntax:

truncate table table_name;

Example:

truncate table emp;

=======================

DROP

By using this command we can delete table permanently from database.

Syntax:

drop table table_name;

Example:

drop table emp;