CREATE TABLE AS SELECT statement can be used when we need to extract part or full data from a table and store it into another table based on certain conditions .
We can create a table based on a query and insert the results of the query into the table
For example if we need the details of all employees whose salary is above certain limit and store it into another table,we can use CREATE TABLE AS SELECT statement.

The following example illustrates this.
Let us create an EMPLOYEE table which holds the employee information like name ,id and salary.

SQL> create table EMPLOYEE(
name VARCHAR2(50),
emp_id NUMBER,
salary NUMBER(8,2));

Table created

Now insert some records in EMPLOYEE table.

SQL> insert into EMPLOYEE values('A',1,12345);
SQL> insert into EMPLOYEE values('B',2,13345);
SQL> insert into EMPLOYEE values('C',3,10345);
SQL> insert into EMPLOYEE values('D',4,14345);

4 row inserted

SQL> select * from EMPLOYEE order by salary;

NAME  EMP_ID SALARY
C     3   10345.00
A     1   12345.00
B     2   13345.00
D     4   14345.00

Now let us create a table for employees whose salary is above 13000.

SQL> CREATE TABLE EMPLOYEE_MAX AS SELECT * from EMPLOYEE WHERE EMPLOYEE.salary>13000;

Table created

Now query the EMPLOYEE_MAX table.

SQL> select * from EMPLOYEE_MAX;

NAME EMP_ID SALARY
B    2   13345.00
D    4   14345.00

The new table has created with records based on the criteria in WHERE clause.

Also Read,Oracle/PLSQL: Create table with foreign key constraint
CREATE TABLE statement: create a table with composite primary key
Oracle PL/SQL:CREATE TABLE statement: create a table with primary key.
Oracle PL/SQL:Create Table
Oracle/PLSQL: Foreign Keys with ON DELETE CASCADE option
ORA-02449: unique/primary keys in table referenced by foreign
ORA-02292: integrity constraint violated – child record found
ORA-02291: integrity constraint violated – parent key not Found
Oracle/PLSQL: Foreign Keys | Oracle Referential Integrity
Oracle/PLSQL: Composite Primary Key
Oracle/PLSQL: Primary Key and Composite Primary Key
ALTER TABLE to ADD PRIMARY KEY in Oracle.

Technorati Tags:
Create Table as Select