Create and Alter Tables

Before create a tables please follow the below rules not for table only but for all database objects.

1-     All names begin with letters and contain no more than 30 characters.

2-     Names contain only the characters A-Z,a-z,0-9,underscore (_),$,#.

3-     You can’t duplicate the name of another object in the same schema.

4-     Name can’t an oracle reserved word.

To create table a user must have the create table privilege.

Steps to create table

1-   Define a table name using  CREATE TABLE statement

2-   Define a column names, datatype, and length.

If you want define multiple columns, separate each column with a comma.

3-   Define constraints for the respective columns.

For example.

CREATE TABLE TRAINING

(T_NO  NUMBER(4)  CONSTRAINT TRAINING_T_NO_PK PRIMARY_KEY,T_NAME  VARACHAR2(25) CONSTRAINT TRAINING_T_NAME _NN NOT_NULL);

Result

TABLE CREATED

---------------------------------------------

Create table based on another

You can create table based on another table using AS subquery clause within the create table statement.

While create a table based on another table the second table maybe contain data this useful when creating a table to replace another similar table.

If you want to create table based on another without data you need to use a condition in the where keywords. This condition must Non-matching any data in second table

Like where 1=0;

For example

Assume you want to select rows from EMP table and insert them in the new table

Type

Create table NEWEMP as select

EMPNO,ENAME,DEPTNO,SAL

From EMP;

Result

TABLE CREATED

If you want display data in a new table

Type

SELECT * FROM NEWEMP;

  EMPNO ENAME          DEPTNO        SAL

------- ---------- ---------- ----------

   7369 SMITH               20                      800

   7499 ALLEN              30                     1600

   7521 WARD               30                     1250

   7566 JONES               20                     2975

   7654 MARTIN           30                     1250

   7698 BLAKE             30                      2850

   7782 CLARK             10                      2450

   7788 SCOTT              20                     3000

   7839 KING                 10                      5000

   7844 TURNER           30                      1500

   7876 ADAMS             20                      1100

  EMPNO ENAME          DEPTNO        SAL

------- ---------- ---------- ----------

 7900 JAMES              30                   950

 7902 FORD                20                   3000

 7934 MILLER            10                   1300

Assume that you want create structure only without data

Type

CREATE TABLE NEWEMP2 AS

SELECT * FROM EMP

WHERE 1=0;

Since   1 not equal 0 then the new table created without data

If you want show the result

Type

DESCRIBE NEWEMP2;

Name                                      Null?    Type

 ----------------------------------------- -------- -------------

 EMPNO                                     NOT NULL NUMBER (4)

 ENAME                                                           VARCHAR2 (10)

 JOB                                                                  VARCHAR2 (9)

 MGR                                                                 NUMBER (4)

 HIREDATE                                                      DATE

 SAL                                                                   NUMBER (7,2)

 COMM                                                              NUMBER (7,2)

 DEPTNO                                                           NUMBER (2)
--------------------------------------------------------------------------------------------------

Alter tables

Some time you need to add column, modify column, redfine labels

Add column

It used to add column to an existing table

Suppose that you want add a new column for NEWEMP table this column named ABC

Alter Table NEWEMP

Add(ABC varchar2(20));

----------------

Dropping column

If you want drop colum

Type

Alter table NEWEMP

Drop ABC;

You can also set column as unused by

Typing

Alter table NEWEMP

SET UNUSED ABC;

YOU CAN DROP ALL UNUSED COLUMN

ALTER TABLE NEWEMP

DROUP UNUSED COLUMN;

-----------------------------------------------------------------

MODIFYING COLUMN

to modify the column you need to used alter table statement and the MODIFY clause.

Also you can modify data type, default value, size, add or remove NOT NULL constraint.

FOR EXAMPLE

ALTER TABLE NEWEMP

MODIFY (ABC VARCHAR2 (50));

------------------------------------------------------------------------------------

TABLE DROPPING

You can drop table using DROP TABLE statement. While drop a table index for these table will be dropped

You can use THE CASCED CONSTRAINT option  if there are any referential constraint .

For Example

DROP TABLE NEWEMP

CASCADE CONSTRAINTS;

-------------------

RENAMING TABLES

You can rename a table by using RENAME statement

Syntax

RENAME old_name

To              new_name

For example

RENAME newemp

To              training;

-------------------------------------------------------------------

TRUNCATE A TABLE

Means that you can remove all the rows immediately without generating any rollback information

Before using the TRUNCATE TABLE statement, you should disable any foreign key constraint.

For example

If you want remove rows from NEWEMP table

Type

Truncate table NEWEMP;

---------------------------------------------------

COMMENT ON TABLE

You can add or remove a comment on the table as a massage

For example

Type 

Comment on table TRAINING

Is ‘content information about trainees people ’

To remove a comment

Type

Comment on table TRAINING

Is ‘’