Creating Views with the CREATE VIEW Statement

The CREATE VIEW statement in the following example creates the EMP_LOCATIONS table, which joins four tables. (For information about joins, see Selecting Data from Multiple Tables.)

Creating the EMP_LOCATIONS View with CREATE VIEW

CREATE VIEW EMP_LOCATIONS AS
SELECT e.EMPLOYEE_ID,
  e.LAST_NAME || ', ' || e.FIRST_NAME NAME,
  d.DEPARTMENT_NAME DEPARTMENT,
  l.CITY CITY,
  c.COUNTRY_NAME COUNTRY
FROM EMPLOYEES e, DEPARTMENTS d, LOCATIONS l, COUNTRIES c
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID AND
 d.LOCATION_ID = l.LOCATION_ID AND
 l.COUNTRY_ID = c.COUNTRY_ID
ORDER BY LAST_NAME;

Result:

View created.

Creating Views with the CREATE VIEW Statement

Related Topics