In this topic, we described about the below sections -
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.
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.
Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.
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).
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.
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."