Monday , September 23 2019
Home / SQL / CREATE TABLE and TRUNCATE TABLE Statement

CREATE TABLE and TRUNCATE TABLE Statement

CREATE TABLE statement:

What is TABLE?

TABLE is a database object. It is used to store data in database. The TABLE always stores user data and we can query for a specific data in the TABLE when required. Suppose Business data, Student and Teacher data, Employees of an organization data can be stored in TABLE.

CREATE TABLE statement

The CREATE TABLE statement is used to create a TABLE definition in the database.

SYNTAX

The Syntax of a Simple Table:

CREATE  TABLE    schema_name . table_name

(

column_name_1     datatype(length),

column_name_2     datatype(length),

column_name_3     datatype(length),

column_name_4     datatype(length)

);

Example:

CREATE TABLE   HR.Employee

(

Employee_Id                 varchar2(10),

Employee_Name          varchar2(100),

Address                           varchar2(200),

Join_Date                      date

);

Here, a table named Employee has been created into HR schema by CREATE TABLE statement. The Employee table is containing four columns named Employee_Id, Employee_Name, Address, Join_Date and these columns are also bearing           varchar2(10), varchar2(100), varchar2(200), date datatypes with length respectively. And every column has been separated by comma ( , ) but after last column, no comma is required. Also the columns will be contained within Start and Ending parenthesis ( ). A semicolon (;) will be used after the Ending parenthesis to terminate the CREATE TABLE statement.

The Syntax of a Table which will be created under a user defined TABLESPACE:

CREATE   TABLE      schema_name . table_name

(

column_name_1      datatype(length),

column_name_2      datatype(length),

column_name_3      datatype(length),

column_name_4      datatype(length)

) TABLESPACE    tablespace_name;

Example:

CREATE TABLE   HR.Employee

(

Employee_Id                 varchar2(10),

Employee_Name          varchar2(100),

Address                           varchar2(200),

Join_Date                      date

) TABLESPACE     tbs_permanent;

Here the Employee table has been created under the tablespace named tbs_permanent and semicolon (;) will be used after the tablespace name.

Note: If you do not specify the schema name then the table will be created into the user schema which user is currently logged in. And if you do not specify tablespace name during table creation then the table will be stored under the default tablespace of the user which user is currently logged in.

PRIVILEGE required for Table creation:

  • To create a new table into the user schema that is currently logged in, the user should have CREATE TABLE system privilege.
  • To create a new table into another user’s schema, the user should have CREATE ANY TABLE system privilege.

TRUNCATE TABLE statement:

The TRUCATE TABLE statement works as same as DELETE statement without WHERE clause. It will be used to remove all rows or records from the database table in oracle. It will also deallocate the spaces already used by removed records to store new records. The TRUNCATE command is much faster than DELETE statement. It will save time of dropping and re-creating a table.

Note: If you delete all rows of a table by TRUNCATE TABLE statement, the rows that have been removed cannot be rolled back.

Syntax:

TRUNCATE    TABLE    table_name;

table_name is the name of the table that will be truncated.

Note: Before truncating table, insert minimum two rows into the table which you want to truncate.

Example:

TRUNCATE TABLE Employee;

In the above example, the EMPLOYEE table has been truncated. In result, all rows of EMPLOYYE table have been removed and also deallocate spaces to insert new rows.

Prerequisites:

If you want to truncate a table, the table should be belonging to your own schema and also should have DROP ANY TABLE system privilege.

Check Also

Unique Constraint

Definition and Set UNIQUE constraint by CREATE TABLE statement in Oracle

Definition of UNIQUE constraint: The UNIQUE constraint is used to ensure unique or distinct values …

Set NOT NULL constraint by ALTER TABLE statement in Oracle

You can set NOT NULL constraint on existing table column by ALTER TABLE statement. Practice: …

Leave a Reply

Your email address will not be published. Required fields are marked *