The CREATE VIEW statement, shown in Figure 14-2, is used to create a view. The statement assigns a name to the view and specifies the query that defines the view.
To create the view successfully, you must have permission to access all of the tables referenced in the query.
The CREATE VIEW statement can optionally assign a name to each column in the newly created view. If a list of column names is specified, it must have the same number of items as the number of columns produced by the query. Note that only the column names are specified; the data type, length, and other characteristics of each column are derived from the definition of the columns in the source tables. If the list of column names is omitted from the CREATE VIEW statement, each column in the view takes the name of the corresponding column in the query. The list of column names must be specified if the query includes calculated columns or if it produces two columns with identical names.
Although all views are created in the same way, in practice, different types of views are typically used for different purposes. The next few sections examine these types of views and give examples of the CREATE VIEW statement.
1. Horizontal Views
A common use of views is to restrict a user’s access to only selected rows of a table.
For example, in the sample database, you may want to let a sales manager see only the SALESREPS rows for salespeople in the manager’s own region. To accomplish this, you can define two views, as follows:
Create a view showing Eastern region salespeople.
CREATE VIEW EASTREPS AS
SELECT *
FROM SALESREPS
WHERE REP_OFFICE IN (11, 12, 13)
Create a view showing Western region salespeople.
CREATE VIEW WESTREPS AS
SELECT *
FROM SALESREPS
WHERE REP_OFFICE IN (21, 22)
Now you can give each sales manager permission to access either the EASTREPS or the WESTREPS view, denying them permission to access the other view and the SALESREPS table itself. This effectively gives the sales manager a customized view of the SALESREPS table, showing only salespeople in the appropriate region.
A view like EASTREPS or WESTREPS is often called a horizontal view. As shown in Figure 14-3, a horizontal view slices the source table horizontally to create the view.
All of the columns of the source table participate in the view, but only some of its rows are visible through the view. Horizontal views are appropriate when the source table contains data that relates to various organizations or users. They provide a private table for each user, composed only of the rows needed by that user.
Here are some more examples of horizontal views:
Define a view containing only Eastern region offices.
CREATE VIEW EASTOFFICES AS
SELECT *
FROM OFFICES
WHERE REGION = ‘Eastern’
Define a view for Sue Smith (employee number 102) containing only orders placed by customers assigned to her.
CREATE VIEW SUEORDERS AS
SELECT *
FROM ORDERS
WHERE CUST IN (SELECT CUST_NUM
FROM CUSTOMERS
WHERE CUST_REP = 102)
Define a view showing only those customers who have more than $30,000 worth of orders currently on the books.
CREATE VIEW BIGCUSTOMERS AS
SELECT *
FROM CUSTOMERS
WHERE 30000.00 < (SELECT SUM(AMOUNT)
FROM ORDERS
WHERE CUST = CUST NUM)
In each of these examples, the view is derived from a single source table. The view is defined by a SELECT * query and therefore has exactly the same columns as the source table. The WHERE clause determines which rows of the source table are visible in the view.
2. Vertical Views
Another common use of views is to restrict a user’s access to only certain columns of a table. For example, in the sample database, the order-processing department may need access to the employee number, name, and office assignment of each salesperson, because this information may be needed to process an order correctly. However, there is no need for the order-processing staff to see the salesperson’s year-to-date sales or quota. This selective view of the SALESREPS table can be constructed with the following view:
Create a view showing selected salesperson information.
CREATE VIEW REPINFO AS
SELECT EMPL_NUM, NAME, REP_OFFICE
FROM SALESREPS
By giving the order-processing staff access to this view and denying access to the SALESREPS table itself, access to sensitive sales and quota data is effectively restricted.
A view like the REPINFO view is often called a vertical view. As shown in Figure 14-4, a vertical view slices the source table vertically to create the view. Vertical views are commonly found where the data stored in a table is used by various users or groups of users. They provide a private table for each user, composed only of the columns needed by that user.
Here are some more examples of vertical views:
Define a view of the OFFICES table for the order-processing staff that includes the office’s city, office number, and region.
CREATE VIEW OFFICEINFO AS
SELECT OFFICE, CITY, REGION
FROM OFFICES
Define a view of the CUSTOMERS table that includes only customer names and their assignment to salespeople.
CREATE VIEW CUSTINFO AS
SELECT COMPANY, CUST_REP
FROM CUSTOMERS
In each of these examples, the view is derived from a single source table. The select list in the view definition determines which columns of the source table are visible in the view. Because these are vertical views, every row of the source table is represented in the view, and the view definition does not include a WHERE clause.
3. Row/Column Subset Views
When you define a view, SQL does not restrict you to purely horizontal or vertical slices of a table. In fact, the SQL language does not include the notion of horizontal and vertical views. These concepts merely help you to visualize how the view presents the information from the source table. It’s quite common to define a view that slices a source table in both the horizontal and vertical dimensions, as in this example:
Define a view that contains the customer number, company name, and credit limit of all customers assigned to Bill Adams (employee number 105).
CREATE VIEW BILLCUST AS
SELECT CUST_NUM, COMPANY, CREDIT_LIMIT
FROM CUSTOMERS
WHERE CUST_REP = 105
The data visible through this view is a row/column subset of the CUSTOMERS table. Only the columns explicitly named in the select list of the view and the rows that meet the search condition are visible through the view.
4. Grouped Views
The query specified in a view definition may include a GROUP BY clause. This type of view is called a grouped view, because the data visible through the view is the result of a grouped query. Grouped views perform the same function as grouped queries; they group related rows of data and produce one row of query results for each group, summarizing the data in that group. A grouped view makes these grouped query results into a virtual table, allowing you to perform further queries on them.
Here is an example of a grouped view:
Define a view that contains summary order data for each salesperson.
CREATE VIEW ORD_BY_REP (WHO, HOW_MANY, TOTAL, LOW, HIGH, AVERAGE) AS
SELECT REP, COUNT(*), SUM(AMOUNT), MIN(AMOUNT), MAX(AMOUNT),
AVG(AMOUNT)
FROM ORDERS
GROUP BY REP
As this example shows, the definition of a grouped view always includes a column name list. The list assigns names to the columns in the grouped view, which are derived from column functions such as SUM() and MIN(). It may also specify a modified name for a grouping column. In this example, the REP column of the ORDERS table becomes the WHO column in the ORD_BY_REP view.
Once this grouped view is defined, it can be used to simplify queries. For example, this query generates a simple report that summarizes the orders for each salesperson:
Show the name, number of orders, total order amount, and average order size for each salesperson.
Unlike a horizontal or vertical view, the rows in a grouped view do not have a one-to-one correspondence with the rows in the source table. A grouped view is not just a filter on its source table that screens out certain rows and columns. It is a summary of the source tables; therefore, a substantial amount of DBMS processing is required to maintain the illusion of a virtual table for grouped views.
Grouped views can be used in queries just like other, simpler views. A grouped view cannot be updated, however. The reason should be obvious from the example. What would it mean to update the average order size for salesrep number 105? Because each row in the grouped view corresponds to a group of rows from the source table, and because the columns in the grouped view generally contain calculated data, there is no way to translate the update request into an update against the rows of the source table. Grouped views thus function as read-only views, which can participate in queries but not in updates.
Grouped views are also subject to the SQL restrictions on nested column functions. Recall from Chapter 8 that nested column functions, such as:
MIN(MIN(A))
are not legal in SQL expressions. Although the grouped view hides the column functions in its select list from the user, the DBMS still knows about them and enforces the restriction. Consider this example:
For each sales office, show the range of average order sizes for all salespeople who work in the office.
SELECT REP_OFFICE, MIN(AVERAGE), MAX(AVERAGE)
FROM SALESREPS, ORD_BY_REP
WHERE EMPL_NUM = WHO
GROUP BY REP_OFFICE
Error: Nested column function reference
This query produces an error, even though it appears perfectly reasonable. It’s a two-table query that groups the rows of the ORD_BY_REP view based on the office to which the salesperson is assigned. But the column functions MIN() and MAX() in the select list cause a problem. The argument to these column functions, the AVERAGE column, is itself the result of a column function. The “actual” query being requested from SQL is:
SELECT REP_OFFICE, MIN(AVG(AMOUNT)), MAX(AVG(AMOUNT))
FROM SALESREPS, ORDERS
WHERE EMPL_NUM = REP
GROUP BY REP
GROUP BY REP_OFFICE
This query is illegal because of the double GROUP BY and the nested column functions. Unfortunately, as this example shows, a perfectly reasonable grouped SELECT statement may, in fact, cause an error if one of its source tables turns out to be a grouped view. There’s no way to anticipate this situation; you must just understand the cause of the error when SQL reports it to you.
5. Joined Views
One of the most frequent reasons for using views is to simplify multitable queries.
By specifying a two-table or three-table query in the view definition, you can create a joined view that draws its data from two or three different tables and presents the query results as a single virtual table. Once the view is defined, you can often use a simple single-table query against the view for requests that would otherwise each require a two-table or three-table join.
For example, suppose that Sam Clark, the vice president of sales, often runs queries against the ORDERS table in the sample database. However, Sam doesn’t like to work with customer and employee numbers. Instead, he’d like to be able to use a version of the ORDERS table that has names instead of numbers. Here is a view that meets Sam’s needs:
Create a view of the ORDERS table with names instead of numbers.
CREATE VIEW ORDER_INFO (ORDER_NUM, COMPANY, REP_NAME, AMOUNT) AS
SELECT ORDER_NUM, COMPANY, NAME, AMOUNT
FROM ORDERS, CUSTOMERS, SALESREPS
WHERE CUST = CUST_NUM
AND REP = EMPL_NUM
This view is defined by a three-table join. As with a grouped view, the processing required to create the illusion of a virtual table for this view is considerable. Each row of the view is derived from a combination of one row from the ORDERS table, one row from the CUSTOMERS table, and one row from the SALESREPS table.
Although it has a relatively complex definition, this view can provide some real benefits. Here is a query against the view that generates a report of orders, grouped by salesperson:
Show the total current orders for each company for each salesperson.
Note that this query is a single-table SELECT statement, which is considerably simpler than the equivalent three-table SELECT statement for the source tables:
SELECT NAME, COMPANY, SUM(AMOUNT)
FROM SALESREPS, ORDERS, CUSTOMERS
WHERE REP = EMPL_NUM
AND CUST = CUST_NUM
GROUP BY NAME, COMPANY
Similarly, it’s easy to generate a report of the largest orders, showing who placed them and who received them, with this query against the view:
Show the largest current orders, sorted by amount.
The view makes it much easier to see what’s going on in the query than if it were expressed as the equivalent three-table join. Of course, the DBMS must work just as hard to generate the query results for the single-table query against the view as it would to generate the query results for the equivalent three-table query. In fact, the DBMS must perform slightly more work to handle the query against the view.
However, for the human user of the database, it’s much easier to write and understand the single-table query that references the view.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.