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.

If we delete some info in virtual table will it also get deleted from base table?

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?

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.

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 tables can have indices but that must be built into the virtual table implementation. They cannot be added separately using CREATE INDEX statements.

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.

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.

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.