Structured Query Language/Views

Often users and applications request information in a form which differs from the structure of existing tables. To achieve those requests the SELECT command offers plenty of possibilities: projections, joins, group by clause, and so on. If there are always the same requests, which is the case particularly for applications, or if the table structure intentionally should be hidden from the application-level, views can be defined. Furthermore, the access rights to views may be different from those to tables.

Views look like a table. They have columns of a certain data type, which can be retrieved in the same way as columns of a table. But views are only definitions, they don't have data of its own! Their data is always the data of a table or is based on another view. A view is a different sight to the stored data or somewhat like a predefined SELECT.

Create a View
One creates a view by specifying its name, optionally column names, and especially the SELECT command on which the view is based. Within this SELECT all elements are allowed in the same way as in a standalone SELECT command. If no column names are specified the column names of the SELECT are used.

Example 1: Hide Columns
As a first example here is the view person_view_1 which contains all but id and ssn columns of table person. Users who have the right to read from this view but not from the table person don't have access to id and ssn.

As indicated in the above 'order by' example it is possible to use all columns of the view (but not all of the table!) within any part of SELECTs to the view: in the projection, the WHERE, ORDER BY, GROUP BY and HAVING clauses, in function calls and so on.

Example 2: Rename Columns
Next, there is a renaming of a column. The column name lastname of the table will be familyname in the view.

Example 3: Apply WHERE Condition
Not only columns can be hidden in a view. It's also possible to hide complete rows, because the view definition may contain a WHERE clause.

This view contains only persons born in San Francisco or Richland. All other persons are hidden. Therefore the following SELECT retrieves nothing although there are persons in the table which fulfill the condition.

Example 4: Use Functions
This example uses the sum function.

In this example, the elaborated construct 'GROUP BY / HAVING' is hidden from users and applications.

Example 5: Join
Views can contain columns from several tables by using JOIN commands. The following example view contains the name of persons in combination with the available contact information. As an INNER JOIN is used, some persons occur multiple, others not at all.

The columns person.id an contact.person_id are used during the definition of the view. But they are not part of the projection and hence not available for SELECTs to the view.

Hint: The syntax and semantic of join operations is explained on a separate page.

Some more Hints
Within a CREATE VIEW statement, one may use more elements of the regular SELECT statement than it is shown on this page, especially: SET operations, recursive definitions, CASE expressions, ORDER BY, and so on.

If there is an ORDER BY clause within the CREATE VIEW and another one in a SELECT to this view, the later one overwrites the former.

Write Access via Views
In some cases, but not in general, it should be possible to change data (UPDATE, INSERT or DELETE command) in a table by accessing it via a view. Assume, as a counterexample, that one wants to change the column count_of_members of person_view_4 to a different value. What shall the DBMS do? The column is subject to an aggregate function which counts the number of existing rows in the underlying table. Shall it add some more random values into new rows respectively shall it delete random rows to satisfy the new value of count_of_members? Of course not!

On the other hand, a very simple view like 'CREATE VIEW person_0 AS SELECT * from person;', which is a 1:1 copy of the original table, should be manageable by the DBMS. Where is the borderline between updateable and non-updateable views? The SQL standard does not define it. But the concrete SQL implementations offer limited write-access to views based on their own rules. Sometimes these rules are very fixed, in other cases they consist of flexible techniques like 'INSTEAD OF' triggers to give programmers the chance to implement their own rules.

Here are some general rules which may be part of the implementors fixed rules to define, which views are update-able in this sense:
 * The view definition is based on one and only one table. It includes the Primary Key of this underlying table.
 * The view definition must not use any aggregate function.
 * The view definition must not have any DISTINCT-, GROUP BY- or HAVING-clause.
 * The view definition must not have any JOIN, SUBQUERY, SET operation, EXISTS or NOT EXISTS predicate.
 * Many implementations have a keyword which can be used to force a view to be read-only, even if technically it doesn't have to be.

If it is possible to use the UPDATE, INSERT or DELETE command to a view, the syntax is the same as with tables.

Clean up the Example Database
The DROP VIEW statement deletes a view definition. In doing so the data of the underlying table(s) is not affected.

Don't confuse the DROP command (definitions) with the DELETE command (data)!

Exercises
Create a view 'hobby_view_1' which contains all columns of table 'hobby' except 'id'.

Rename column 'remark' to 'explanation'. Create two different solutions.

Create a view 'hobby_view_2' with the same criteria as in the previous example. The only difference

is that the length of the explanation column is limited to 30 character. Hint: use the function

substr(, 1, 30) to determine the first 30 characters - this is not part of the SQL standard but works in many implementations.

Create a view 'contact_view_3' which contains all rows of table contact with the exception of the 'icq' rows. Count the number of the view rows and compare it with the number of rows in the table 'contact'.

Create a view 'contact_view_4' which contains one row per contact type with its notation and the number of occurrences. Afterwards select those which occur more than once.

Create a view 'person_view_6' which contains first- and lastname of persons plus the number of persons with the same name as the person itself (family name). Hint: the solution uses a correlated subquery.

Clean up the example database.