Lesson 8

Views

 

A view is created to create a virtual table. It is a vehicle to examine and handle data. The virtual table created does not physically exist in the database.

 

The basic syntax of the view is:

 

            CREATE VIEW view_name [(column_name [, column_name] ...)]

            AS

            SQL query statement

 

 

 

Example - A simple view from showing the courses students are registered in.

 

            CREATE VIEW studreg

            AS

            SELECT s.studnum, s.surname, r.course, r.section

                        FROM student as s, register as r

                        WHERE s.studnum = r.studnum                                                                                                                                                                     

 

To execute the view:

 

            SELECT * FROM studreg

 

If you are using a lot of system resources you may wish to cancel or DROP the view.

 

            DROP VIEW studreg

 

Views can save a lot of typing and allow you to develop complex selects which your users can then execute without knowledge of the underlying SQL statement. Of course you could save your SQL statements in operating system files and have your users submit these. Views are more intuitive for frequently used selects.

 

Create views for groups of users with common data requests about their perception of the database. The student has a different view of the database than the teacher. Likewise an accountant would have a different view of the database than a sales manager.

 

Only allowing users to use views adds security as users can be restricted to the data in the database they allowed to see.

 

The three main benefits of VIEWS are:

 

                        - Simplifying data access

           

                        - Enhancing data security

           

                        - Enhances data independence

 

 

Views separate your users from the complexity of the database and provide independence if the database changes. For example if you split a table or add a table your users views can be changed to reflect the change without them knowing there was a change. They simply continue to use the familiar view names.

 

Views are referred to as virtual tables. It is important to note the tables themselves are not stored only the definitions of the view are stored.

 

Most SQL statements will work in views; however, some dialects do not allow joins.

 

Example - Find all students with marks higher than the average algebra mark. Make this a view.

 

CREATE VIEW algbhigh

AS

SELECT  s.surname

            FROM student as s, mark as m

            WHERE s.studnum = m.studnum        

            AND mark >=

                        ( SELECT AVG(mark)

                          FROM mark

                          WHERE course = 'alg') ;

            AND m.course = ‘alg’

 

To execute the view

 

SELECT * FROM algbhigh                                                                                                                                                                     

 

 

 

8.1 Problems with views and standards

 

The main reason for restrictions on views is that of interpreting data modification statements.

 

The ISO ANSI standard is very strict on updating in views. It states views must be read only if the VIEW contains:

 

            - the keyword DISTINCT in the SELECT list

 

- expressions in the SELECT list (i.e. (aggregates, function, computed columns etc.)

 

            - references to more than one column in the FROM clause or in a sub query

 

            - a GROUP BY or HAVING clause.