Views

Section 0: Module Objectives or Competencies

Section 1: Overview

All SQL queries result in the creation of a temporary table to hold the results.

The VIEW command can be used to create a logical (virtual) table.

• A view is a query that is stored in the database and returns the result set of the query by which it is defined. A simple view never stores data, but simply fetches the results of the query in which it is defined.
• A VIEW is simply a way of creating a table from a select statement that is then accessible for other queries.
• The logical table exists only in memory, but may be treated as if it is a real table.
  • A VIEW is just a logical table that represents the data in the table(s) that it is built from.
  • It does not actually store the associated data a second time.
  • When the base table is updated, the view is also updated. More on updatable views later.

The VIEW command is especially useful because not only can it be used as a mechanism for saving queries, but it may be used to restrict user access to selected portions of the data contained in a table.

In addition, restricting access to columns and rows allows users to focus on the specific data that interests them.

• Unnecessary data is left out.
• Network performance is improved because less data is traveling over the internet to client applications.
• Using a view to filter data on the server is much more efficient than fetching an entire table and then applying a filter on the client.
• To restrict the rows in a view, you use the same kinds of criteria in a WHERE clause that you are already familiar with.

Section 2: Syntax

CREATE VIEW viewName AS SELECT query

Example:

CREATE VIEW PRICEGT50 AS
  SELECT P_DESCRIPT, P_QOH, P_PRICE
  FROM PRODUCT
  WHERE P_PRICE > 50.00;

SELECT * FROM PRICEGT50;

Section 3: Uses

There are at least seven primary uses of VIEWS:

1. to save queries
2. to provide row and column level 'security'
3. to ensure efficient access paths
4. to mask complexity from the user
5. to ensure proper data derivation
6. to rename columns
7. to provide solutions that cannot be accomplished without views (i.e., complex queries built on the results of other queries/views)

It is useful to provide the users with a VIEW rather than the real table, because if they have access only to a view, they cannot corrupt or damage the real table.

Views can also be used as the basis for reports. For example, if you need a report that shows a summary of total product cost and quantity-on-hand statistics grouped by vendor, you could use the following:

CREATE VIEW PROD_STATS AS
  SELECT V_CODE, SUM(P_QOH*P_PRICE) AS TOTCOST, MAX(P_QOH) AS MAXQTY,
    MIN(P_QOH) AS MINQTY, AVG(P_QOH) AS AVGQTY
  FROM PRODUCT
  GROUP BY V_CODE;

SELECT * FROM PROD_STATS;

Section 4: Composition

VIEWS can consist of:

• Rows from tables, including:
  • a subset of rows from a single table,
  • all rows from a single table,
  • a subset of rows from multiple tables, or
  • all rows from multiple tables.
• Rows from views, including the same combinations as listed above for tables.
• Columns from tables, including:
  • a subset of columns from a single table,
  • all columns from a single table
  • a subset of columns from multiple tables, or
  • all columns from multiple tables.
• Columns from views including the same combinations as listed above for tables.

Section 5: Saving Queries

If you want to re-issue a query and get the same result set, the query can be saved as a text file, opened, and executed again.

SQL provides a better way to save queries in the database catalog through a view.

A view is a named query stored in the database catalog that allows it to be referred to later.

You can save a query as a view with a name like "Q6a" or "Q7", as in the following example:

CREATE VIEW Q00 AS
  SELECT P_CODE, P_DESCRIPT, P_PRICE
  FROM PRODUCT
  WHERE P_PRICE = (SELECT MAX( P_PRICE ) FROM PRODUCT);

Then to run your query and see the results, apply a SELECT statement to the view:

SELECT * FROM Q00;

Note that in MySQL you cannot save a prepared statement as a view, but it is possible to save prepared statements (or multi-statement queries) in a stored procedure by enclosing all statements inside a BEGIN-END block.

Section 6: Updatable Views

Views can be read-only or designed to allow dynamic updates to the underlying table, so if the data in the original table is altered, those alterations are reflected in the VIEWs.

However, in order to create an updatable view using MySQL, the SELECT statement that defines View must follow these restrictions (link):

• SELECT statements must not reference more than one table. There must not be more than one table in a FROM clause, other tables in JOIN statements, or a UNION with other tables.
• SELECT statements must not use the GROUP BY or HAVING clause.
• SELECT statements must not use DISTINCT in the selection list.
• SELECT statements must not reference a view that is not updatable
• SELECT statements must not contain any expression (aggregates, functions, computed columns…)

Section 7: Links

• Creating Updatable Views
• Creating an Updatable View
• Updatable and Insertable Views
• MySQL Tutorial - Views