Creating the Phone-list view

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’re 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;
   
   

   

Advance To:

Creating Indexes