TABLE

Create Table in oracle

In Oracle, produce TABLE statement is employed to make a brand new table within the information.

To create a table, you have got to call that table and outline its columns and datatype for every column.

Syntax:

Parameters used in syntax

• table_name: It specifies the name of the table that you would like to make.
• column1, column2 : It specifies the columns that you would like to feature within the table. each column should have a datatype. each column ought to either be outlined as "NULL" or "NOT NULL". within the case, the worth is left blank; it's treated as "NULL" as default.

The produce TABLE line tells the information to form a brand new table, named as "mytable". The table is outlined as having 2 columns in it. when the produce TABLE line, the columns within the table square measure outlined, with parentheses insertion the column definition code.

The first defined column, named "column1", is defined as a VARCHAR(30) type of column, meaning it can store alphanumeric data and allows for a maximum of 30 characters to be stored in that column per row of data. The second defined column, named "column2", is defined as a DATE type of column, meaning it can store date values.

Alter Table in oracle

ALTER table in an exceedingly Oracle DDL command. It uses to vary the structure of the table.
Using ALTER table command we are able to
1. Add columns
2. Drop columns
3. Modify the column
4. Rename column
5. Add constraints
6. Drop constraints
(  There ar several stuff you may do with ALTER command in Oracle. on top of ar the foremost used and basic ones. )
Examples:-
Consider the below table structure

Name                  sort
—————————————– ——– ————-
CUST_ID      variety
CUST_NAME            VARCHAR2(10)
CUST_ADDRESS         VARCHAR2(50)

1. Add columns

a. Add one column to client table
alter table client add cust_dob date;
or
alter table client add (cust_dob date);
b. Add multiple columns to client table
alter table client add
( cust_zip variety, cust_phone variety, cust_fax variety, cust_email varchar2(20) ) ;

2. Drop columns

a. Dropping single column
alter table client drop column cust_email;
b. Dropping multiple columns in an exceedingly single command
alter table client drop ( cust_phone, cust_fax) ;  — No want of ‘column’ keyword here

3. Modify column

a. ever-changing length of the column
alter table client modify ( cust_name varchar2(50) ) ;
b. ever-changing knowledge style of the column
alter table client modify ( cust_zip varchar2(20) ) ;

4. Rename column

alter table client rename column cust_zip to cust_zipcode ;

5. Add constraints

a. Adding primary key constraints
alter table client add constraint pk_cust_id primary key (cust_id);
It is forever a higher to produce constraint name as a result of it's simple to keep up later.
b. Adding foreign key constraints

SQL> desc Client;
Name                  sort
—————————————– ——– ————
CUST_ID                  variety
CUST_NAME                VARCHAR2(50)
CUST_ADDRESS             VARCHAR2(50)
CUST_ZIPCODE             variety
PROD_ID                  variety
SQL> desc product;
Name                sort
—————————————– ——– ————
PROD_ID               variety
PROD_DESC             VARCHAR2(50)
alter table client add constraint fk_prod_id foreign key (prod_id) references product
( prod_id );
( Here client as Master Table and merchandise as kid table. )
c. Adding alternative constraints like check, unique etc.
alter table client add constraint chk_cust_age check (cust_age >18 ) ;
alter table client add constraint uq_cust_address distinctive (cust_address);

6. Drop constraints

alter table client drop constraint chk_cust_age;
or in generic method
alter table client drop constraint constraint_name;

Drop Table in oracle

The DROP TABLE statement deletes a table. The information renames the table on delete and moves it to recycle bin. The area allotted to table in tablespace isn't free straight off. The born table will be recovered with FLASHBACK TABLE statement.

To drop a table the user should be the owner or should have DROP ANY TABLE privilege.

Dropping a table invalidates dependent objects and removes object privileges on table. The DROP TABLE command drops all rows in table. It additionally drops indexes and triggers created on them regardless of the schema they belong to. If the table is particitioned, native index partitions are born. All storage tables of nested tables and LOBs are born.

If the table is recreated, then the privileges should be re-granted. The indexes, integrity constraints and triggers additionally got to be recreated. Removing rows with TRUNCATE or victimisation produce OR REPLACE TABLE doesn't have any of the on top of effects and thus their use is most well-liked rather than dropping and recreating it.

For external table, the DROP TABLE statement removes solely information from information. the particular knowledge that resides outside the information isn't affected.

If the table being born is an element of a cluster, the table is rapt to recycle bin. If the cluster is born, the table is purged from recycle bin and not recovered with FLASHBACK TABLE.

The following query drops table CUSTOMER.

DROP TABLE CUSTOMER;