Five examples of defining view tables are provided in this section:

• Defining a view table to limit the columns to be searched
• Using search conditions to define a view table
• Defining a read-only view table
• Defining a view table from which duplications are eliminated
• Defining a view table from another view table

(a) Defining a view table to limit the columns to be searched

Figure 2-41 shows an example of defining a view table to limit the columns to be searched. In this example, view table V1 is derived from a stock table in a way that the columns that can be searched will not include the color column.

Figure 2-41 Example of defining a view table for limiting the columns to be searched

(b) Using search conditions to define a view table

Figure 2-42 shows an example of using search conditions to define a view table. In this example, a query is used to create view table V2 from a stock table and a sales table in order to determine which products at each branch have sold less than 10 items (quantity sold).

Figure 2-42 Example of using search conditions to define a view table

(c) Defining a read-only view table

Figure 2-43 shows an example of defining a read-only view table. In this example, a query is used to create a read-only view table V3 from a stock table to determine the products whose price is higher than the average price of all products; the retrieved information includes the product codes, product names, prices, and stock quantities.

Figure 2-43 Example of defining a read-only view table

(d) Defining a view table from which duplications are eliminated

Figure 2-44 shows an example defining a view table from which duplications are eliminated. In this example, view table V4 is created from a stock table; in view table V4, duplicated product names and prices are eliminated.

Figure 2-44 Example of defining a view table from which duplications are eliminated

(e) Defining a view table from another view table

Figure 2-45 shows an example of defining a view table from another view table. In this example, a query is used to define view table V5, which is to consist of the rows in view table V1, defined in (a) of Section 2.11(1) Defining view tables, that contain skirt as the product name.

Figure 2-45 Example of defining a view table from another view table

Figure 2-46 shows an example of manipulating a view table. In this example, the product name, branch, and sales total of the product with the highest sales total are retrieved from the view table (V2) defined in (1)(b) Using search conditions to define a view table. (The view table is specified in the SQL statement that specifies the subquery.)

Figure 2-46 Example of manipulating a view table