• Skip to primary navigation
  • Skip to main content

Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

  • Home
  • HTML
    • Tags in HTML
    • HTML Attributes
  • OA Framework
    • Item in OAF
    • Regions in OAF
    • General OAF Topics
  • Oracle
    • statement
    • function
    • clause
    • plsql
    • sql
You are here: Home / Oracle / statement / CREATE TABLE Statement in Oracle SQL – PLSQL Using Already Existing Tables

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

October 22, 2012 by techhoneyadmin

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

Filed Under: statement Tagged With: create table statement in oracle plsql, create table statement in oracle sql, CREATETABLEPLSQL, how to use create table statement in oracle database query, how to use create table statement in oracle plsql, how to use create table statement in oracle sql, syntax and example of create table statement in oracle database query, syntax and example of create table statement in oracle plsql, syntax and example of create table statement in oracle sql, using create table statement in oracle database query, using create table statement in oracle plsql, using create table statement in oracle sql

Copyright © 2023 · Parallax Pro on Genesis Framework · WordPress · Log in