SQL CREATE TABLE Statement

The create table statement is used to create a new table. The CREATE TABLE statement defines a table. The definition must include its name and the names and attributes of its columns. The definition can include other attributes of the table, such as its primary key or check constraints. This statement comes under the DDL statement.

Syntax:
 
SQL> Create table tablename 
(Column1 data type [default exp] [Constraint],
 Column2 data type [default exp] [Constraint], 
Column3 data type [default exp] [Constraint], 
); 

To create a new table, enter the keywords create table followed by the table name, followed by an open parenthesis, followed by the first column name, followed by the data type for that column, followed by any optional constraints, and followed by a closing parenthesis. It is important to make sure you use an open parenthesis before the beginning table and a closing parenthesis after the end of the last column definition. Make sure you separate each column definition with a comma. All SQL statements should end with a”;”.

Example:
 
SQL> Create table employee 
(Name varchar2 (15), 
Eid number (6), 
Age number (3), 
City varchar2 (30), 
State varchar2 (20)); 
Table created.   

If you want to see the structure of the table then use the following command on SQL prompt.

SQL > DESC employee; 

As you can see that this command displays the entire attribute names with their data type and if constraints are imposed then their detail also.

You can also create a table from an existing table by copying the existing table’s columns. It is important to note that when creating a table in this way, the new table will be populated with the records from the existing table (based on the SELECT Statement).

Syntax #1 - Copying all columns from another table
 
 SQL>CREATE TABLE new _table AS (SELECT * FROM old_table);
 
Example:
 
SQL> CREATE TABLE persons AS (select * from persons);
 
Table created. 

This would create a new table called persons that included all columns from the persons table.

If there were records in the old table, then the new table would also contain the records selected by the SELECT statement.

Syntax #2 – Copying Selected columns from another table

SQL>Create Table Tablename (<columnname1> columnname2>) as select Statement;

Where <columnnamel>, columnname2> are the new columns in new table with new name to be associated with the values returned by the subquery.

Example: SQL> Create Table Persons (Person_id, name) AS SELECT (P_Id, FirstName) from persons;

Table created.

Note: Creating a table from another table without copying any values from the old table:

Example:

SQL> CREATE TABLE persons AS (select * from persons where I=2);
 
Table created. 

Modifying the Table Structure with alter Table Command

After creating the table one may have need to change the table either by adding new columns or by modifying tile existing columns. The SQL ALTER TABLE statement is the SQL command that makes changes to the definition of an SQL table. Table structure can be changed by using alter command. With this command Field type or property can be changed or a new field can be added. This sql alter table command is used like create table command. To alter a table, the table must be in your schema, or you must have alter table privilege.

Anytime you want to change the definition of an SQL table. You could:

• Add a column to a table.

• Change the definition of an existing column in a table.

• Drop a column from a table.

• To enable or disable integrity constraint.

• If need specify the default value for the existing columns.