USING VIEWS

Before we are able to create views, we should know what view actually means. It is logical structure based on one or more tables, or another view.

We are treating views as any other table in the database. We can query them, perform insert, update and delete. Actually when we perform DMLs on a view, the operation will consider the table on which base the view is created.

There are many reason why we should use views. One of them is security. What does it means? It means that views give us opportunity to hide some critical information stored in the table.

Example:

Let take table employees from HR schema. It contains information about all employees in the company, names, emails, phones, salary…. Now we need to give access to the people from payroll department just to columns department_id, first_name, last_name, salary. To do this we have to create a view based on table employees.



Screen clipping taken: 4.2.2011 г.; 13:15 ч.

Now let take we should give the shipping department manager access to the information about all employees in his own department and hide from him the information about the rest of the employees in the company. To do this we are going to create a view called shipping_staff. This view will be based on 2 tables: employees and departments



Other reasons for creating and using views can be:

1. Better performance
2. Simplify SQL
3. Preventing errors...

   In our first example the view is based on just one table and it doesn't use any functions. It make it simple view.

   In our second example the view is based on two tables which make it automatically complex view

SIMPLE VIEW	COMPLEX VIEW
Select from one table	Select from one or more tables
Does not contain functions	Contains functions
Can perform DML trough simple views	Can't always perform DML trough complex views