Creating a Table by Using a Subquery

Creates a Table and Inserts Rows

CREATE TABLE    table
                         [(column, column , ...0 ]
AS   subquery

- Match the number of specified columns to the number of subquery columns.

- Define columns with column names and default values.

1. The table is creted with the specified column names, and the rows retrieved by the SELECT statement are inserted into the table.

2. The column definition can contain only the column name and default value.

3. If column specifications are given, the number of columns must equal the number of columns in the subquery SELECT list.

4. If no column specifications are given, the column names of the table are the same as the column names in the subquery.

5. The column data type definitions and the NOT NULL constraint are passed to the new table. The other constraint rules are not passed to the new table. However, you can add constraints in the column definition.

EX)

CREATE TABLE  dept80
AS
   SELECT employee_id, last_name, salary *12 ANNSAL, hire_date
   FROM    employees
   WHERE  department_id = 80;

** salary *12 ANNSAL ==> The alias should be given, otherwise the following error is generated.

ERROR at line 3:
ORA-00998: must name this expression with a column alias.