Views

Understanding Views:

To understand views let’s take an e.g. of a mirror from our day to day life. We can see exact replica of ourselves  in the mirror i.e. Virtual image. View is a also mirror image of a table.

Defining Views:

Technically speaking, view can be defined as a ‘Virtual Table’ which acts as a table but is not an actual table.To understand the concept of a view, let’s see what happens internally when a view is created.

Internal Working of a View:

Let’s take an EMP table containing 24 records.Out of 24 records, suppose 10 employees in EMP table have salary greater than 2000. Now, create a view for this.

CREATE VIEW Emp_V AS SELECT * FROM EMP WHERE SAL > 2000;

When above query is fired, it does not execute statement ‘SELECT * FROM EMP WHERE SAL > 2000’, instead stores this statement in the System Catalog under the name ‘Emp_V’. Thus, when we create a view, any new table is not created.Instead,only SQL statement containing its definition (SELECT * FROM EMP WHERE SAL > 2000) is stored with a name (Emp_V ), hence views do not occupy any space.

Scenario 1: Retrieving Data from View.

SELECT * FROM Emp_V;

Now, When this query is fired, SQL finds the view definition with name Emp_V in System Catalog  and converts the query into ‘SELECT * FROM EMP WHERE SAL>2000’  i.e SQL knows from the view definition that it has to select data whose salary is greater than 2000. As a result, it shows total 10 records.

Scenario 2: Updating Data in a View.

UPDATE Emp_V SET DEPTNO = 20;

Now, When this query is fired, SQL finds the view definition with name Emp_V in System Catalog and converts the query into ‘UPDATE EMP SET DEPTNO =20 WHERE SAL>2000’  i.e SQL knows from the view definition that it has to Update data for those whose salary is greater than 2000.let’s run the below query to fetch the data.

SELECT * FROM Emp;
SELECT * FROM Emp_V;

By firing either of above Select queries on the view or directly on the table, we can view the updated records i.e. for all those whose salary is > 2000, dept no. will be updated to 20.Thus, updations done on view are actually done on original table internally.

Scenario 3: What happens to view when we Insert  or update records directly into original table.

INSERT INTO EMP (EMPNO,ENAME,DEPTNO,SAL) VALUES('25','SHIKHA','20','5000');

As a result of above query, a new row is inserted into the EMP table resulting into a total of 25 records now.

SELECT * from Emp_V ;

Now, When we fire a Select query on view, it displays total 11 records ( 10 old records having salary > 2000 + 1 new inserted record ). Thus, whenever we query a view, Database recreates the data picking up the latest updated records from the original table. Thus, View always shows latest data. Similar is the case with the update query.

Key-Facts about Views:

• Views are virtual or pseudo tables that are not real tables.
• Views do not occupy any space as they do not store any data.
• View always shows latest data.
• Views are actually SQL statements being stored in the system catalog under a name i.e. the view name.
• View can be made on a single table ( Simple view ) or a combination of tables (Complex Views).
• Any changes i.e. update / insert /Delete done on views are actually done on original table.

Create Simple View :

CREATE VIEW <View Name> AS SELECT <Column Name> FROM <Table Name> WHERE Condition (optional);

e.g. CREATE VIEW EMP_V AS SELECT * FROM EMP ;
     CREATE VIEW EMP_V1 AS SELECT ENAME FROM EMP WHERE SAL > 2000;

Create Complex View :

Create or Replace View :

Suppose, we want to modify the existing view definition like add or remove columns from the ‘ select statement ‘ of view definition then we use create or replace view command.

CREATE OR REPLACE VIEW <View Name> AS SELECT <Column Name> FROM <Table Name> WHERE Condition;
e.g. CREATE OR REPLACE VIEW EMP_V AS SELECT * FROM EMP;

Note: In above Query, If View EMP_V is already present than it will replace that view, else will 
create new.

Create View on Existing View :

CREATE VIEW <View Name> AS SELECT <Column Name> FROM <View Name> WHERE Condition (optional);

e.g. CREATE VIEW EMP_V_1 AS SELECT * FROM EMP_V ;
     CREATE VIEW EMP_V_2 AS SELECT * FROM EMP_V_1;

Note : View EMP_V_1 is created on view EMP_V and EMP_V_2 is created on EMP_V_1.
Now if view EMP_V_1 is deleted than EMP_V_2 will not work but EMP_V will work. 

Create View with ‘FORCE’ Option :

e.g.CREATE VIEW FEES_V AS SELECT * FROM FEES;

The above query will give error as table FEES does not exist.

Now,suppose still we want to create above view, although table FEES is not there in Database, then we
have to use FORCE Option.

CREATE FORCE VIEW FEES_V AS SELECT * FROM FEES;

Output --> Warning Message : View Created with Compilation Errors.

Thus, with the help of 'FORCE' Option we can create a view even though Table is not present.
But this view can only be used when its base table is created.

Create View ‘WITH CHECK’ Option : 

Update , Insert and Delete operations on View :

• If a view is created on a Single Table and contains all mandatory columns of the original table, then insert , update , delete are possible on views.
• If view is created on a Single Table and do not contain all columns with ‘ NOT NULL’ constraint then only update and delete operations can be done, insert is not possible.
• Update / Insert are only possible on views , if the select clause of the view definition does not contain :distinct clause, set operators , order by clause , multiple tables in from clause, subqueries in where clause ,calculated columns and group by and Having clause.
• Insert , Update , Delete operations done on View are actually performed on original table.

UPDATE <View Name> SET <Column Name> = <Value> WHERE <Condition>;
e.g UPDATE EMP_V SET DEPTNO = 20 WHERE ENAME = 'SMITH';

INSERT INTO <View Name> (<Columns>) VALUES ('<Value>');
e.g. INSERT INTO EMP_V (EMPNO,ENAME) VALUES (26,'SHIKHA');

DELETE FROM <View Name> WHERE <Condition>;
e.g. DELETE FROM EMP_V WHERE DEPTNO = 10;

Dropping a View:

DROP VIEW < View Name>;
e.g. DROP VIEW EMP_V

To See View  Definition:

DESC VIEW;

Materialized View:

Difference between Materialized and Normal Views :

Advantages of View :