Sybase 12.4.2 Creating views, Working with views, Definition in the database system tables, 128

Working with views

Similarities between views and base tables

Differences between views and permanent tables

Benefits of tailoring access

Views are similar to the permanent tables of the database (a permanent table is also called a base table) in many ways:

•You can assign access permissions to views just as to base tables.

•You can perform SELECT queries on views.

•You can perform INSERT and DELETE operations on some views.

•You can create views based on other views.

There are some differences between views and permanent tables:

•You cannot create indexes on views.

•You cannot perform INSERT, DELETE, and UPDATE operations on all views.

•You cannot assign integrity constraints and keys to views.

•Views refer to the information in base tables, but do not hold copies of that information. Views are recomputed each time you invoke them.

Views are used to tailor access to data in the database. Tailoring access serves several purposes:

•Improved security By not allowing access to information that is not relevant.

•Improved usability By presenting users and application developers with data in a more easily understood form than in the base tables.

•Improved consistency By centralizing in the database the definition of common queries.

Creating views

	A SELECT statement operates on one or more tables and produces a result set
	that is also a table: just like a base table, a result set from a SELECT query has
	columns and rows. A view gives a name to a particular query, and holds the
	definition in the database system tables.
Example	Suppose that you frequently need to list the number of employees in each
	department. You can get this list with the following statement:
	SELECT dept_ID, count(*)
	FROM employee
	GROUP BY dept_ID
	You can create a view containing the results of this statement as follows:

128