Oracle Create Table AS Statement - oracle tutorial - sql tutorial
Learn oracle - oracle tutorial - Oracle create table using as - oracle examples - oracle programs
How to create table As statement in oracle ?
- The CREATE TABLE AS statement is used to create a table from an existing table by copying the columns of existing table.
Syntax:
CREATE TABLE new_table
AS (SELECT * FROM old_table);
click below button to copy the code. By - oracle tutorial - team
oracle tutorial , sql tutorial , sql , pl sql tutorial , oracle , pl sql , plsql
Create Table Example: copying all columns of another table
- In this example, we are creating a "newcustomers" table by copying all the columns from the already existing table "Customers".
CREATE TABLE newcustomers
AS (SELECT * FROM customers WHERE customer_id < 5000);
click below button to copy the code. By - oracle tutorial - team
Output
Table created.
- This table is named as "newcustomers" and having the same columns of "customers" table.
Create Table Example: copying selected columns of another table
Syntax:
CREATE TABLE new_table
AS (SELECT column_1, column2, ... column_n
FROM old_table);
click below button to copy the code. By - oracle tutorial - team
- Let's take an example:
CREATE TABLE newcustomers2
AS (SELECT customer_id, customer_name
FROM customers
WHERE customer_id < 5000);
click below button to copy the code. By - oracle tutorial - team
- The above example will create a new table called "newcustomers2".
- This table includes the specified columns customer_id and customer_name from the customers table.
Learn oracle - oracle tutorial - Oracle create table using as from multiple tables - oracle examples - oracle programs
Create Table Example: copying selected columns from multiple tables
Syntax:
CREATE TABLE new_table
AS (SELECT column_1, column2, ... column_n
FROM old_table_1, old_table_2, ... old_table_n);
click below button to copy the code. By - oracle tutorial - team
- Let's take an example: Consider that you have already created two tables "regularcustomers" and "irregularcustomers".
- The table "regularcustomers" has three columns rcustomer_id, rcustomer_name and rc_city.
SQL Create Table
CREATE TABLE "regularcustomers"
("RCUSTOMER_ID" NUMBER(10,0) NOT NULL ENABLE,
"RCUSTOMER_NAME" VARCHAR2(50) NOT NULL ENABLE,
"RC_CITY" VARCHAR2(50))/
click below button to copy the code. By - oracle tutorial - team
- The second table "irregularcustomers" has also three columns ircustomer_id, ircustomer_name and irc_city.
CREATE TABLE "irregularcustomers"
("IRCUSTOMER_ID" NUMBER(10,0) NOT NULL ENABLE,
"IRCUSTOMER_NAME" VARCHAR2(50) NOT NULL ENABLE,
"IRC_CITY" VARCHAR2(50))/
click below button to copy the code. By - oracle tutorial - team
- In the following example, we will create a table name "newcustomers3" form copying columns from both tables.
Example:
CREATE TABLE newcustomers3
AS (SELECT regularcustomers.rcustomer_id, regularcustomers.rc_city, irregularcustomers.ircustomer_name
FROM regularcustomers, irregularcustomers
WHERE regularcustomers.rcustomer_id = irregularcustomers.ircustomer_id
AND regularcustomers.rcustomer_id < 5000);