SQL Views | Types Of Views | Simple View | Complex View | Materialized View | Inline View

Views

What is a View?

•    A view is a logical table based on a table or another view.4
•    A view contains no data of its own but is like a window through which data from tables can          be viewed or changed.
•    The tables on which a view is based are called base tables. The view is stored as a SELECT                 statement in the data dictionary.
•     We can present logical subsets or combinations of data by creating views of tables.
  

Types of Views :

•   Simple View.
•   Complex View.
•   Materialized View.
•  Inline View.

Advantages of Views :

•       Views restrict access to the data because the view can display selective columns from the table.

•       Views can be used to make simple queries to retrieve the results of complicated queries. For example, views can be used to query information from multiple tables without the user knowing how to write a join statement.

•       Views provide data independence for ad hoc users and application programs. One view can be used to retrieve data from several tables.

•       Views provide groups of users access to data according to their particular criteria.

VIEW SYNTAX

Syntax:

CREATE [OR REPLACE] VIEW view_name AS

SELECT column1, column2, ...

FROM table_name

WHERE condition;

 

Example: CREATE VIEW employee_view AS

     SELECT employee_id, first_name, last_name, salary

      FROM employees

                                WHERE department_id = 10;

Creating a View :

Example:

                   CREATE VIEW empvu80

                          AS SELECT emp_id, last_name, salary

                                 FROM XX_EMP_EMPID

                               WHERE department_id = 80;

 

Guidelines for creating a view:

    - The sub-query that defines a view can contain complex SELECT   

        syntax, including joins, groups, and sub-queries.

     - The sub-query that defines the view cannot contain an ORDER BY clause. The ORDER BY clause is specified when you retrieve data from the view.

     -  You can use the OR REPLACE option to change the definition of the  

        view without dropping and re-creating it or re-grant object privileges previously granted on it.

Dropping 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;

In the syntax:

              "view" is the name of the view

The statement removes the view definition from the database. Dropping views has no effect on the tables on which the view was based.

Views or other applications based on deleted views become invalid. Only the creator or a user with the DROP ANY VIEW privilege can remove a view.

 

1.Simple View :

Simple View is a View which is created from only one Base tables.

DML operations performed on simple view:

•       If simple View having GROUP Functions, Group By Clause, ROWNUM, DISTINCT, SET Operators, Joins then We can’t perform DML operations through simple views to base tables.

•       We must include ‘Base table’ NOT NULL column then only we perform insertion operation.

Example: CREATE VIEW simple_view AS

                  SELECT employee_id, first_name, last_name, salary

                                FROM employees

                  WHERE department_id = 10;

 

2.Complex View :

Complex View is a View which is created from multiple Base tables.

DML operations on complex view:

•       We can’t perform DML operations through Complex views to base tables.

•       When we trying to perform DML operations through complex views to base tables then some table columns are affected and some other are not affected.

To overcome this problem Oracle introduced Instead of triggers in PL/SQL.

Example:  CREATE VIEW employee_sales_view AS

                  SELECT e.employee_id,

                                e.first_name || ' ' || e.last_name AS full_name,  e.salary,

                                d.department_name,

 NVL(SUM(s.amount), 0) AS total_sales

FROM employees e

JOIN departments d ON e.department_id = d.department_id

LEFT JOIN sales s ON e.employee_id = s.employee_id

GROUP BY e.employee_id, e.first_name, e.last_name, e.salary, d.department_name;

 

3.Materialized View :

•       A materialized view takes the regular view described above and materializes it by proactively computing the results and storing them in a “virtual” table.

•       A view can be “materialized” by storing the tuples of the view in the database. Index structures can be built on the materialized view.

•       Consequently, database accesses to the materialized view can be much faster than recomputing the view. A materialized view is like a cache --- a copy of the data that can be accessed quickly.

•       If a regular view is a saved query, a materialized view is a saved query plus its results stored as a table.

Example:

CREATE MATERIALIZED VIEW user_purchase_summary AS SELECT

  u.id as user_id,

  COUNT(*) as total_purchases,

  SUM(CASE when p.status = 'cancelled' THEN 1 ELSE 0 END) as cancelled_purchases

FROM users u

JOIN purchases p ON p.user_id = u.id;

 

4.Inline-view :

·       An inline view is a SELECT statement in the FROM-clause of another SELECT statement to create a temporary table that could be referenced by the SELECT statement.

·       The sub-query specified in the FROM clause of a query is called an inline view.

    Syntax:

             Select * from (sub-query or select statement);

 

SELECT e.employee_id, e.first_name, e.last_name, e.salary, e.department_id

FROM employees e

JOIN (

    SELECT department_id, AVG(salary) AS avg_salary

    FROM employees

    GROUP BY department_id

) dept_avg ON e.department_id = dept_avg.department_id

WHERE e.salary > dept_avg.avg_salary;