You can create a virtual table by selecting specific rows and columns from a table that is actually stored in a database (called a base table). Such a virtual table is called a view table. By creating a view table that makes only specified columns available to the public, you can effectively protect the remainder of the data. In addition, the resulting reduction in the number of columns to be manipulated improves the operability of the table.

To create a view table, you must execute the CREATE VIEW of the definition SQL. Figure 3-20 shows an example of a view table.

Figure 3-20 Example of a view table

Explanation

The VSTOCK view table is created from the STOCK base table, and is composed of the product code (PCODE), stock quantity (SQUANTITY), and unit price (PRICE) columns and only those rows with Socks in the product name (PNAME) column. The columns are arranged in the order of product code, stock quantity, and unit price.

CREATE VIEW VSTOCK
   AS SELECT PCODE,SQUANTITY,PRICE
   FROM STOCK
   WHERE PNAME = N'Socks'

If the HiRDB External Data Access facility is being used

You can create a view table from a foreign table. However, you cannot update such a view table.