The CREATE TABLE statement is employed to establish a new table within a database.
| CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, …. ); |
The column parameters define the names of the table’s columns.
The datatype parameter specifies the kind of data that each column can store (e.g., varchar, integer, date, etc.).
The following example creates a table called “Persons” that contains five columns: PersonID, LastName, FirstName, Address, and City:
| CREATE TABLE Persons ( PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) ); |
The PersonID column is an integer type designed to store integers.
The LastName, FirstName, Address, and City columns are of type varchar, capable of holding character data up to 255 characters in length.
Thus, the empty ‘Persons’ table will be structured as follows:
|
PersonID |
LastName |
FirstName |
Address |
City |
|
|
|
|
|
|
Now you can populate the empty ‘Persons’ table with data using the SQL INSERT INTO statement.
Creating a copy of an existing table is also possible using the CREATE TABLE statement.
The new table inherits the same column definitions as the original. You can choose to replicate all columns or specify certain columns.
If you create a new table from an existing one, the new table will initially contain the same data as the original.
| CREATE TABLE new_table_name AS SELECT column1, column2,… FROM existing_table_name WHERE ….; |
The SQL statement below creates a new table named ‘TestTables’, which is a copy of the ‘Customers’ table:
| CREATE TABLE TestTable AS SELECT customername, contactname FROM customers; |