SQL CREATE Statement

---

While creating the table we need to follow below rules:

• The user should have CREATE TABLE privilege.
• The table name should starts with letter.
• The table name 1-30 characters long.
• Table name cannot be duplicate with other objects.
• Table name should not be oracle server reserved word.

Syntax:

CREATE TABLE Table_Name (Column_Name1 1<Data_type> [size],Column_Name2  <Data_type>[size],...);

Example:

CREATE TABLE STUDENTS
        (STUDNO NUMBER(6),FNAME VARCHAR2(30),LNAME VARCHAR2(30),
         DOB DATE,DOJ DATE,FEES NUMBER(7,2),
         GENDER VARCHAR2(1),INSERTBY VARCHAR2(10));

If you try to create with same column name in same table, it will show you below error


Two columns can't have same name in same table :

create table col_same_name(col1 varchar2(10),col1 number);
ORA-00957: duplicate column name

Other way to create table:

If you want to create a table in your database with existing table structure than follow the below SQL.

CREATE TABLE STUDENTS_TBL AS SELECT * FROM STUDENTS WHERE 1=2;

If you want to create a table in your database with existing table structure and data than follow the below sql

CREATE TABLE STUDENTS_TAB AS SELECT * FROM STUDENTS;

Error List : While creating a table we can get bellow errors.

List Of Error
ORA-00972: identifier is too long
ORA-00955: name is already used by an existing object
ORA-00903: invalid table name
ORA-00957: duplicate column name