oracle
  1. oracle-create-table-as
PreviousNext

Create Table As - (Oracle Tables)

Oracle's CREATE TABLE AS statement is used to create a new table and populate it with the result set of a SELECT statement. This is also known as a "query table".

Syntax

The basic syntax of the CREATE TABLE AS statement is as follows:

CREATE TABLE new_table AS
SELECT column1, column2, column3...
FROM existing_table
WHERE condition;

Here, SELECT ... FROM is the SELECT statement that will generate the result set, and new_table is the name of the new table that will be created. The columns in new_table will have the same data types as the columns in existing_table, unless data type conversions are explicitly applied.

Example

Consider the following example, which uses the employees table in an Oracle database:

CREATE TABLE sales_staff AS
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE department_id = 80;

This statement creates a new table sales_staff that contains the employee_id, first_name, last_name, and salary columns from the employees table. Only employees in the sales department (department_id = 80) are included in the SELECT statement.

Output

The output of the CREATE TABLE AS statement is the creation of a new table in the database with the specified columns populated with the results of the SELECT statement.

Explanation

In the above example, the CREATE TABLE AS statement is used to create a new table called sales_staff, which contains information about employees working in the sales department. The SELECT statement specifies the columns that should be included in the new table, as well as a condition that limits the data to employees in the sales department.

Use

CREATE TABLE AS is useful when you need to create a new table that contains a subset of an existing table, or when you need to transform data in an existing table into a new format.

Important Points

  • CREATE TABLE AS is used to create a new table and populate it with the result set of a SELECT statement.
  • The columns in the new table will have the same datatypes as the columns in the original table, unless datatype conversions are explicitly applied.
  • CREATE TABLE AS is useful for creating new tables that contain a subset of an existing table, or for transforming data in an existing table into a new format.

Summary

In summary, CREATE TABLE AS in Oracle is a useful SQL statement for creating a new table and populating it with the results of a SELECT statement. It can be used to create a new table that contains a subset of an existing table, or for transforming data in an existing table into a new format.

Create TableAlter Table
Published on: