Create Table As in Oracle

In Oracle, you can use the CREATE TABLE AS statement to create a new table based on the result set of a query. This is often used to copy data from an existing table, or to generate a new table based on some transformation or aggregation of existing data.

Here's the syntax for CREATE TABLE AS in Oracle:

CREATE TABLE new_table_name

AS

SELECT column1, column2, ...

FROM existing_table_name;

Here's an example:

Let's say you have an existing table called employees with columns employee_id, first_name, last_name, salary, and department_id, and you want to create a new table called high_salary_employees to store information about employees with a salary higher than a certain threshold, let's say $100,000.

You can use the CREATE TABLE AS statement as follows:

CREATE TABLE high_salary_employees

AS

SELECT employee_id, first_name, last_name, salary, department_id

FROM employees

WHERE salary > 100000;

This statement will create a new table called high_salary_employees with the same columns as the employees table, and it will contain only the rows where the salary column is greater than $100,000.

Keep in mind that the new table created with CREATE TABLE AS will not inherit constraints, indexes, triggers, or any other objects associated with the source table. You may need to recreate those objects on the new table if needed. Additionally, column data types and lengths will be inherited from the result set of the query, so it's important to ensure they are compatible with the data being inserted into the new table.

Here are some FAQ's about CREATE TBALE:-

1. What is the Purpose of the CREATE TABLE AS Statement in Oracle?

   The CREATE TABLE AS (CTAS) statement in Oracle is used to create a new table by copying the structure and data from an existing table or the result set of a query. It allows you to quickly create a new table with the same schema and data as an existing table or a query result.

2. What is the Syntax of the CREATE TABLE AS Statement?

   The syntax of the CREATE TABLE AS statement in Oracle is:

   

   CREATE TABLE new_table AS

   SELECT column1, column2, ...

   FROM existing_table

   WHERE conditions;

   

   This statement creates a new table named new_table with the same columns and data types as the columns selected from existing_table based on the specified conditions.

3. Can I Create a Table Using CREATE TABLE AS Without Specifying an Existing Table?

   Yes, besides copying from an existing table, you can also use CREATE TABLE AS with a SELECT statement to create a table based on the result set of a query without referring to an existing table. This is often used to create summary tables or to transform and store query results.

4. Does CREATE TABLE AS Copy Indexes, Constraints, and Triggers?

   No, the CREATE TABLE AS statement only copies the structure and data of the source table or query result. It does not copy indexes, constraints, triggers, or any other database objects associated with the source table. You would need to recreate these separately if required.

5. What Permissions Are Required to Use CREATE TABLE AS?

   To use the CREATE TABLE AS statement, the user needs appropriate permissions to create tables in the target schema or tablespace where the new table will be created. Additionally, if the statement involves selecting data from an existing table, the user needs permissions to read from that table. Depending on the privileges granted, users may also need permissions to create objects in the database.