CREATE TABLE statement in Oracle

Table is a basic unit of storage. When you create table you define the column name and their behavior. You must know the naming rules, data types and create table syntax to create table.

Data Types :

Data Types define the behavior of data values. Every column name must have a datatype. It is useful for example if you define amount column name datatype is float or number you can perform various mathematical operation in select query as well.

Most popular datatypes in oracle are

• Varchar2(size) : variable length character data.
• Number            : define numeric values
• Date                  : date and time values.

Create Table Syntax :

This is the simple syntax of create table statement. default and not null are two optional keywords. 

Create Table example in Oracle Sql Plus:

Open Sql Plus

Type username and password to connect oracle 

Write create table statement in oracle*sql plus:

Once the table is created you can see the structure of table by desc <table Name>  command.

Create table by sub query:

Syntax 1:
Create table <table name> as Select * from <table name>;

create new table based on sub query. It will copy table structure and data.

Ex.
Create table emp_new as select * from employee;

Syntax 2:

Create table <table name> as select <col1,col2,..> from <table name>;               

create table based on given column reference. It also copy data.

Ex.       
Create table emp_new1 as select eno,ename from employee;

Syntax 3:
Create table <table name> as Select * from <table name> where 1<>1;       

create table based on sub query but it copy only the structure of table not data.

Ex.    

Create table emp_new2 as select * from employee where 1<>1;