InterBase Quick Start: Part II - Creating Views

Go Up to InterBase Quick Start: Part II - Data Definition

A view is a virtual table that contains selected rows and columns from one or more tables or views. InterBase only stores the definition of a view. The contents of a view are essentially pointers to data in the underlying tables. When you create a view, you do not copy data from the source tables to the view, you just look at the original data.

A view often functions as a security device, because it allows you to give people permissions to see only the data that the view defines (as opposed to whole tables). Another common use case for views is to store an often-used query or set of queries in the database.

In this section you use the CREATE VIEW statement to create a phone list. The data that you combine in this view are the number, first name, last name, and phone extension of an employee from the Employee table and the location and department phone number of that employee from the Department table.

You can select from a view just as you can from a table. Other operations are more restricted. See Working with Views in the Data Definition Guide for more information about views.

Creating the Phone_list View

Enter the following statement to create the Phone_list view from selected columns in the Employee and Department tables.

CREATE VIEW phone_list
AS
  SELECT emp_no,
         first_name,
         last_name,
         phone_ext,
         location,
         phone_no
  FROM   employee,
         department
  WHERE  employee.dept_no = department.dept_no

The WHERE clause specifies how to connect the rows: the dept_no column in the Department table is a foreign key that references the dept_no column in the Employee table. Both columns are UNIQUE and NOT NULL, so the dept_no value in a Department table uniquely identifies a row in the Employee table.

Notice that when the same column name appears in two tables in the same query, you must reference the columns by specifying both the table name and the column name, joined by a period:

table_name.column_name

You can examine the Metadata for the Phone_list view to confirm that the definition is correct:

/* View: PHONE_LIST, Owner: TUTOR */

CREATE VIEW "PHONE_LIST" (
  "EMP_NO", 
  "FIRST_NAME", 
  "LAST_NAME", 
  "PHONE_EXT", 
  "LOCATION", 
  "PHONE_NO"
) AS

  SELECT emp_no,
         first_name,
         last_name,
         phone_ext,
         location,
         phone_no
  FROM   employee,
         department
  WHERE  employee.dept_no = department.dept_no
;

Advance To:

Creating Indexes