Views

• A view is a virtual table based on the result set of an SQL statement.

• A view is a data object which does not contain any data.

• Contents of the view are the result of a base table.

• They are operated just like a base table but don’t contain any data.

• The difference between a view and a table is that views are definitions built on top of other tables (or views).

• If data is changed in the underlying table, the view reflects the exact change.

• A view can be built on top of single or multiple tables.

Simple Syntax

Create view <viewname>  as <select query>

Example

use chandr34;

create view vw_album as select * from Chinook.Album where ArtistId > 8;

create view chandr34.vw_customer as select customer_id, customer_first_name 
from murach.customers;

select * from chandr34.vw_customer;

create View chandr34.vw_customer as 

select customer_id, concat_ws(' ',customer_first_name, customer_last_name) as customer_name 
from murach.customers;

View Inside a View

create view chandr34.vw_customer1 as select * from vw_customer;

Alter Views

Views can be used in SubQuery, Derived Tables.

Alter view <viewname> as <select query>

alter view vw_album as select * from Chinook.Album;

Alter View chandr34.vw_customer as 

select customer_id, concat_ws(',',customer_first_name, customer_last_name) as customer_name from murach.customers;

Drop Views

Drop view <viewname>;

drop view vw_album;