Tables

Table is a database structure that holds the data organized into rows and columns. Each column has a data type and optionally constraints associated with it. We can perform the following operations with respect to Tables.

• Creating Table – Tables are created with CREATE Table clause
  
  Syntax
CREATE TABLE <table_name>
( <column1> <datatype> [CONSTRAINT] <constraint_def> DEFAULT <Default_Expr>
, <column2> <datatype> [CONSTRAINT] <constraint_def> DEFAULT <Default_Expr>
.
.
.
, <columnn> <datatype> [CONSTRAINT] <constraint_def> DEFAULT <Default_Expr>
)
[ON COMMIT {DELETE | PRESERVE} ROWS]
TABLESPACE <tab_space>;

Note : [ON COMMIT {DELETE | PRESERVE} ROWS] – This is used with Global Temporary Tables.

• ​​Altering Table – Tables are modified with ALTER Table clause. ALTER TABLE statement can perform one of the following
  Add, Modify or Drop a Column
  Add or Drop a Constraint
  Enable or Disable a Constraint
  
  Syntax
ALTER TABLE <table_name>
ADD         <column_name> | [CONSTRAINT] <constraint_def>
MODIFY      <column_name> <datatype>

• Renaming Table – Tables are renamed with RENAME Table clause.
  
  Syntax
  RENAME TABLE <current_table_name>
    TO <new_table_name>;
• Dropping Table – Tables are dropped (i.e., remove the whole structure along with the data in it) using the DROP TABLE statement.
  
  
  Syntax
  DROP TABLE <table_name>;
• Truncating Table – The tables are truncated (i.e. whole data inside the table is deleted) using the TRUNCATE TABLE statement.
  
  
  Syntax
  TRUNCATE TABLE <table_name>;

Tables - Examples


Ex: CREATE keyword
Creating a table and primary key constraint



CREATE TABLE mytable
( id                NUMBER      
, name              VARCHAR2(100)
, CONSTRAINT PK_ID PRIMARY KEY(id,name)
);


Ex: CREATE Keyword
Creating a simple table



CREATE TABLE mytable
( id      NUMBER        CONSTRAINT PK_ID PRIMARY KEY
, name    VARCHAR2(100)
);


Ex: ALTER Keyword
Add a primary key



ALTER TABLE mytable
  ADD CONSTRAINT PK_ID PRIMARY KEY(id,name);


Ex: ALTER Keyword
Modify a column



 ALTER TABLE mytable
MODIFY address varchar2(300);


Ex: ALTER Keyword
DROP a column



ALTER TABLE mytable
 DROP COLUMN address;


Ex: RENAME Keyword
Rename a table to a new name



RENAME mytable
    TO mytable_new;


Ex: TRUNCATE Keyword
Delete all the records from mytable_new



TRUNCATE TABLE mytable_new;


Ex: DROP Keyword
DROP the table



DROP TABLE mytable_new;