SQL CREATE TABLE: How-To Guide

The SQL CREATE TABLE statement adds a table to a database. Using this statement, you can specify the names of the columns in the new table and the types of data each column can store. You can also state any constraints related to how data is represented in the table.

Before you run queries and store data in a database, you need to create a table where your data will be stored. A table is where all the data in a database is stored. Each table can contain, which then contains data about a single record within the table.

We use the “CREATE TABLE” statement to create a new table in a database (similar to the CREATE DATABASE statement to create a database). In this tutorial, we’ll discuss the basics of tables on SQL servers and explore how to create them in databases.

SQL CREATE TABLE

The SQL CREATE TABLE statement adds a new table to a database based on the structure you have defined. You must specify the names of each column in the database, relevant constraints, and the data type for each column. You can change the structure of a table after the table has been created.

Let’s take a look at the syntax for the CREATE TABLE statement:

CREATE TABLE table_name (
	column_name data_type constraints,
	column_name1 data_type1 constraints1
);

You can add as many columns to a table as you want. To specify multiple columns, you need to end each line that defines a column with a comma. In the above syntax, we have created two columns.

You must specify at least the names for each column in the database and the type of data that column can store. Table names are required, too. Constaints are optional. SQL Constraints refer to any prerequisites that must be met before data is added to a table.

CREATE TABLE SQL Example

We have been asked by the HR department to put together a database table that stores information about employees. The table should store the following pieces of information about each employee:

• Name
• Email
• Branch
• Address
• Job title
• How many employee of the month awards the employee has won

To create this table, we need to use a CREATE TABLE statement:

CREATE TABLE employees (
	name varchar(20),
	email varchar(50),
	branch varchar(30),
	address varchar(75),
	title varchar(75),
	employee_month_awards integer
);

We have created a table with six columns. The first five columns all have the datatype “varchar,” which is a variable-length string. This means that our string can be as long as we want, up to the maximum string length that we stated.

Names can be no longer than 20 characters and addresses can be no longer than 75 characters. Our last column — employee_month_awards — is an integer and will store the number of awards each employee has received. You can include any recognized data type you want in your table, whether it’s a primary key, foreign key, varchar, or a boolean.

Now that we have created a table, we can start to run queries using it and insert data. Remember, after you create a table, it will be empty, and you’ll need to feed data into your table using the SQL INSERT statement.

SQL Create Table Using Another Table

What if you want to create a table using the structure of another table? You can do that in SQL too, using the CREATE TABLE statement. The new table will have the same column definitions. You can specify whether you want to copy some or all of the columns from another table to your new one.

We want to create a table which contains information about all the executives who work at a company. The executive records are already in the employees database.

To create a new table that contains all of the data on executives from the employees database, we can use this command:

CREATE TABLE executives AS
SELECT name, email, address
WHERE title = 'Executive*'
FROM employees;

Our database has created a table called “executives,” which is a copy of our existing table. This new table includes the columns “name,” “email,” and “address” from our “employee” table, and only includes data from employees who are an executive.

If the employee table was empty, only the structure would be copied — the name, email, and address columns. But if the employee table contained data, it would be copied over to our new database. Only records where the employee is an executive would be copied.

So, our new table has a list of the names, emails, and addresses of everyone whose title starts with “Executive.” 

Conclusion

The SQL CREATE TABLE statement creates a new table in an existing database. To use this statement, you need to define the structure for each column that will appear in your new table.

Do you want to learn more about how to use SQL? Check out our How to Learn SQL guide. This guide contains a list of top books, online resources, and courses to help you build upon your knowledge of SQL.