SQL Create Table: Basics of the Best Database Language

To start working with SQL, you need to be able to store data using tables. Tables provide a systematic way of storing data in any database, using different columns and data types that resemble all kinds of real-world data. The SQL Create Table command is an essential command that enables the formation of tables, without which you won’t be able to move forward in your journey to become an expert in SQL.

In this article we’ll be learning how to create tables by the following docket:

  • What is SQL Create Table?
  • Syntax for Creating a Table in SQL
  • Not Null Constraint in SQL
  • What Is a Primary Key?
  • Creating a Table Using the SQL Create Table command
  • Creating a Table Using Another Table
  • Dropping Table

Business Analyst Master's Program

Gain expertise in Business analytics toolsExplore Program
Business Analyst Master's Program

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

CREATE TABLE table_name (

         column1 data type,

         column2 data type,

         …

         column n data type

);

  • 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.                                           

invalid_name_starts_with_number

invalid_name_contains_special_character

  • 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:

Categories_of_data_types

Data Type 

Examples

Numeric

int, decimal, float

Character String

char, varchar, text

Unicode Character String

nchar, nvarchar(max), ntext

Binary

varbinary, varbinary(max), image

Date and Time

datetime, timestamp, year

Miscellaneous

xml, json, clob

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:

Table_example

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.

Business Intelligence Career Guide

A Complete Guide to Becoming a Business AnalystDownload Now
Business Intelligence Career Guide

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:

CREATE TABLE table_name (

         column1 data type NOT NULL,

         column2 data type, 

         column3 data type

);

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:

CREATE TABLE table_name ( 

         column1 data type Primary Key,

         column2 data type

);

In the above table, column1 is the Primary Key.

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

CREATE TABLE table_name (

        column1 data type,

        column2 data type,

        column3 data type,

        Primary Key(column1, column2)

);

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.

primary_key-SQL_create_table

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:

CREATE DATABASE our_database;

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

Create_table_command-SQL_create

This will result in the following table:

EmployeeID

Name

Address

Salary

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

desc_command-SQL_create_table

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.

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:

CREATE TABLE table_name

as

SELECT * FROM original_table_name;

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

Create_table_as_command-SQL_create_table

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

Create_table_as_command_details

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:

CREATE TABLE table_name

as

SELECT required_column_1, required_column_2…,required_column_n

FROM original_table_name;

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:

create_table_as_subset

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

subset_table_details

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:

DROP TABLE tablename;

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.

-SQL_Create_table

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

Check_drop_command

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

Gain expertise in the latest Business analytics tools and techniques with the Post Graduate Program in Business Analysis. Enroll now!

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.

About the Author

SimplilearnSimplilearn

Simplilearn is one of the world’s leading providers of online training for Digital Marketing, Cloud Computing, Project Management, Data Science, IT, Software Development, and many other emerging technologies.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.