Share this page:

SQL: CREATE TABLE Statement

Learn how to use the SQL CREATE TABLE statement with syntax, examples, and practice exercises.

Description

The SQL CREATE TABLE statement allows you to create and define a table.

Syntax

The syntax for the SQL CREATE TABLE statement is:

CREATE TABLE table_name
( 
  column1 datatype null/not null,
  column2 datatype null/not null,
  ...
);

Parameters or Arguments

table_name is the name of the table that you wish to create.

column1, column2 are the columns that you wish to create in the table. Each column must have a datatype. The column should either be defined as "null" or "not null" and if this value is left blank, the database assumes "null" as the default.

Example

Let's look at a SQL CREATE TABLE example.

CREATE TABLE suppliers
( supplier_id number(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50)
);

This SQL CREATE TABLE example creates a table called suppliers which has 3 columns.

  • The first column is called supplier_id which is created as a number datatype (maximum 10 digits in length) and can not contain null values.
  • The second column is called supplier_name which is a varchar2 datatype (50 maximum characters in length) and also can not contain null values.
  • The third column is called contact_name which is a varchar2 datatype but can contain null values.

Now the only problem with this SQL CREATE TABLE statement is that you have not defined a primary key for the table. We could modify this SQL CREATE TABLE statement and define the supplier_id as the primary key as follows:

CREATE TABLE suppliers
( supplier_id number(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
);

Learn about primary keys.

Learn about foreign keys.

Practice Exercise #1:

Create a SQL table called customers that stores customer ID, name, and address information.

Solution for Practice Exercise #1:

The SQL CREATE TABLE statement for the customers table is:

CREATE TABLE customers
( customer_id number(10) not null,
  customer_name varchar2(50) not null,
  address varchar2(50),
  city varchar2(50),
  state varchar2(25),
  zip_code varchar2(10)
);

Practice Exercise #2:

Create a SQL table called customers that stores customer ID, name, and address information.

But this time, the customer ID should be the primary key for the table.

Solution for Practice Exercise #2:

The SQL CREATE TABLE statement for the customers table is:

CREATE TABLE customers
( customer_id number(10) not null,
  customer_name varchar2(50) not null,
  address varchar2(50),
  city varchar2(50),
  state varchar2(25),
  zip_code varchar2(10),
  CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

Practice Exercise #3:

Based on the departments table below, create a SQL table called employees that stores employee number, employee name, department, and salary information. The primary key for the employees table should be the employee number. Create a foreign key on the employees table that references the departments table based on the department_id field.

CREATE TABLE departments
( department_id number(10) not null,
  department_name varchar2(50) not null,
  CONSTRAINT departments_pk PRIMARY KEY (department_id)
);

Solution for Practice Exercise #3:

The SQL CREATE TABLE statement for the employees table is:

CREATE TABLE employees
( employee_number number(10) not null,
  employee_name varchar2(50) not null,
  department_id number(10),
  salary number(6),
  CONSTRAINT employees_pk PRIMARY KEY (employee_number),
  CONSTRAINT fk_departments
    FOREIGN KEY (department_id)
    REFERENCES departments(department_id)
);