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