How to create tables using SQL

The Structured Query Language, or SQL is a language used to interact with databases. The SQL create statement is ued to create tables in a database. In this SQL tutorial you will learn how to create tables with SQL. On this page we will look at how a basic table is created. In the next pages you will learn how to create table with primary and foreign keys using SQL.

SQL CREATE statement example

The basic CREATE statement below creates a table with three fields, firstname, lastname and email.

first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
email VARCHAR(50),
birthdate DATE,
average_monthly_visits INTEGER

Each field in the CREATE TABLE statement must have a name and a data type. The field named "first_name" is of data type "varchar". Varchar is a data type that is used to store text of variable length.

The varchar data type allows you to specify a maximum length. For the first_name and last_name fields a maximum length of 30 characters is specified between the parentheses.

The birthdate field has a DATE data type, which is obviously used to store a date.

Common data types

Some common data types are listed below. Note that there can be differences in data types between different relational database systems and database systems may offer more data types than the ones listed here, but these are the ones you will encounter most often when working with databases.

varchar(n) Used for a varying number of characters. The maximum can be set between the parentheses.
character(n) or char(n) Used for a fixed number of characters. The maximum can be set between the parentheses.
integer Used for normal numbers without decimals.
smallint Used for small numbers without decimals.
float, double, decimal Used for numbers with decimals.
date, time, datetime Used respectively for date or time values or values that include both a date and a time.
timestamp A datetime value combined in a single value

Allowing or disallowing NULL values.

So, when defining a field in an SQL create statement you must provide a name and a data type. Let's see what the NOT NULL part in the declaration of the first_name field is all about.

first_name VARCHAR(30) NOT NULL

NULL is a special word in the world of programming and databases. It means "nothing", "empty" or "no value". The NULL keyword in a field declaration like the one above means no value is required and NOT NULL means a value is required.

Specifying a NOT NULL constraint on a database field ensures that the field must always have a value.

If you would try to insert a new customer into the Customer table without a first_name, the database would complain by showing an error and it would not insert the record.

The email field declaration looks a lot like first_name and last_name, but misses the NOT NULL bit. This means that it is not required to provide a value for email when a new record is entered.

Ommitting the NOT NULL part is the same as explicitly allowing "Null values" by writing NULL. The two field declarations below are identical.

email VARCHAR(50)
email VARCHAR(50) NULL

So, in the field declarations we saw in this example, three questions are answerd.

  1. What is the field name? Answer: first_name
  2. What is the field's data type? Answer: Varchar (and the maximum length is 30)
  3. Is a value required for this field? Answer: yes

On the next page we will look into the creation of a primary key in the SQL create statement.