1.0

ROOMS_TAKEN INT DEFAULT 0,
PRIMARY KEY (HOTEL_ID, BOOKING_DATE));
CREATE TABLE ... AS ...
With the alternate form of the CREATE TABLE statement, you specify the column names and/or the column
data types with a query. The columns in the query result are used as a model for creating the columns in the new
table.
If no column names are specified for the new table, then all the columns in the result of the query expression
are used to create same-named columns in the new table, of the corresponding data type(s). If one or more column
names are specified for the new table, the same number of columns must be present in the result of the query
expression; the data types of those columns are used for the corresponding columns of the new table.
The WITH NO DATA clause specifies that the data rows that result from evaluating the query expression are
not used; only the names and data types of the columns in the query result are used. The WITH NO DATA clause
must be specified.
Example of CREATE TABLE...AS...
Create a new table using all of the columns and data types from an existing table, T1:
CREATE TABLE T3 AS SELECT * FROM T1 WITH NO DATA;
Create a new table, providing new names for the columns but using the same data types as
the columns of an existing table:
CREATE TABLE T3 (A,B,C,D,E) AS SELECT * FROM T1 WITH NO DATA;
Create a new table, providing new names for the columns but using the data types from
specific columns of an existing table:
CREATE TABLE T3 (A,B,C) AS SELECT V,DP,I FROM T1 WITH NO DATA;
This example shows that the columns in the result of the query expression may be unnamed
expressions, but their data types can still be used to provide the data types for the
corresponding named columns in the newly-created table:
CREATE TABLE T3 (X,Y) AS SELECT 2*I,2.0*F FROM T1 WITH NO
DATA;
Column Definition
The column definition defines the name of a column and its data-type. The set of supported data-types are detailed
in Data Types on page 573.
column-name data-type
[ column-constraint ]*
[ [ WITH ] DEFAULT { constant-expression | NULL }
| [ GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
[ ( START WITH integer-constant [, INCREMENT BY integer-constant
] ) ] ] ]
[ Column Constraint on page 453 ]*
GENERATED ALWAYS and GENERATED BY DEFAULT Identity Columns
SQLFire supports both GENERATED ALWAYS and GENERATED BY DEFAULT identity columns only for
BIGINT and INTEGER data types. The START WITH and INCREMENT BY clauses are supported only for
451
SQL Language Reference