Virtual table in SQL in Database
A view is a single table that is derived from other tables.These other tables can be base tables or previously defined views.A view does not necessarily exit in physical form.In contrast to base tables whose tuples are always physically stored in the database.A view as a way of specifying a table that need to reference frequently even through it may not exist physically.
Syntax creation of view in SQL
create view view-name AS SELECT Attribute-name FROM Table-name Where Condition.
Example
create view report as select roll-no,name from student where roll>10.
A view is supposed to be always up to date if modify the tuples in the base tables on which the view is defined the view must automatically reflect these changes.Hence the view is not realized or materialized at the time of view definition but rather at the time when we specify a query on the view.It is the responsibility of the DBMS and not the user to make sure that the view is kept up to date.
If we do not need a view any more we can use the DROP VIEW command to dispose of it.
DROP VIEW ABC;
A view with a single defining table is updatable if the view attributes contain the primary key of the base relation as well as all attributes with the NOT NULL constraint that do not have default values specified.A view defined on multiple table using joins are generally not updatable. Views defined using grouping and aggregate functions are not updateable.
In sql the clause WITH CHECK option must be added at the end of the view definition if a view is to be updated.This allows the system to check for view updatability and to plan an execution strategy for the view updates.
It is also possible to define a view table in the FORM clause of an SQL query.This is known as an in-line view.In this case the view is defined within the query itself.
0 Comments