SQL Create Table

SQL (Structured Query Language) includes plenty of commands to manage relational databases. They are then differentiated into several categories, namely TCL, DCL, DML, and DDL. Creating new tables is required for the purposes of data analysis in SQL.

You need to know how to create a new table in SQL is you need to store SQL query output, or you require pulling a new source of data into your analysis for storing your cleaned and transformed data without removing the original data sets.

In this tutorial, we will learn about creating a table in SQL, the syntax of CREATE TABLE, and what are various column parameters to set, and so on.

SQL Data Types

Before moving to create a table, you should know the SQL data types. This is important because you need to define each of these types in the column when you create a table. For example, if you are setting a new column with text, you will not be able to include dates or numbers. It is the reason you should be aware fo the data types in SQL. Here are some of them.

Text:

It’s the general data type in SQL. It includes a mix of numbers, letters, and other characters. It accepts only alphabetical letters.

For example, D2G3 accept Run 189.87.897.42

Integer:

The integer specifies the whole number without any fractional part.

For example, 28454545, 385, 4

Boolean:

It specifies a binary value. The binary value can be TRUE or FALSE.

Time:

You can take any format of this.

For example, 12:42:00

Decimal:

It’s a number that includes the fractional part.

For example, 3.8927 984.7483974, 1.34

Date:

You can take your own format.

For example, 2019-12-20

Timestamp:

its the combination of time and date.

For example, 2019-12-20 12:42:00

How to make a Creat Table statement?

Let’s learn how to create a table using the CREATE TABLE. Let’s check out the basic syntax of creating a table with the CREATE TABLE SQL statement.

CREATE TABLE new_table_name
{
first_column   first_column_data_type,
second_column   second_column_data_type,
third_column   third_column_data_type,
fourth_column   fourth_column_data_type,
….
last_column   last_column_data_type
};

CREATE TABLE should always be at the beginning of every SQL Statement as its one of the basic SQL keywords.

• New_table_name is identified as your created table name. You need to make sure that the table name is meaningful and simple. Also, create the table name in the lower case.
• The first_column, second_column, third_column, last_column, etc. are the new column names in the created new table. Name sure your new_table_names are meaningful, simple, and in the lowercase.
• One important trick is that you need to mention the data type for every column.
• You can check out the first_column_data type after the first_column. It can generally be a text. In case you need to perform some mathematical calculations or you need to fill them with numbers, then you need to make use of the Decima, Integer, or other numeric data types.
• You can make use of the Timestamp, Time, Date, or any other Time/Date data types in case you need to create using any DateTime functions.

Let me explain the syntax with an example.

Create TABLE Employees(EmployeeID int, 
Employeename varchar(255), 
Sex varchar(255),
Employeeparentname varchar(255),
Employee address varchar(255),
Employee phonenumber int);

Output

Now the next step is to create value into the created table with the help of the Insert query. Here another question arises, what if you need to create a table using an already existing table? How to do it?

Let’s look for the procedure below.

How to create a table using another table?

You need to make use of the following syntax if you need to develop a table from an existing table.

CREATE TABLE newtablename AS
SELECT column1,column2,column3,....columnN
FROM existingtablename
WHERE …….;

In this, you are going to select the needed columns from the present table, but it should be based on a condition. Displaying the condition is not a mandatory option. 

Example

CREATE TABLE sampletable AS
SELECT employeeID, employeename
FROM employees;

The output for the above syntax will be

The new table created has the same definition of columns as the older version. In case your existing table has any values, then the new table will be filled with the existing column values.

About the Syntax:

The syntax of creating a table is important. Here are some clues.

• The information of the column should go between the parentheses ‘(‘ once after the CREATE TABLE new_table_name.
• You need to make use of the Commas ‘,’ to separate the columns,
• It’s good to use tabs between the data types and column names. Similarly, use line breaks between the columns. Your line breaks and indentations will never affect the SQL statement execution, but it makes reading them easier.
• Make sure to add a semicolon (;) at the end of the SQL statement.

I hope the above tutorial on CREATE TABLE provided a detailed understanding of the concept. If you have any queries on creating a table using the CREATE TABLE SQL statement, let us know through the comment section below.

Related Blogs

1. SQL Joins
2. Schema in SQL
3. Decode in SQL