Sams Teach Yourself SQL in 24 Hours

Recommended Book

Sams Teach Yourself SQL in 24 Hours, 3rd Edition

Learn More Buy

Recommended Book

Recommended Book 

Sams Teach Yourself SQL in 24 Hours, 3rd Edition

Learn More Buy

What Is a View?

A view is a virtual table. That is, a view looks like a table and acts like a table as far as a user is concerned. A view is actually a composition of a table in the form of a predefined query. For example, a view can be created from the EMPLOYEE_TBL table that contains only the employee's name and address, instead of all columns in the EMPLOYEE_TBL table. A view can contain all rows of a table or select rows from a table. A view can be created from one or many tables.

A view is a predefined query that is stored in the database, has the appearance of an ordinary table, and is accessed like a table, but does not require physical storage.

When a view is created, a SELECT statement is actually run against the database, which defines the view. The SELECT statement used to define the view may simply contain column names from the table, or can be more explicitly written using various functions and calculations to manipulate or summarize the data that the user sees. Study the illustration of a view in Figure 20.1.

Figure 20.1 The view.

A view is considered a database object, although the view takes up no storage space on its own. The main difference between a view and a table is that data in a table consumes physical storage, whereas a view does not require physical storage because it is actually referring to data from a table.

A view is used in the same manner as a table is used in the database, meaning that data can be selected from a view as it is from a table. Data can also be manipulated in a view, although there are some restrictions. The following sections discuss some common uses for views and how views are stored in the database.

Views Can Be Utilized as a Form of Security

Views can be utilized as a form of security in the database. Let's say you have a table called EMPLOYEE_TBL. The EMPLOYEE_TBL includes employee names, addresses, phone numbers, emergency contacts, department, position, and salary or hourly pay. You have some temporary help come in to write some reports; you need a report of employees' names, addresses, and phone numbers. If you give access to the EMPLOYEE_TBL to the temporary help, they can see how much each of your employees receives in compensation—you do not want this to happen. To prevent that, you have created a view containing only the required information: employee name, address, and phone numbers. The temporary help can then be given access to the view to write the report without having access to the compensation columns in the table.

Views Can Be Utilized to Maintain Summarized Data

If you have a summarized data report in which the data in the table or tables is updated often and the report is created often, a view with summarized data may be an excellent choice.

For example, suppose that you have a table containing information about individuals, such as their city of residence, their sex, their salary, and their age. You could create a view based on the table that shows summarized figures for individuals for each city, such as the average age, average salary, total number of males, and total number of females. After the view is created, to retrieve this information from the base table(s), you can simply query the view instead of composing a SELECT statement that may, in some cases, turn out to be very complex.

The only difference between the syntax for creating a view with summarized data and creating a view from a single or multiple tables is the use of aggregate functions. Review Hour 9, "Summarizing Data Results from a Query," for the use of aggregate functions.

How Is a View Stored?

A view is stored in memory only. A view takes up no storage space--as do other database objects—other than the space required to store the view definition itself. A view is owned by the view's creator or the schema owner. The view owner automatically has all applicable privileges on that view and can grant privileges on the view to other users, as with tables. The GRANT command's GRANT OPTION privilege works the same as on a table. See Hour 19, "Managing Database Security," for more information.