The CREATE TABLE AS statement is used to create a table from an existing table by copying the columns of existing table.
CREATE TABLE new_table AS (SELECT * FROM old_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);
This table is named as "newcustomers" and having the same columns of "customers" table.
CREATE TABLE new_table AS (SELECT column_1, column2, ... column_n FROM old_table);
Let's take an example:
CREATE TABLE newcustomers2 AS (SELECT customer_id, customer_name FROM customers WHERE customer_id < 5000);
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.
CREATE TABLE new_table AS (SELECT column_1, column2, ... column_n FROM old_table_1, old_table_2, ... old_table_n);
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.
CREATE TABLE "regularcustomers" ( "RCUSTOMER_ID" NUMBER(10,0) NOT NULL ENABLE, "RCUSTOMER_NAME" VARCHAR2(50) NOT NULL ENABLE, "RC_CITY" VARCHAR2(50) ) /
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) ) /
In the following example, we will create a table name "newcustomers3" form copying columns from both tables.
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);