SQL CREATE TABLE – The Ultimate Guide to SQL CREATE TABLE Statement

Let’s deep dive into SQL CREATE TABLE the intricacies of this important command, learn about its syntax, and run through an example of how to use it.

What is SQL CREATE TABLE?

The CREATE TABLE statement in SQL is a Data Definition Language (DDL) operation that is used to create a new table in a database. Tables are an essential component of databases as they hold all the data. When a table is created, the name of the table, names of columns, and the type of data that can be stored in the columns are defined.

SQL CREATE TABLE Syntax

The basic syntax for creating a table in SQL is as follows:

CREATE TABLE table_name (
    column1 data_type constraints,
    column2 data_type constraints,
    column3 data_type constraints,
    ....
);

• table_name is the name you wish to give to the table.

• column1, column2, …, columnN are the names of the columns of the table.

• data_type is the type of data that you can store in a column. It could be integer, varchar, date, etc.

• constraints are the rules you can apply on the column. For example, NOT NULL means that the column must always have a value.

Note that when defining a table, you can specify one or more columns to be the Primary Key for the table, using the PRIMARY KEY constraint. A Primary Key uniquely identifies each record in the table.

Creating a Table: A Practical Example

Let’s create a simple Employees table to store basic employee data. This table will contain columns for EmployeeID, FirstName, LastName, Email, PhoneNumber, and HireDate.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(100),
    LastName VARCHAR(100),
    Email VARCHAR(255),
    PhoneNumber VARCHAR(15),
    HireDate DATE
);

In this example,

• EmployeeID is an integer and is designated as the primary key.
• FirstName, LastName, Email, and PhoneNumber are variable character fields. The numbers in the parentheses define the maximum length of the string.
• HireDate is a date type that will contain the date an employee was hired.

The CREATE TABLE statement doesn’t return any result but if everything runs without errors, it creates a new table in your database according to your specifications.

Conclusion

Mastering the CREATE TABLE statement is an important first step in managing databases with SQL. While this command can get quite complex when you start considering different data types, constraints, and relationships, the basics remain the same: you’re defining a structured way to store data.