SQL CREATE TABLE Keyword
The SQL CREATE TABLE keyword is used to create a new table. Creating a table involves providing a name to the table and defining name and data type (e.g. varchar, integer, date, etc.) of each column.
Syntax
The syntax of using CREATE TABLE keyword is given below:
CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, ..... ..... );
Example: Create a table
The mentioned below statement creates a table called Employee which contains five columns: EmpID, Name, City, Age and Salary.
CREATE TABLE Employee ( EmpID INT NOT NULL PRIMARY KEY, Name VARCHAR(255) NOT NULL, City VARCHAR(100), Age INT, Salary DECIMAL(18,2) );
In the above statement, the data type specifies what type of data the column can hold. After the data type, optional attributes can be provided for each column, like NOT NULL is used to ensure that a column cannot have a NULL value. See SQL Constraints for more information.
The above statement will create a empty table named Employee containing five columns. When the table is created successfully it displays a message. Along with this in databases like MySQL and MariaDB, it can also be checked using DESC command as follows:
DESC Employee;
This will produce the result as shown below:
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| EmpID | int(11) | No | PRI | ||
| Name | varchar(255) | No | |||
| City | varchar(100) | Yes | NULL | ||
| Age | int(11) | Yes | NULL | ||
| Salary | decimal(18,2) | Yes | NULL |
Similarly in SQL Server, the description of the table can be checked using EXEC sp_columns command as shown below:
EXEC sp_columns Employee;
This result of the above code will be similar to:
| TABLE_OWNER | TABLE_NAME | COLUMN_NAME | DATA_TYPE | TYPE_NAME | PRECISION | LENGTH |
|---|---|---|---|---|---|---|
| dbo | Employee | EmpID | 4 | int identity | 10 | 4 |
| dbo | Employee | Name | 12 | varchar | 255 | 255 |
| dbo | Employee | City | 12 | varchar | 100 | 100 |
| dbo | Employee | Age | 4 | int | 10 | 4 |
| dbo | Employee | Salary | 3 | decimal | 18 | 20 |
This indicates that the Employee is now available in the database which can be used to store information related to employee.
Create a table using Another table
The following keywords are used to create a table from an existing table:
CREATE TABLE AS Keyword
In databases like MySQL and MariaDB, the CREATE TABLE AS keyword is used to create a table from an existing table by copying the existing table's columns. 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
The syntax for using CREATE TABLE AS keyword given below:
/* copy all columns from a table */
CREATE TABLE new_table
AS (SELECT * FROM old_table);
/* copy selected columns from a table */
CREATE TABLE new_table
AS (SELECT column_1, column2, ...
FROM old_table);
/* copy columns from multiple table */
CREATE TABLE new_table
AS (SELECT column_1, column2, ...
FROM old_table_1, old_table_2, ...);
Example:
Consider a database table called Employee with the following records:
| EmpID | Name | City | Age | Salary |
|---|---|---|---|---|
| 1 | John | London | 25 | 3000 |
| 2 | Marry | New York | 24 | 2750 |
| 3 | Jo | Paris | 27 | 2800 |
| 4 | Kim | Amsterdam | 30 | 3100 |
| 5 | Ramesh | New Delhi | 28 | 3000 |
| 6 | Huang | Beijing | 28 | 2800 |
-
Copy all columns of a table: The below statement is used to create a new table called Employee_PT by copying all columns of Employee table. The new table will contain those records of Employee table where the age of the employee is less than 28.
CREATE TABLE Employee_PT AS (SELECT * FROM Employee WHERE Age < 28); -- see the result SELECT * FROM Employee_PT;
This will produce the result as shown below:
EmpID Name City Age Salary 1 John London 25 3000 2 Marry New York 24 2750 3 Jo Paris 27 2800 -
Copy selected columns of a table: It is also possible to copy selected columns when using CREATE TABLE AS statement. In the example below, the columns which are copied are: EmpID, Name and City.
CREATE TABLE Employee_PT AS (SELECT EmpID, Name, City FROM Employee WHERE Age < 28); -- see the result SELECT * FROM Employee_PT;
This will produce the result as shown below:
EmpID Name City 1 John London 2 Marry New York 3 Jo Paris -
Using AS clause: The AS clause can be used to rename the column name. See the example below:
CREATE TABLE Employee_PT AS (SELECT EmpID, Name AS EmployeeName, City FROM Employee WHERE Age < 28); -- see the result SELECT * FROM Employee_PT;
This will produce the result as shown below:
EmpID EmployeeName City 1 John London 2 Marry New York 3 Jo Paris -
Copy columns from multiple tables: Consider one more table called Contact_Info with the following records:
Phone_Number EmpID Address Gender +1-8054098000 2 Brooklyn, New York, USA F +33-147996101 3 Grenelle, Paris, France M +31-201150319 4 Geuzenveld, Amsterdam, Netherlands F +86-1099732458 6 Yizhuangzhen, Beijing, China M +65-67234824 7 Yishun, Singapore M +81-357799072 8 Koto City, Tokyo, Japan M The below statement is used to create a new table called Employee_PT which will contain selected columns of Employee and Contact_Info tables. The new table will contain records based on INNER JOIN.
CREATE TABLE Employee_PT AS (SELECT A.EmpID, A.Name, A.City, B.Address FROM Employee A INNER JOIN Contact_Info B ON A.EmpID = B.EmpID); -- see the result SELECT * FROM Employee_PT;
This will produce the result as shown below:
EmpID Name City Address 2 Marry New York Brooklyn, New York, USA 3 Jo Paris Grenelle, Paris, France 4 Kim Amsterdam Geuzenveld, Amsterdam, Netherlands 6 Huang Beijing Yizhuangzhen, Beijing, China
SELECT INTO Keyword
In databases like SQL Server, the SELECT INTO keyword is used to create a table from an existing table by copying the existing table's columns. 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
The syntax for using SELECT INTO keyword is given below:
/* copy all columns into new_table */ SELECT * INTO new_table FROM old_table WHERE condition; /* copy selected columns into new_table */ SELECT column1, column2, column3, ... INTO new_table FROM old_table WHERE condition;
Example:
Consider a database table called Employee with the following records:
| EmpID | Name | City | Age | Salary |
|---|---|---|---|---|
| 1 | John | London | 25 | 3000 |
| 2 | Marry | New York | 24 | 2750 |
| 3 | Jo | Paris | 27 | 2800 |
| 4 | Kim | Amsterdam | 30 | 3100 |
| 5 | Ramesh | New Delhi | 28 | 3000 |
| 6 | Huang | Beijing | 28 | 2800 |
-
Copy all columns of a table: The below statement is used to copy all columns of Employee table into a new table called Employee_PT. The new table will contain all records of Employee table where the age of the employee is less than 28.
SELECT * INTO Employee_PT FROM Employee WHERE Age < 28; -- see the result SELECT * FROM Employee_PT;
This will produce the result as shown below:
EmpID Name City Age Salary 1 John London 25 3000 2 Marry New York 24 2750 3 Jo Paris 27 2800 -
Copy selected columns of a table: It is also possible to copy selected columns when using SELECT INTO statement. In the example below, the columns which are copied are: EmpID, Name and City.
SELECT EmpID, Name, City INTO Employee_PT FROM Employee WHERE Age < 28; -- see the result SELECT * FROM Employee_PT;
This will produce the result as shown below:
EmpID Name City 1 John London 2 Marry New York 3 Jo Paris -
Using AS clause: The AS clause can be used to rename the column name. See the example below:
SELECT EmpID, Name AS EmployeeName, City INTO Employee_PT FROM Employee WHERE Age < 28; -- see the result SELECT * FROM Employee_PT;
This will produce the result as shown below:
EmpID EmployeeName City 1 John London 2 Marry New York 3 Jo Paris
❮ SQL Keywords
