Summary -

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