Sayantan's Blog On Oracle

CREATE Statement in Oracle

CREATE statement is used to create a new database object. Its applicable below database objects in SQL:

• TABLE
• VIEW
• INDEX
• SEQUENCE
• SYNONYM

We will discuss only about tables in this article.

Naming convention for creating a TABLE:

• It must be starts with a character
• Maximum length of a table name allowed in oracle is 128
• It contains only A-Z, a-z, 0-9, _, # and $
• There should not be any database object which has the same name as the table name to be created
• It should not contain any oracle database reserve keywords

Types of CREATE Table statements:

• CREATE TABLE WITH COLUMN DEFINITION
• CREATE TABLE WITH SUBQUERY
• CREATING TABLE WITH COMPLETE STRUCTURE AND DATA OF ANOTHER TABLE
• CREATING TABLE WITH PARTIAL STRUCTURE AND DATA OF ANOTHER TABLE
• CREATING TABLE WITH STRUCTURE OF ANOTHER TABLE ONLY

CREATE TABLE WITH COLUMN DEFINITION:

CREATE TABLE EMP_CREATE_TABLE_TEST
(EMPLOYEE_ID       NUMBER(6),
 FIRST_NAME        VARCHAR2(20),
 LAST_NAME         VARCHAR2(20),
 JOB_ID            VARCHAR2(10),
 SALARY            NUMBER(8, 2),
 DEPARTMENT_ID     NUMBER(4)
);

CREATE TABLE WITH SUBQUERY:

CREATE TABLE DEPT_WISE_SUMMARY
AS
SELECT DEPARTMENT_ID,
       SUM(SALARY) TOTAL_SALARY,
       ROUND(AVG(SALARY), 0) AVG_SALARY,
       MAX(SALARY) MAX_SALARY,
       MIN(SALARY) MIN_SALARY,
       COUNT(EMPLOYEE_ID) TOTAL_EMPLOYEES
FROM EMPLOYEES
WHERE DEPARTMENT_ID IS NOT NULL
GROUP BY DEPARTMENT_ID
ORDER BY DEPARTMENT_ID;

SELECT * FROM DEPT_WISE_SUMMARY;

CREATING TABLE WITH COMPLETE STRUCTURE AND DATA OF ANOTHER TABLE:

CREATE TABLE EMPLOYEES_TEST
AS
SELECT * FROM EMPLOYEES;

SELECT * FROM EMPLOYEES_TEST;

CREATING TABLE WITH PARTIAL STRUCTURE AND DATA OF ANOTHER TABLE:

CREATE TABLE EMPLOYEES_TEST_PART
AS
SELECT EMPLOYEE_ID, 
       FIRST_NAME, 
       LAST_NAME, 
       HIRE_DATE, 
       JOB_ID, 
       SALARY, 
       DEPARTMENT_ID 
FROM EMPLOYEES;

SELECT * FROM EMPLOYEES_TEST_PART;

CREATING TABLE WITH STRUCTURE OF ANOTHER TABLE ONLY:

CREATE TABLE EMPLOYEES_TEST_STRUCT
AS
SELECT * FROM EMPLOYEES
WHERE 1 = 2;

SELECT * FROM EMPLOYEES_TEST_STRUCT;

RELATED TOPICS:

• Insert operation in Oracle