SQL Views

The "View" is a virtual table which contains rows and columns. View is commonly used in SQL. View simplify the complex queries which means if there is any complex query, then we can create “view” out of it so that we don’t need to type complex query again and again. View doesn’t store data. View doesn’t create separate copy of data, it retrieves the data from original table, exceptional is materialized view.

Now, let's compare the regular query and same query using view.

E.g.

Select e. employee_id, e. first_name, d.department_id

 from employees e, departments d

 where

 e.department_id=d. department_id; 

In this query the third person gets to know all the confidential data like all the column names (e.g.. Department_id , employee_id) and the tables from which data is retrieved etc.

 So, if we create a view on this query the confidential data does not get to know the other person.

 On the same query let’s create a view.

E.g. View is created with create view.

•   Syntax of View:

•  Create view view name as

      Select column1, column2, column3,

From table name

Where condition;

•  Eg.

• Create view vw_tables as Select e. employee_id, e. first_name, d. department_id from employees e, departments d where e. department_id=d. department_id;

Select * from vw_tables;

We can see here both the queries have the same output. But because we created a view on a second query all the confidential information got secured.

 In this “vw_tables” is the view name; it is not table name. “Vw_tables” doesn’t contain any data. It does not store any actual data.

Uses of View:

1.     View used as security mechanism. View offers security to data.

2.    View can reduce complexity of data. View can simplify the query.

3.    View can improve performance of query by pre-computing data such joints, aggregation etc.

Types of View:

There are two types of View: Simple View and Complex View

1.Simple view: Simple View contains only one base table.

2.Complex view: Complex view contains more than one base table.

Now let’s see the difference between Simple View and Complex View.

Simple View	Complex View
It contains only base tables.	It contains more than one base table.
It doesn’t contain aggregate functions like sum, count and max etc.	It may contain aggregate functions like sum, count and max etc.
It doesn’t contain Group by clause.	It contains Group by clause.
It doesn’t contain Null values.	It contains Null values which is not selected from Simple view.
It involves Select statement without any joints and subqueries.	It involves Joins, subqueries.
We can apply insert, update, and delete on simple view.	We cannot apply insert, update, and delete on complex view.
E.g.  Create view vw_tables         Select * from employee         Where employee_name= ‘John’	E.g. Create view vw_tables as select e. employee_id,e.first_name,d.department_id from employees e, departments d where e.department_id=d.department_id;

3.Materialized view: Materialized view is just same as View, but view doesn’t store the data and materialized view store data. Materialized view store query plus data.

Advantages of Materialized view:

1.     For large datasets, regular queries take time. But materialized view increases the speed of queries because data is already stored and pre-computed.

2.     Using “Refresh materialized view” command, we can easily refresh the materialized view.

3.     In materialized view the data is precomputed, so query performance gets improves.

Disadvantages of Materialized view:

1.     It requires additional storage to maintain materialized view.

2.     It needs to be refreshed after regular intervals because original data might change.

3.     If data is real-time data, then materialized view is not good choice.

4.Comparison view and materialized view:

View	Materialized View
It is only store query.	It is storing query plus data.
The views are dynamic. It means it displays data from tables or view.	Materialized views are static. It means it only displays data from the last refresh.
The view is not as fast as materialized view.	It responds faster than View.
Views are more suitable for real-time data.	Materialized views are more suitable for queries that need pre-computed data.
Views are more convenient for creating temporary queries.	Materialized views are more convenient for permanent queries.

Now we can see steps of how to create view, create and replace view and drop of view.

1. Here we can see step by step how to create a view.

•  First connect to database.

•  For creating view first add “Create View” command

•  Then type “View name”, in our example view name is vw_dept

•   After the “View name” type “Alias”. i.e.. “as”

e.g. 

create view  vw_dept as

select department_ID,department_Name

from departments;

•    After this view is created.

•    For display data, type “View name.”

•    i.e. Select * from vw_dept;

•   So that we will get data, i.e. department_id and department_Name.

•   Using this step we can create view.

2.Now let’s see how to Create and Replace View.

We can update the view with “create or replace view” statement.

• Syntax of update view:

• Create or Replace View view_name AS

Select column1, column2, ...

From table_name

Where condition;

After creating a view, if we re-executed the same query again it gives us error.

The error we get is “Name is already used by existing object.”

•  So here is one more command we have, i.e. “Create or replace view.”
  

• E.g.

• Create or Replace view vw_dept as

select department_ID,department_Name

from departments;

•         So, in the query a view is  created.

•         If we executed this query again and again then it first checks view is presented or not.

•         If the view is not present, then it creates a view.

•         If the view is already there then it is replaced with view.

•         So that if we executed this query again and again it won’t give us error.

•         In create or replace view we cannot change data type, order of columns, and change number of columns.

•        If it changes, then it will be through error.

3.Now let’s see how to drop View

•  So, we want to delete the view or remove the view completely then use “Drop view.”

•  Syntax of Drop View:

•   Drop view view name;

Following are steps to drop view,

             1.First type “Drop view" command.

             2. Then mention the view_name. i.e vw_dept.

             E.g. Drop view vw_dept;

4.     Now let’s see how to update the view.

•  If we want to change any data or update data, we use “Update” command.

•  But all views are not updatable. Because of that, the update command is not applicable for all the views.

• There are some rules for updating view:

• We can update those view who have single table.

•  The view should not contain “Distinct clause.”

•  The view should not contain “Group by clause” and “Having clause”.

•  The view should not contain aggregate functions.

•  Syntax of Update:

            Update view_name

            Set column1, column2,

            Where condition;

•     For updating view,

•     First create or replace view with view_name.

•     E.g. create or replace view vw_departments as        select * from departments;

After creating a view on vw_departments now let’s update the table.

Here are a few steps for updating the table.

1.     First give Update command.

2.     After updating the command give view name.

3.     Give “set” command after that column name which you want to update and its value.

4.     Then give “Where” condition.

       E.g.

       Update vw_departments

Set department_name = ‘Education’

where department_id=60;

Conclusion:

SQL View is very useful. It restricts sensitive data. Views simplify the complexity of query. We can manage our SQL database easier and more efficiently by learning Views. Views improve performance of query and analysis.