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;

Views

What Is a View?

• A view is nothing but a window of an existing table by which one can view and can also change the values in tables. 
• The main point aspect of view is views have no storage space. For this reason views are also called as virtual tables. 
• Thus in other words a view is a database object that gives a logical representation of one or more database tables.

Advantages of Views

• To restrict data access.
• To make complex queries easy.
• To provide data independence.
• To present different views of the same data.

Simple Views and Complex Views

Feature	Simple Views	Complex Views
Number of Tables	One	One or more
Contain Functions	No	Yes
Contain Group of Data	No	Yes