DBMS SQL Views ~ GNIITHELP

SQL View

A view in SQL is a logical subset of data from one or more tables. View is used to restrict data access.

Syntax for creating a View,

CREATE or REPLACE view view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

---

Example of Creating a View

Consider following Sale table,

oid	order_name	previous_balance	customer
11	ord1	2000	Alex
12	ord2	1000	Adam
13	ord3	2000	Abhi
14	ord4	1000	Adam
15	ord5	2000	Alex

SQL Query to Create View

CREATE or REPLACE view sale_view as select * from Sale where customer = 'Alex';

The data fetched from select statement will be stored in another object called sale_view. We can use create seperately and replace too but using both together works better.

---

Example of Displaying a View

Syntax of displaying a view is similar to fetching data from table using Select statement.

SELECT * from sale_view;

---

Force View Creation

force keyword is used while creating a view. This keyword force to create View even if the table does not exist. After creating a force View if we create the base table and enter values in it, the view will be automatically updated.

Syntax for forced View is,

CREATE or REPLACE force view view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

---

Update a View

Update command for view is same as for tables.

Syntax to Update a View is,

UPDATE view-name 
set value
WHERE condition;

If we update a view it also updates base table data automatically.

---

Read-Only View

We can create a view with read-only option to restrict access to the view.

Syntax to create a view with Read-Only Access

CREATE or REPLACE force view view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition with read-only

The above syntax will create view for read-only purpose, we cannot Update or Insert data into read-only view. It will throw an error.

---

Types of View

There are two types of view,

• Simple View
• Complex View

Simple View	Complex View
Created from one table	Created from one or more table
Does not contain functions	Contain functions
Does not contain groups of data	Contains groups of data