InterBase Quick Start: Part II - Creating Views
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
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.
- Enter the following statement to create the
Phone_listview from selected columns in the
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
WHEREclause specifies how to connect the rows: the
dept_nocolumn in the
Departmenttable is a foreign key that references the
dept_nocolumn in the
Employeetable. Both columns are
NOT NULL, so the
dept_novalue in a
Departmenttable uniquely identifies a row in the
- 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:
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 ;