VIEWS in SQL

VIEWS in SQL

Table of Contents

View is the single table which will be derived from the other tables of the database, and these other tables will be base tables or may be previously defined as views. Its not necessary for views to be physically present. It’s considered to be the virtual table.

We can also say that the view is the way of specifying table that we need to reference frequently even if the may not exists. Example: suppose if we frequently issue the queries which will retrieve the employee’s name and the project names that the employee will works for.

Specifying of views in SQL:

Basically, in the SQL to specify it, command will be used to create view where this will give the table name or may be view name and a list of attributes names, and also the query to specify the contents of the views.

Suppose if none of the view attributes outcome from putting in the arithmetic operations, then we should not specify attributes of the defining tables in default case.

Example 1:   create view workson1

                      As select fname, lname, pname

                      From employee, project, works for

                      Where ssn= essn and pno= pnumber;

Example 2: create view deptinfo 

                    As select dname, count (*), sum(salary)

                    From department, employee

                    Group by dname;

In the first example we will not specify any new attribute names for the views that is workson1 although we could have specified it but in this case workson1 will be inheriting the names of the view attributes from defining the tables employee, projects and works.

In the second example, we have specified new attributes for the view deptinfo by using one to one corresponds between attribute which is specified in the create view clause and also the specified in the select clause of the query which will define the view.

We can also state SQL queries as view or may be virtual table in the same way we need to state the queries which involves base tables.

Example 3: select fname, lname

                    From workson1

                    Where pname= ‘productx’;

fnamelnamepnamehours
Dept nameNo of empsTotal salary

In the same way the query will be specified in two joins if specified on the base of relation and one advantage or benefit would be view to simplify the specification of the certain queries.

View is suppose to be always up to date suppose, if we modify the database of the tables on which the view will be defined, the view should automatically reflect these changes.

View will not be realised at the time of the definition but it would rather reflect at the time to make sure view has been updated.

Example:

Drop view workson1;

Updating of the views would be complicated and also be ambiguous

Example 4: update workson1

                     Set pname= ‘producty’ 

                     Where lname= ‘jack‘ and fname= ‘plunket ‘

                      And pname= ‘producty’;

Example 5: update projectset 

                        Where pname= ‘producty’;

Some of the updates will not make sense 

Example 6: update deptinfo

                     Set total sal= 100000

                      Where dname= ‘research’;

Large number of updates on underlying base relation will be satisfying this view update. View update will be feasible only when the one possible update on the base relation which has accomplished the desired update effect on the views.

Questions

  1. What is view? Explain
  2. How to create view
Share this article
Subscribe
By pressing the Subscribe button, you confirm that you have read our Privacy Policy.
Need a Free Demo Class?
Join H2K Infosys IT Online Training