Creating SQL Views

To summarize, the benefits of creating user views are:

1. You can present each user with exactly the data they need to do their job, and only that data.
2. You can save a complicated join as a view and run it without recreating it, thus saving time and effort.
3. You can calculate values, which you cannot do in base tables.

As you will see in the next lesson, you can limit which operations users can perform on views.

The next lesson discusses permissions. Permissions are database tasks that a user or group of users is allowed to carry out. There are several types of permissions the administrator can grant:.

1. Select: The user can retrieve data from a table or view.
2. Insert: The user can create new records in a table or view. The user may also be limited to inserting values into particular fields.
3. Update: The user can modify existing values in a table or view, again for an entire table or specific columns.
4. Delete: The user can remove existing records from a table or view.
5. References: The user can use a field in an existing table or view a field as a foreign key in a table that the user creates. Thispermission may be limited to particular fields.
6. All: The user has all of the permissions listed above.

The people responsible for developing a database schema and those who write application programs for use by non-technical users typically have knowledge of and access to the entire schema, including direct access to the base tables of the database . However, it is usually undesirable to have end users working directly with base tables, primarily for security reasons. The relational data model therefore includes a way to provide end users with their own window into the database, one that hides the details of the overall database design and prohibits direct access to the base tables.

A view is not stored with data. Instead, it is stored under a name in the database itself along with a database query that will retrieve its data. A view can therefore contain data from more than

1. one table,
2. selected rows, and
3. selected columns.

Note: Although a view can be constructed in just about any way that you can query a relational database, many views can be used for data display. Only views that meet a strict set of rules can be used to modify data. The power of storing views in this way, however, is that whenever the user includes the name of the view in a data manipulation language statement, the DBMS executes the query associated with the view name and recreates the table of the view. This means that the data in a view will always be current. A view table remains in main memory only for the duration of the data manipulation statement in which it was used. As soon as the user issues another query, the view table is removed from main memory to be replaced by the result of the most recent query. A view table is therefore a virtual table^[1].
Note: Some end user DBMSs give the user the ability to save the contents of a view as a base table. This is a undesirable feature, as there are no provisions for automatically updating the data in the saved table whenever the tables on which it was based change. The view table will therefore become out of date and inaccurate

[1]Virtual tables: Virtual tables are views which are logical structures and are created from one or more database tables after applying a function or clause such as GROUP BY or ORDER BY.