SQL Create Table

Summary -

In this topic, we described about the below sections -

Create Table

What is table?

Table is an organized collection of data contained in rows and columns. Instead of having one table with large data, database can be created with multiple tables and relate them with the keys. So, the retrieving of the data could be easier and quick when compared to the table with large amount of data.

Generally, a table can be created to store the data. CREATE STATEMENT is used to create new table in database.

Syntax -

CREATE TABLE [IF EXISTS] table_name (
   column1 datatype,
   column2 datatype,
   column3 datatype,
   ……,
   columnN datatype,
 );

table_name – Specifies the name of the new table.
column1, column2, column3, ….., columnN – Specifies the name of the columns.
datatype – Specifies the type of column.

If the optional IF NOT EXISTS keywords includes, the database server takes no action if a table exists with the specified name.

Example-

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

employee_details -

eid	ename	designation	manager_id	date_of_hire	salary	dept_id
001	Employee1	Director		2019-07-11	45000.00	1000
002	Employee2	Director		2019-07-11	40000.00	2000
003	Employee3	Manager	Employee1	2019-07-11	27000.00	1000
004	Employee4	Manager	Employee2	2019-10-08	25000.00	2000
005	Employee5	Analyst	Employee3	2019-07-11	20000.00	1000
006	Employee6	Analyst	Employee3	2019-10-08	18000.00	1000
007	Employee7	Clerk	Employee3	2019-07-11	15000.00	1000
008	Employee8	Salesman	Employee4	2019-09-09	14000.00	2000
009	Employee9	Salesman	Employee4	2019-10-08	13000.00	2000

Syntax for creating above table-

CREATE TABLE employee_details (
  emp_id int,
  emp_name varchar(255),
  designation varchar(255),
  manager_id varchar(255),
  manager_id varchar(255),
  date_of_hire date,
  salary decimal(7,2),
  dept_id int  
);

Whenever, user execute the above query the table gets created in database. Before that, user should have admin privilege to create a table. Now, employee_details table created with empty rows.

User should follow some rules while creating table -

Table name should be simple, meaningful and it is related to the data within the table.
Table name should be unique. Table name would not match with any other existing table in database.
Keywords ( for example - SELECT, CREATE, INSERT etc,.) should not be used as name of the table or column.
Each column should separate by comma "," in the table creation.
Column names should be unique and should match with other column in the same table.
Use the suitable datatype to the respective column.
Table and column name length are different for different databases. So, choose the names as per the limits of current database in use. (for example - SQL Server allows the table and column names up to 128 characters only).

Verifying table

Once the table created successfully, user should verify the database table is accessible or not for all the users. To do it, user has to query the table.

To verify the employee_details table, use the below query -

SELECT * FROM employee_details;

If the empty table displays with its structure like below, then the table created successfully.

emp_id	emp_name	designation	manager_id	date_of_hire	salary	dept_id

If any error occurred, then the table not created successfully, and user should have to contact the admin team.

What happen if try to create a table in database with the name that already existing?

System will not throw any error if "IF NOT EXISTS" clause used along with the CREATE TABLE query.

If "IF NOT EXISTS" clause not used, system throws below error -

Error: "A Table with the specified name already exists.Please specify a unique name."