syntax and example of create table statement in oracle database query

The CREATE TABLE statement in SQL allows us to create or define a new table in our database. It is a Data Definition Language (DDL) statement.

Here we will learn how to use CREATE TABLE statement to create new tables using already existing tables in the database.

Syntax for using CREATE TABLE statement to create a new table by already existing table(s) is:

CREATE TABLE new_table_name
AS
(SELECT *
FROM
old_table_name
);

Let’s take an example for understanding:

Suppose we already have a table named “Employee” as shown below.

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

Scenario 1:

We want to create a new table named “employee_copy” which should be copy of “Employee” table. For this we need to write the CREATE TABLE statement as follows.

CREATE TABLE employee_copy
AS
(SELECT *
FROM employee);

The above CREATE TABLE statement will create a new table named as employee_copy in the data base having 5 columns as ‘employee_id’,’employee_name’,’salary’,’department’ and ‘commission’. Also all the data from the employee table is copied to the employee_copy table.

If you query employee_copy table as,

SELECT *
FROM employee_copy;

You will get the following result.

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

Scenario 2:

We want to create a new table named “employee_copy” which should be contain only 3 columns of “Employee” table e.g(employee_id, employee_name and salary) . For this we need to write the CREATE TABLE statement as follows.

CREATE TABLE employee_copy
AS
(SELECT employee_id
        ,employee_name
        ,salary
 FROM employee);

The above CREATE TABLE statement will create a new table named as employee_copy in the database having 3 columns as ‘employee_id’,’employee_name’ and ’salary’ from employee table. Also all the data from the employee table of the aforementioned 3 columns will be copied to the employee_copy table.

If you query employee_copy table as,

SELECT *
FROM employee_copy;

You will get the following result:

Employee_ID	Employee_Name	Salary
101	Emp A	10000
102	Emp B	20000
103	Emp C	28000
104	Emp D	30000
105	Emp E	32000

Scenario 3:

Suppose we also have a department table in our database which has the list of departments as shown below:

Department_Id	Department
1000	Sales
2000	IT
3000	Support

And we want to create a new table named “employee_copy” which should be contain only 3 columns of “Employee” table e.g(employee_id, employee_name and salary) and 1 column from the “Department” table e.g. (department). For this we need to write the CREATE TABLE statement as follows.

CREATE TABLE employee_copy
AS
(SELECT employee_id
        ,employee_name
        ,salary
        ,dept.department
 FROM employee emp
      ,department dept
 WHERE emp.department = dept.department);

The above CREATE TABLE statement will create a new table named as employee_copy in the database having 4 columns as ‘employee_id’,’employee_name’ and ’salary’ from employee table and ‘department’ from department table.

The employee_copy table will have the data as shown below:

Employee_ID	Employee_Name	Salary	Department
101	Emp A	10000	Sales
102	Emp B	20000	IT
103	Emp C	28000	IT
104	Emp D	30000	Support
105	Emp E	32000	Sales

The CREATE TABLE statement in SQL allows us to create or define a new table in our database. It is a Data Definition Language (DDL) statement.

Syntax for the CREATE TABLE statement is:

CREATE TABLE table_name
(column_name1 datatype NULL/NOT NULL
,column_name2 datatype NULL/NOT NULL
,column_name3 datatype NULL/NOT NULL
.
.
);

Each column must have a data type. Also every column has to be defined as NULL or NOT NULL, if nothing is specified in NULL or NOT NULL place then the default value will be assumed as NULL.

Let’s take an example for understanding:

Suppose we want to create a table named “Employee” as shown below (without data).

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

Now we will see how we can use CREATE STATEMENT to create or define a new table in data base.

CREATE TABLE employee
(employee_id   NUMBER(10)    NOT NULL
,employee_name VARCHAR2(500) NOT NULL
,salary        NUMBER(20)    NOT NULL
,department    VARCHAR2(300) NOT NULL
,commission    NUMBER(20)
);

The above CREATE TABLE statement will create a new table named as employee in the data base having 5 columns as ‘employee_id’,’employee_name’,’salary’,’department’ and ‘commission’.

CREATE TABLE Statement in Oracle SQL – PLSQL Using Already Existing Tables

CREATE TABLE Statement in Oracle SQL – PLSQL