DDL - Create, Alter, Drop, Truncate and Rename SQL statements in Oracle

DDL - Data Definition Language

Its used to create and modify database objects such as tables etc. DDL are used to define metadata in the database. By default DDL is auto commit, when ever you a execute a DDL commit is triggered

• CREATE : Its used to create the table, schema etc
• ALTER : Modifying existing table by adding column, dropping column
• DROP : Delete table or any other database objects
• TRUNCATE : It clears all the data inside a table
• RENAME : Change the name of the table or any other object

Create SQL Statement

Create statement is used to create the objects. Here we will create the table to demonstrate an example

Create table without primary key or constraints 

SQL> CREATE TABLE hr.emp (

eno NUMBER(10),

ename VARCHAR2(15),

ssn NUMBER(10),

dept VARCHAR2(10),

mgr_id NUMBER(5),

hiredate DATE DEFAULT (sysdate));

Create table with primary key and other constraints

SQL> CREATE TABLE hr.emp (

eno NUMBER(10) PRIMARY KEY,

ename VARCHAR2(15) NOT NULL,

ssn NUMBER(10),

dept VARCHAR2(10),

mgr_id NUMBER(5),

hiredate DATE DEFAULT (sysdate));

Alter SQL Statement 

Alter command is used to modify database objects such as tables etc. Its used to add, drop and rename the columns of the table

Alter table add column

This statement adds column to existing table

SQL> alter table hr.emp add (phone number, email varchar2(35));

Alter table rename column

This statement renames existing column to new column name

SQL> alter table hr.emp rename column phone to mobile_number;

Alter table modify column data type

This statement changes the datatype of a column

SQL>  alter table hr.emp modify email char(50);

Alter table drop column

This statement drops existing column from the table

SQL> alter table hr.emp drop (email, mobile_number);

Drop SQL Statement 

Drop SQL statements are used to drop or delete the database objects

SQL> drop table emp;

Rename SQL Statement

This SQL statement renames an existing table with new table name

SQL> rename emp to employee;

Truncate SQL Statement

This SQL statement deletes all the rows that are present in the table

SQL> truncate table emp;