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

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.

[sourcecode language=”sql”]
CREATE TABLE employee_copy
AS
(SELECT *
FROM employee);[/sourcecode]

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,

[sourcecode language=”sql”]
SELECT *
FROM employee_copy;
[/sourcecode]

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.

[sourcecode language=”sql”]
CREATE TABLE employee_copy
AS
(SELECT employee_id
,employee_name
,salary
FROM employee);
[/sourcecode]

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,

[sourcecode language=”sql”]
SELECT *
FROM employee_copy;
[/sourcecode]

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.

[sourcecode language=”sql”]
CREATE TABLE employee_copy
AS
(SELECT employee_id
,employee_name
,salary
,dept.department
FROM employee emp
,department dept
WHERE emp.department = dept.department);
[/sourcecode]

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

Leave a Reply

Your email address will not be published. Required fields are marked *