SQL Create Table: Basics of the Best Database Language

What Is SQL Create Table?

The “CREATE TABLE” command is a part of the Data Definition Language, a sub-language of SQL that enables the creation and modification of the structures of database objects. This command allows us to create and define the structure of tables.

The first step in working with data in SQL involves the creation of tables in the database. Each table consists of columns and their data types. All of this can be defined using the SQL create table command. You can create a table in one of the existing databases or your database that you can create using the “CREATE DATABASE” command.

Let’s get more insight into the syntax of the SQL create table command.

Syntax for Creating a Table in SQL

• The “CREATE TABLE” statement lets the database system know that you wish to create a table
• This statement is followed by the name you want to define your table with

The table name should contain only alphanumeric characters and underscores and should start with an alphabet or underscore.

For example, “ _first_table_1” and “first_table” are valid table names, whereas “1_table” and “table_@1” are not valid names.                                           

• Then, inside brackets, you can specify the details of each column you want to add in the table

Each column has a name and data type. The naming of columns follows the same rules as the naming of tables. Each column can contain data of one of the following data types:

Each table definition can contain several columns, and commas between them separate each of these column definitions. 

For example, if we want to create a table about a school’s students containing attributes like student ID or roll number, student’s name, and the grade they’re in, we’ll use the following query:

As you can see, we can also add some constraints like “NOT NULL” and “Primary Key”  to your table’s columns. Let’s see what these are.

Want a Top Software Development Job? Start Here!

Full Stack Developer - MERN StackExplore Program

Not Null Constraint in SQL

By default, if you don’t specify any conditions, the columns in a table in SQL can be left blank or contain missing values. These are known as NULL values. If you don’t want any column in your table to be left blank, you can add the “NOT NULL” constraint to it during in the following way:

In the above table, column2 and column3 will accept null values, but column1 will not.

For example, in our table “Student,” the NOT NULL constraint has been defined on the column “Name” as we want all the records to accept explicit values. Column “Class” can contain null values.

Now that you know the concept of null values, let’s see what Primary Key is and what function it serves.

What Is a Primary Key?

Primary key is added to a table to identify each unique row or record. If a column is defined as the “Primary Key” it cannot contain null values. A table can have only one Primary Key, which can consist of single or multiple columns. 

If you have defined Primary Key on any column, then two records of that column cannot have the same value. Each entry of that column has to be a unique value, so you need to choose the Primary Key accordingly. 

For example, in our “Student” table, Student ID should be set as the primary key as every student has a unique ID number. 

The syntax to define one attribute as the primary key is as follows:

In the above table, column1 is the Primary Key.

Use the following syntax to define multiple attributes as the Primary Key.

Column1 and column2 together form the Primary Key in the above table.

For example, if our table “Student” had an attribute “Address” in place of the attribute “StudentID”, Name and Address together will form the Primary Key.

Now, let’s create our table using the commands and constraints discussed above.

Creating a Table Using the SQL Create Table Command

You can create your table in one of the existing databases of your database system, or in a database you create yourself.

• We’ll create our own database using the following command:

• In this database, we’ll create a table Employee_details using the following query:

This will result in the following table:

• Using the “DESC table_name” command, you can check the details of the table you created.

Here, we have set the “EmployeeID” attribute as the primary key as each employee has a unique identification number. All the other attributes like name, address, and salary, can have the same value for multiple records and cannot be selected as the Primary Key.

The most suitable data type should be selected for each column as it allows the correct insertion of values and a neat database. 

For example, in the above table, the attributes “Address” and “Name” cannot have the data type “int” as they contain different types of alphanumeric characters and special symbols. “decimal” is the most suitable data type for the “salary” attribute as salary is not always an integer and can contain decimal point numbers. 

We have put a “NOT NULL” constraint on the “Name” attribute as it is an essential attribute of this table—we don’t want any record to be empty in this column.

There is another way of creating a table. Let’s see how that works.

Learn From The Best Mentors in the Industry!

Automation Testing Masters ProgramExplore Program

Creating a Table Using Another Table

• Sometimes, when you work on data, things can go wrong. In such cases, it is wise to create a backup of the data you’re working on.

You can achieve this by creating a duplicate of the table to work on. In this way, if anything goes wrong, you can always recreate the table from the original table, both structure- and data-wise.

The syntax for creating a duplicate table is as follows:

To create the duplicate of our “Employee_details” table, we’ll use the following command: 

• To see the details of this duplicate table, we’ll use the “DESC” command which will result in the following:

As we can see, all the details except for the Primary Key constraint is present in the duplicate table. This is because this command does not copy the Primary Key constraint.

• Sometimes, we require a subset of the columns of the original table. 

The syntax of the query we can use to achieve this is as follows:

For example, if we need a table containing all the salary details from our “Employee_details” table, the columns “EmployeeID” and “Salary” would suffice. To create this table, we’ll use the following query:

• To check the details of this table, we’ll use the “DESC” command which will result in the following:

You should always delete a table when it’s not required anymore, as it takes up valuable memory.

Dropping Table

You can delete a table by using the “DROP” command from the Data Definition Language, which is the sublanguage of SQL that enables you to create and modify the structure of database objects.

The syntax of this command is as follows:

You need to be careful before dropping a table as you won’t be able to retrieve any data from it once dropped.

For example, we don’t require the table “Employee_salary” anymore, so we’ll use the following command to delete it.

The table has now been deleted. You can check this using the “DESC” command.

As you can see, the table has been deleted permanently from our database.

Learn 15+ In-Demand Tools and Skills!

Automation Testing Masters ProgramExplore Program

Next Steps

In conclusion, tables are the backbone of any database. Without learning how to create and manipulate them, you cannot move forward with database management.

Now that you know how to create and drop a table, the next step in your SQL journey would be to learn how to populate tables so that you can manipulate and query that data. If you liked this article and want to get certified and become an expert in SQL, you can check out Simplilearn’s Business Analyst Master’s Program as it covers SQL in great depth.