VIEWS

  SQL CREATE VIEW Statement

In SQL, a view is a virtual table based on the result-set of an SQL statement.

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.

SQL CREATE VIEW Syntax

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;

Note: A view always shows up-to-date data! The database engine recreates the data, using the view's SQL statement, every time a user queries a view.

SQL Updating a View

You can update a view by using the following syntax:

SQL CREATE OR REPLACE VIEW Syntax

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

SQL Dropping a View

You can delete a view with the DROP VIEW command.

SQL DROP VIEW Syntax

DROP VIEW view_name

Example.

CUSTOMERS table having the following records:

 ID         NAME              AGE                 ADDRESS        SALARY  

  1        Ramesh                 25                   Ahmedabad        2000.00

  2        Khilan                   25                   Delhi                  1500.00

  3         kaushik                23                    Kota                   2000.00

  4         Chaitali                25                    Mumbai             6500.00

  5        Hardik                  27                    Bhopal               8500.00

  6         Komal                 22                    MP                     4500.00

  7        Muffy                   24                    Indore                10000.00|

Q.1 Create view to display name and age of customer

CREATE VIEW CUSTOMERS_VIEW AS

SELECT name, age

FROM  CUSTOMERS;

SELECT * FROM CUSTOMERS_VIEW;

Output

 Name               Age

 Ramesh          25

 Khilan             25

 kaushik            23

 Chaitali           25

 Hardik             27

 Komal             22

 Muffy              24 


TYPES OF VIEWS :

1. Vertical view
2. Horizontal View

VERTICAL VIEW -
When view is manipulated vertically (using column selection) then it is termed as Vertical view.Vertical views are updatable, but we should not voilet the constraint of underlying parent table.

Example
Create view v2 as
select ID,NAME,Age 
from customers;

HORIZONTAL VIEW -
 When view is manipulated horizontally (using rows selection) then it is termed as Horizontal view.
horizontal views are updatable provided they satisfy the condition on basis of which view is created.

Example
Create view v3 as
select * 
from customers
where age=25;

CREATING VIEWS USING CHECK OPTION

In order to ensure that horizontal views only allow updates based on specific conditions ,we should end our create view statement "with  check option" 

Example

Create view v4 as 
select * 
from customer
where age=25
with check option;

Now in above example somebody inserts a new record into customers table and that new record does not satisfy view condition oracle will cause an error.

Insert into v4 values(8,'Goutam',26,'Pune',10000);

then with check option ensure that this value is not accepted since view was created with age=25.

GROUPED VIEW -
A view that is created by using group by clause,having clause grouping function or distinct clause is termed as grouped view.
Note:Grouped views are not updatable at all ,this is because update statement does not work with group by clause ,having clause,grouping functions and distinct Clause.

 Example
Create view v5 as (Age,number) as
select age,count(*)
from customers
group by deptno;

Select * from V5;

Output

AGE       NUMBER
25               3
23               1
27               1 
22               1
24               1

JOINED VIEWS 

A view that uses any type of join and displays content of diffeent tables together is termed as join view.

Example

Create view v6 as 
select ename,location
from emp,dept 
where emp.deptno=dept.deptno;

Select * from v6;

ADVANTAGES OF VIEWS

1. Security
2. Query simplicity
3. Structural simplicity.

DISADVANTAGES OF VIEWS

1. View updating-
The view updating rules are so strict and different for different types of views that 70% of times a view can not be updated at all.

2. Performance-
A view can lead to misconception of lack of performance because a view could take more time in processing than the underlying query.