How to Create a Table in SQL

Creating tables in databases is a very helpful skill, and not just for software engineers or database administrators. It allows you to design or change the structure of a database and store data that’s related to each other. In this article, you’ll learn what a database table is, who creates them, and how to use the syntax of the CREATE TABLE command.

What Is a Database Table?

A relational database is built of various structures like tables, views, procedures, and triggers. The main element in a database is the table. It’s a structure that actually stores data. In most cases, a database contains more than one table and the tables are related to each other (i.e. tables store related information, like a person’s name, eye color, and height).

The picture below shows a sample of a database for an online musical equipment shop. (The image comes from the Vertabelo blog; Vertabelo is an online database modeling tool.) Notice that this database has several tables. The lines indicate relationships between tables:

As you see, there are a lot of tables: category, instrument, manufacturer, and more. In a SQL database, every table has a name and stores data in rows and columns. Each row stores details about a specific object.

For example, the table instrument stores information about instruments. Each row is a specific instrument: one row can store information about a white guitar purchased from “Music Shop in Chicago”; another row can store info about a silver drum purchased from “In Tom’s shop”.

The details in each row should be atomic (meaning they can’t get any smaller or more specific), so we have to divide them into columns. Each column stores one kind of data, such as a color, a name, a type of an instrument, or the store in which it was purchased. Below is a sample table with these columns and rows:

The table’s name should describe the data stored in that table. You should also define the data type(s) used in the table columns when you create the table. (You need to have a CREATE TABLE permission to do all this, but that’s another article.)

If you’re interested in learning how to create tables in a database, I recommend the Creating Database Structure track at LearnSQL.com. It’s a series of five interactive courses that will teach you the details of creating tables in SQL.

Who Creates Tables?

You know what a table is, but this raises another question: who creates the tables in a database? Several different groups of people might create database tables.

The first group are database designers. They define the structure of the database: what the tables hold, the relationships between the tables, how the tables store information. They design new databases for specific applications or services.

Database administrators (DBAs) take care of database servers. They maintain, create, and modify databases. Creating new tables is part of their job.

Software engineers and application developers also create database tables. When creating a new application, they often have to design a database for it.

Data analysts are another group that sometimes creates tables. They use databases to store the data they’re analyzing and may use tables to hold the results of their analysis.

Of course, designing databases and creating tables can be helpful for anyone who works with data or uses databases.

How to Create a Table

Creating a table in a database is very simple. You just need to use the standard SQL syntax for the CREATE TABLE command:

CREATE TABLE table_name
( 
column1 data_type, 
column2 data_type,
 … 
);

Let’s dig into what’s going on here. First you put the CREATE TABLE keyword, followed by the table name. This name should not be an SQL keyword (like CREATE, LIKE, or NULL). It must be unique within the database – if one table is named person, other tables in the same database cannot have this name – and describe the data being stored in the table. You can read more about naming tables in Naming Conventions in Database Modeling and The 11 Worst Database Naming Conventions I’ve Seen in Real Life.

After the name, you add a pair of parentheses. Inside the parentheses, you list the names of the columns with their data types. A data type defines what kind of values (numbers, text, dates, times, etc.) you can store in a column. The most popular data types are:

• INTEGER/INT stores whole numbers like -2567, 0 , 1, and 67.
• VARCHAR stores text data. You can specify the maximum number of characters like this: VARCHAR(n). For example, varchar(4) can store the texts ‘Lisa’, ‘Os’ or ‘Gary’.
• CHAR is similar, but instead of setting the max length, you set the absolute length. For example, CHAR(4) will store ‘Os’ as ‘Os ‘. Note the two extra spaces, which make the length up to 4.
• FLOAT stores floating-point numbers, such as -23.789, 23.5, and 78.0.
• DECIMAL is similar to float in that it stores decimal-point numbers. However, you can indicate the maximum total number of digits (p) and the number of digits after the decimal (s) like this: DECIMAL(p,s). In other words, decimal(4,2) can store 12.56, 1.56, 70. Unlike FLOAT, DECIMAL offers precise calculations. Use it to store money values.
• TIME stores time data, e.g. ‘12:34:50’.
• DATE stores date data, e.g.’2020-09-09’.
• DATETIME stores both date and time data in a single field, e.g. ‘2020-03-30 12:34:50’.

This basic CREATE TABLE syntax is the same for most SQL engines; you can use it in Oracle, SQL Server, MySQL, or PostgreSQL.

Creating a Simple Database Table

Let’s create a table called customer that stores details about a company’s customers. Each row will contain data about one specific customer. The table should have the columns ID, last_name, first_name, and registration_date. The columns defined in the table store atomic data about each customer: their identification number, last name, first name, and the date when they registered as a customer.

Here’s the command we’d use:

CREATE TABLE  customer
( 
ID int, 
last_name varchar(80),
first_name varchar(80),
registration_date date
);

We start with the CREATE TABLE keyword, after which we give the name of the new table: customer. Next, in the parentheses, we give the column names and data types. The first column is ID, which stores integer values like 1,2,3. The second and third columns, last_name and first_name, are string values; we’ll use varchar(80) for them. Remember, the value in brackets denotes the maximum number of characters in the string. Finally, the column registration_date stores the date when the person was registered.

Data types control what kind of information can be entered in a column; you can’t enter letters or symbols in an INT column like ID. You can find more about data types in the courses The Basics of Creating Tables in SQL and Data Types in SQL.

Defining Columns Without Empty/Repeated Values

The CREATE TABLE statement shown in the last section allows users to insert non-unique values or empty values (NULLs) into a field. For example, you can have the same ID value for more than one customer. We can change this behavior by creating a table with constraints: we’ll add constraints to ID to prevent NULLs and non-unique values; we’ll also add constraints in the columns first_name and last_name to prevent NULLs in these columns. Have a look:

CREATE TABLE customer 
( 
ID int NOT NULL UNIQUE, 
last_name varchar(80) NOT NULL,
first_name varchar(80) NOT NULL,
registration_date date
);

SQL offers the NOT NULL keyword, which when added to the column definition doesn’t allow empty values (NULL) when inserting or modifying a row. In our example, we cannot omit (or insert a NULL) into the columns first_name, last_name or ID. They must have a value.

Another valuable keyword is UNIQUE. By adding this keyword, you can make sure that all values in this column are different in every row. In our example, the column ID stores unique values that identify each customer. You can learn more about different kinds of constraints in the course SQL Constraints.

Creating a Table from Data Stored in Another Table

We can create a new table without defining columns: the process is based on data and columns in other tables.

Use this method if you want to create tables and insert data stored in specific columns in another table. Here’s the syntax:

CREATE TABLE new_table_name
  SELECT col1, col2, … 
  FROM existing_table_name ;

First we provide the CREATE TABLE keyword and the new table name. Next, we use the SELECT command. This allows you to specify the columns and table(s) from which you copy data to the new table. The names and data types of these columns will automatically be defined in the new table.

Below, we create the table history_instrument using the columns ID, name, color, and type from the instrument table:

CREATE TABLE history_instrument   
  SELECT ID, name, type  
  FROM instrument ;

Notice that the new table doesn’t store all the columns from the table instrument.

Learn More About Database Structures

If you’re interested in building database structures like tables, there is a lot of material online that you can use. On LearnSQL.com you’ll find articles like Understanding Numerical Data Types in SQL and TRUNCATE TABLE vs. DELETE vs. DROP TABLE: Removing Tables and Data in SQL to enhance your SQL knowledge. You can also complete the track Creating Database Structure on LearnSQL.com for a comprehensive and interactive overview.