Oracle Create Table AS Statement - oracle tutorial - sql tutorial



Oracle create table using as

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.
Oracle create table using as from multiple tables

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.
Oracle create table

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);
click below button to copy the code. By - oracle tutorial - team

This tutorial provides an indepth knowledge on the following items such as oracle tutorial , sql tutorial , sql , pl sql tutorial , oracle , pl sql , mysql tutorial , sql tutorial for beginners , learn sql , oracle database tutorial , sql query tutorial , oracle dba tutorial , plsql tutorial , oracle tutorial pdf , oracle pl sql tutorial , oracle sql tutorial , sql tutorial point , oracle tutorial for beginners , learn oracle online free , learn oracle online , learning pl sql programming , learn sql online for free , sql learning online , dba oracle tutorial , oracle sql tutorial advanced , oracle 11g dba tutorial with examples , oracle online learning , oracle learning online , how to learn pl sql , sql coding tutorial , sql learning websites , sql basic learning

Related Searches to Oracle Create Table Statement