How to Create Views to Retrieve and Manipulate SQL Data
One of the most powerful features of SQL is its capability to display views of the data that are structured differently from how the database tables store the data. The structure of a database that’s designed according to sound principles — including appropriate normalization — maximizes the integrity of the data.
This structure, however, is often not the best way to look at the data. Several applications may use the same data, but each application may have a different emphasis. The tables you use as sources for columns and rows in a view are the base tables.
A SELECT statement always returns a result in the form of a virtual table. A view is a special kind of virtual table. You can distinguish a view from other virtual tables because the database’s metadata holds the definition of a view. This distinction gives a view a degree of persistence that other virtual tables don’t possess.
You can manipulate a view just as you can manipulate a real table. The difference is that a view’s data doesn’t have an independent existence. The view derives its data from the table or tables from which you draw the view’s columns. Each application can have its own unique views of the same data.
Suppose a database contains five tables: CLIENT, TESTS, EMPLOYEE, ORDERS, and RESULTS. Suppose the national marketing manager wants to see from which states the company’s orders are coming. Some of this information lies in the CLIENT table; some lies in the ORDERS table.
Suppose the quality-control officer wants to compare the order date of a test to the date on which the final test result came in. This comparison requires some data from the ORDERS table and some from the RESULTS table. To satisfy needs such as these, you can create views that give you exactly the data you want in each case.
For the marketing manager, you can create this view.
The following statement creates the marketing manager’s view:
CREATE VIEW ORDERS_BY_STATE (ClientName, State, OrderNumber) AS SELECT CLIENT.ClientName, State, OrderNumber FROM CLIENT, ORDERS WHERE CLIENT.ClientName = ORDERS.ClientName ;
The new view has three columns: ClientName, State, and OrderNumber. ClientName appears in both the CLIENT and ORDERS tables and serves as the link between the two tables. The new view draws State information from the CLIENT table and takes the OrderNumber from the ORDERS table. In the preceding example, you declare the names of the columns explicitly in the new view.
Note that the ClientName is prefixed with the table that contains it, but it’s not done for State and OrderNumber. That is because State appears only in the CLIENT table and OrderNumber appears only in the ORDERS table, so there is no ambiguity. However, ClientName appears in both CLIENT and ORDERS, so the additional identifier is needed.
You don’t need this declaration if the names are the same as the names of the corresponding columns in the source tables. The example shows a similar CREATE VIEW statement, except that the view column names are implied rather than explicitly stated.
With a selection condition
The quality-control officer requires a different view from the one that the marketing manager uses.
Here’s the code that creates the view:
CREATE VIEW REPORTING_LAG AS SELECT ORDERS.OrderNumber, OrderDate, DateReported FROM ORDERS, RESULTS WHERE ORDERS.OrderNumber = RESULTS.OrderNumber AND RESULTS.PreliminaryFinal = 'F' ;
This view contains order-date information from the ORDERS table and final-report-date information from the RESULTS table. Only rows that have an ‘F’ in the PreliminaryFinal column of the RESULTS table appear in the REPORTING LAG view. Note also that the column list in the ORDERS_BY_STATE view is optional. The REPORTING_LAG view works fine without such a list.
With a modified attribute
The SELECT clauses contain only column names. You can include expressions in the SELECT clause as well. Suppose VetLab’s owner is having a birthday and wants to give all his customers a 10-percent discount to celebrate. He can create a view based on the ORDERS table and the TESTS table. He may construct this table as shown in the following code example:
CREATE VIEW BIRTHDAY (ClientName, Test, OrderDate, BirthdayCharge) AS SELECT ClientName, TestOrdered, OrderDate, StandardCharge * .9 FROM ORDERS, TESTS WHERE TestOrdered = TestName ;
Notice that the second column in the BIRTHDAY view — Test — corresponds to the TestOrdered column in the ORDERS table, which also corresponds to the TestName column in the TESTS table.
You can build a view based on multiple tables, as shown in the preceding examples, or you can build a view based on a single table. If you don’t need some of the columns or rows, create a view to remove these elements and then deal with the view rather than the original table. This approach ensures that users see only the parts of the table that are relevant.
Another reason for creating a view is to provide security for its underlying tables. You may want to make some columns in your tables available for inspection while hiding others. You can create a view that includes only those columns that you want to make available and then grant broad access to that view while restricting access to the tables from which you draw the view.