SQL Create Table

Databases are an important part of storing information with computers. Programmers can efficiently store and retrieve many kinds of data in databases. Databases can hold anything from employee payroll information to elementary school class rosters.

Before you run queries and store data, you need to create a table where your data will be stored. A table is where all the data in a database is stored. In this tutorial, we’ll discuss the basics of tables on SQL servers and explore how to create them in databases.

Database Structure Refresher

Before we make our tables, let’s take a moment to review database structures. Databases are sets of tables used for storing data, and each table has its own name and can relate to other tables within a database. For example, you may have a database to store all of a company’s digital information.

Each database has a number of tables, which is a collection of rows and columns. For example, you may have a table that stores user information for an employer intranet (or private network). Each table contains a row, which then contains data about a single record within the table. In this case, a row can contain an individual employee’s login for the intranet and basic information about themselves.

SELECT column_name FROM table_name WHERE your_conditions_here;

SQL Create Table

Now that we have reminded ourselves of how databases are constructed, we can explore how to create a table in SQL. As we mentioned, tables are a crucial part of databases, and you’ll need to create one before you start to store information or run queries.

In SQL, we use the “CREATE TABLE” statement to create a new table in a database (similar to the CREATE DATABASE statement to create a database). We then specify what column names we would like to use and the datatypes of those columns. Here is the structure:

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

So, if we wanted to create a table of customers, we would use “CREATE TABLE customers,” then specify what columns we wanted our table to use.

Let’s use a real example to illustrate how the CREATE TABLE statement works:

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

Let’s break this down. 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.

So, names can be no longer than 20 characters; 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 “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, and you can specify whether you want to copy some or all of the columns from another table to your new one.

Here’s an example of this in action:

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

Let’s break this down. 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 if the employee was an executive. 

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

Conclusion

That’s it! In this tutorial, we have covered how to use the CREATE TABLE statement in SQL. This SQL statement allows you to create a new table, which will then store the data you need to hold in your database. We have also explored how to use CREATE TABLE to create a table using the same structure as another table.

Now we have a database with tables we’re ready to start inserting data into our database and running queries.