How to create View in Oracle Database.

In this post I am going to explain how to create VIEW in the oracle Database.
First You have to create below table in your oracle database.

DEPARTMENT Table  Creation Script:-

CREATE TABLE "SYSTEM"."DEPARTMENT" (
"DEPTNO" NUMBER(4,0),
"DEPARTMENT_NAME" VARCHAR2(10 BYTE),
PRIMARY KEY ("DEPTNO"));

DEPARTMENT Table insert Script:- 

Insert into DEPARTMENT (DEPTNO,DEPARTMENT_NAME) values (10,'HR');
Insert into DEPARTMENT (DEPTNO,DEPARTMENT_NAME) values (20,'Finance');
Insert into DEPARTMENT (DEPTNO,DEPARTMENT_NAME) values (30,'IT'); 

EMPLOYEE_SRC Table  Creation Script:-

CREATE TABLE "SYSTEM"."EMPLOYEE_SRC" (
"EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10 BYTE),
"JOB" VARCHAR2(9 BYTE),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
PRIMARY KEY ("EMPNO")); 

EMPLOYEE_SRC Table insert Script:-

Insert into EMPLOYEE_SRC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (1,'KING','PRESIDENT',null,to_timestamp('17-NOV-81','DD-MON-RR HH.MI.SSXFF AM'),5000,null,10);
Insert into EMPLOYEE_SRC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7698,'BLAKE','MANAGER',7839,to_timestamp('01-MAY-81','DD-MON-RR HH.MI.SSXFF AM'),2850,null,30);
Insert into EMPLOYEE_SRC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7782,'CLARK','MANAGER',7839,to_timestamp('09-JUN-81','DD-MON-RR HH.MI.SSXFF AM'),2450,null,10);
Insert into EMPLOYEE_SRC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,to_timestamp('02-APR-81','DD-MON-RR HH.MI.SSXFF AM'),2975,null,20);
Insert into EMPLOYEE_SRC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7788,'SCOTT','ANALYST',7566,to_timestamp('19-APR-87','DD-MON-RR HH.MI.SSXFF AM'),3000,null,20);
Insert into EMPLOYEE_SRC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7902,'FORD','ANALYST',7566,to_timestamp('03-DEC-81','DD-MON-RR HH.MI.SSXFF AM'),3000,null,20);
Insert into EMPLOYEE_SRC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,to_timestamp('17-DEC-80','DD-MON-RR HH.MI.SSXFF AM'),800,null,20);
Insert into EMPLOYEE_SRC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALLEN','SALESMAN',7698,to_timestamp('20-FEB-81','DD-MON-RR HH.MI.SSXFF AM'),1600,300,30);
Insert into EMPLOYEE_SRC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WARD','SALESMAN',7698,to_timestamp('22-FEB-81','DD-MON-RR HH.MI.SSXFF AM'),1250,500,30);
Insert into EMPLOYEE_SRC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7654,'MARTIN','SALESMAN',7698,to_timestamp('28-SEP-81','DD-MON-RR HH.MI.SSXFF AM'),1250,1400,30);
Insert into EMPLOYEE_SRC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7844,'TURNER','SALESMAN',7698,to_timestamp('08-SEP-81','DD-MON-RR HH.MI.SSXFF AM'),1500,0,30);
Insert into EMPLOYEE_SRC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7876,'ADAMS','CLERK',7788,to_timestamp('23-MAY-87','DD-MON-RR HH.MI.SSXFF AM'),1100,null,20);
Insert into EMPLOYEE_SRC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7900,'JAMES','CLERK',7698,to_timestamp('03-DEC-81','DD-MON-RR HH.MI.SSXFF AM'),950,null,30);
Insert into EMPLOYEE_SRC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,to_timestamp('23-JAN-82','DD-MON-RR HH.MI.SSXFF AM'),1300,null,10);

First write a Select statement and it  will base our view.

select
d.department_name  AS "D_NAME",
sum(E.sal)  AS "COST"
from Employee_SRC E
inner Join department D on D.DEPTNO= E.DEPTNO
group by d.department_name;

After that create a view using above select statement.

create VIEW "SYSTEM"."VW_DEPT_COST"("D_NAME","COST") AS
select
d.department_name AS "D_NAME",
sum(E.sal) AS "COST"
from Employee_SRC E
inner Join department D on D.DEPTNO= E.DEPTNO
group by d.department_name;

After that you can view the data of that created View using below script.

select * from VW_DEPT_COST;

Thank you !!!