Creating a View

Creating a View

•   You embed a subquery in the CREATE VIEW statement:

         CREATE [OR REPLACE] [TEMP|TEMPORARY  ] VIEW view
         [(column_name,..)] AS subquery

• The subquery can contain complex SELECT syntax.
•  Create the EMPVU80 view, which contains details of employees in department 80:

        CREATE VIEW empvu80  AS SELECT employee_id, last_name, salary
        FROM employees WHERE  department_id = 80;

• Describe the structure of the view by using the psql \d command:

       \d empvu80

Retrieving Data from a View

SELECT * FROM   empvu80;


Creating a Complex View

• Create a complex view that contains group functions to display values from two tables:

    CREATE OR REPLACE VIEW dept_sum_vu (name, minsal, maxsal, avgsal)
    AS SELECT   d.department_name, MIN(e.salary), MAX(e.salary),AVG(e.salary)
    FROM     employees e JOIN departments d
    ON       (e.department_id = d.department_id)
    GROUP BY d.department_name;

Removing a View

• You can remove a view without losing data because a view is based on underlying tables in the database.

       Syntax :
       DROP VIEW view;

      Example :
      DROP VIEW empvu80;