Views In Oracle SQL

  Usage of Views in Oracle SQL 

                                                                                        Doc: By Venkat

View is often described as a "Virtual Table." .

It doesn't store data physically; instead, it stores a query that dynamically retrieves data from underlying tables (called Base/Master Tables) whenever the view is accessed.

Definition

A View is a named, stored subquery that acts as a window into one or more tables. 

It provides a way to present data in a different perspective, simplify complex joins, and add a layer of security by hiding sensitive columns.

Syntax

CREATE [OR REPLACE] VIEW view_name AS

SELECT column1, column2, ...

FROM table_name

[WHERE condition]

[WITH CHECK OPTION]

[WITH READ ONLY];

OR REPLACE: Drops and recreates the view if it already exists (prevents errors).

WITH CHECK OPTION: Ensures that any INSERT or UPDATE performed through the view satisfies the WHERE clause.

WITH READ ONLY: Prevents any DML (Insert/Update/Delete) operations on the view.

Q:

Data Security (Hiding Columns)

Create a view that shows employee names and departments but hides sensitive information like SALARY or COMMISSION.

CREATE OR REPLACE VIEW emp_public_info AS

SELECT empno, ename, job, deptno

FROM emp;

select *from emp_public_info;

Q:

Simplifying Complex Joins

Instead of writing a 3-table join every time, you can wrap it in a view. This is excellent for Business Intelligence (BI) reporting.

CREATE OR REPLACE VIEW emp_dept_details AS

SELECT e.ename, e.job, d.dname, d.loc

FROM emp e

JOIN dept d ON e.deptno = d.deptno;

select *from emp_dept_details;

Q: 

Restricting Rows (Data Subsetting)

Create a view that only shows employees belonging to Department 20.

CREATE OR REPLACE VIEW dept20_employees AS

SELECT * FROM emp

WHERE deptno = 20

WITH CHECK OPTION; 

-- The check option prevents users from accidentally inserting a Dept 10 record through this view.

select *from dept20_employees;

Q: Renaming Columns for Business Clarity

Sometimes table column names are technical (Ex:- ST_ADDR_1). 

You can use a view to give them user-friendly aliases.

CREATE OR REPLACE VIEW sales_report AS

SELECT sal AS monthly_salary, 

       comm AS bonus, 

       ename AS employee_name

FROM emp;

select *from sales_report;

Q: Aggregated View (Summary Data)

Useful for dashboards where you only need total counts or averages rather than individual records.

CREATE OR REPLACE VIEW dept_salary_summary AS

SELECT deptno, 

       COUNT(*) as total_staff, 

       round(AVG(sal),2) as average_sal

FROM emp

GROUP BY deptno;

select *from dept_salary_summary;

FAQ

Do Views take up disk space?

No, they only store the query definition in the data dictionary. The data remains in the base tables.

Can you update data through a view?

Yes, if it is a Simple View (one table, no aggregations). No, if it is a Complex View (joins, GROUP BY, or DISTINCT), unless you use "Instead Of" triggers.

What is a Materialized View?

Unlike a standard view, a Materialized View actually stores the data physically and must be refreshed. It is used for performance tuning in large data warehouses.

Why use "WITH READ ONLY"?

To ensure the view is used strictly for reporting and to prevent users from accidentally modifying the base table data.