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 stores only 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 are not copying data from the source tables to the view. You are looking at the original data.
A view often functions as a security device, because you can give people permissions on a view but not on the underlying tables. Thus, the people can access a defined part of the data (the part defined in the view), but the rest of the data remains private.
In the following exercise, you use the CREATE VIEW statement to create a phone list by choosing the employee number, first name, last name, and phone extension from the Employee table and the employee’s location and department phone number from the Department table. Views are frequently created to store an often-used query or set of queries in the database.
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 on views.
Creating the Phone_list View
1. 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 tells InterBase 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 row uniquely identifies a row in the Employee table. (In case you are wondering, the dept_no column in the Employee table is UNIQUE because all primary key columns automatically acquire the UNIQUE property.)
- Notice that when the same column name appears in two tables in a query, you reference the columns by giving both the table name and the column name, joined by a period:
table_name.column_name
2. Now look at the structure of the Phone_list view by selecting the EMPLOYEE table and then selecting Database > View Metadata. You should see the following output:
/* 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;
