Skip to main content

Creating Tables

In SQL, a table is a collection of related data held in a structured format within a database. It consists of columns and rows where each column holds a specific attribute and each row represents a single record. In this tutorial, we'll learn how to create a table in SQL.

What you'll learn:​

  • Understanding SQL CREATE TABLE statement
  • Creating a simple table
  • Creating a table with constraints
  • Creating a table from another table

Understanding SQL CREATE TABLE statement​

The CREATE TABLE statement is used to create a table in SQL. The basic syntax is as follows:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

Where table_name is the name of the table, column1, column2,... are the names of the columns and datatype indicates the type of data the column can hold (e.g. varchar, integer, date etc.).

Creating a simple table​

Let's create a simple table named Students with three columns: ID, Name and Grade.

CREATE TABLE Students (
    ID int,
    Name varchar(255),
    Grade varchar(255)
);

In this example, the ID column will hold integers, and the Name and Grade columns will hold strings of characters, where the maximum length is 255.

Creating a table with constraints​

Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the table's data.

Here are some of the most commonly used constraints:

  • NOT NULL - Ensures that a column cannot have a NULL value.
  • UNIQUE - Ensures that all values in a column are different.
  • PRIMARY KEY - Uniquely identified each records in a table.
  • FOREIGN KEY - Constraint is used to prevent actions that would destroy links between tables.

Let's modify our Students table to include some constraints:

CREATE TABLE Students (
    ID int NOT NULL,
    Name varchar(255) NOT NULL,
    Grade varchar(255),
    PRIMARY KEY (ID)
);

In this example, the ID and Name columns cannot be NULL and each ID must be unique because it is the PRIMARY KEY.

Creating a table from another table​

You can also create a new table based on the columns of an existing table using the CREATE TABLE AS statement. Here is the basic syntax:

CREATE TABLE new_table AS
SELECT column1, column2,...
FROM existing_table
WHERE condition;

For example, let's create a new table named GraduatedStudents that contains all students from the Students table that have a grade of 'A':

CREATE TABLE GraduatedStudents AS
SELECT ID, Name, Grade
FROM Students
WHERE Grade = 'A';

This concludes our tutorial on creating tables in SQL. Practice creating your own tables and try adding different constraints to understand their functionality. Remember, the more you practice, the more comfortable you'll become with SQL. Happy learning!