SQL: CREATE TABLE Statement

This SQL tutorial explains 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 CREATE TABLE statement in SQL is:

CREATE TABLE table_name ( column1 datatype [ NULL | NOT NULL ], column2 datatype [ NULL | NOT NULL ], . );

Parameters or Arguments

table_name The name of the table that you wish to create. column1, column2 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 int NOT NULL, supplier_name char(50) NOT NULL, contact_name char(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 char datatype (50 maximum characters in length) and also can not contain null values.
• The third column is called contact_name which is a char 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 int NOT NULL, supplier_name char(50) NOT NULL, contact_name char(50), CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id) );

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 int NOT NULL, customer_name char(50) NOT NULL, address char(50), city char(50), state char(25), zip_code char(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 int NOT NULL, customer_name char(50) NOT NULL, address char(50), city char(50), state char(25), zip_code char(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 int NOT NULL, department_name char(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 int NOT NULL, employee_name char(50) NOT NULL, department_id int, salary int, CONSTRAINT employees_pk PRIMARY KEY (employee_number), CONSTRAINT fk_departments FOREIGN KEY (department_id) REFERENCES departments(department_id) );