Database - Introduction to SQL - Discussion

Views can represent a subset of the data contained in a table. Consequently, a view can limit the degree of exposure of the underlying tables to the outer world: a given user may have permission to query the view while denied access to the rest of the base table.

Views can join and simplify multiple tables into a single virtual table.

Views can act as aggregated tables, where the database engine aggregates data (sum, average, etc. ) and presents the calculated results as part of the data.

Views can hide the complexity of data. For example, a view could appear as Sales2000 or Sales2001, transparently partitioning the actual underlying table.

Views take very little space to store; the database contains only the definition of a view, not a copy of all the data that it presents.

Depending on the SQL engine used, views can provide extra security.

A view is a virtual table which means, whose rows are not explicitly stored in the database but computed as needed.

For ex: Consider a student and course relations. Suppose we are often interested in finding the names of students who have joined in a particular course, we can define a view for this purpose

CREATE VIEW myview (name, sid, course)
AS SELECT S.sname, S.sid, C.course
FROM student S, course C;

There are two types of view:-

A) Simple view.

B) Complex view.

Simple View:

*Create view view_name

AS select * from table name where condition;

Complex View:

*Create view view_name (list of column names)

As write query.


Simple view:- It supports DML operation.

Complex view:- It does not support DML operation.

Its said as virtual table because it physically doesn't exist only the sql syntax which creates the virtual table is stored in the database. When the sql query is rendered at that time system creates a view of that table it is more secure than the direct accessible physical tables.

View can be of two categories:

1. Simple view.
2. Complex view.

We can restrict the dml's to be perform on the view using with read only option.

SQL> create view mvu as select empno, ename, sal, deptno from emp;

When any information changes in view does it get reflected in base table.

Can we change info in view and make the change in main base table also?

Will you please explain it in detail?

A view is a named table that is represented, not by its own physically separate stored data, but by its definition in terms of other named tables (base tables or views).

Virtual table is a table which does not occupy any space it link with main table. Which is create by view, we can perform any select statement on virtual table.

Virtual tables can have indices but that must be built into the virtual table implementation. They cannot be added separately using CREATE INDEX statements.

View is virtual database table, we can retrive the data from view by using sql commands.
By using view we can provide confidential to our data.