Transcript of "Views"

1.
VIEW

2.
•
•
•
•
•
•
•
•
VIEW is a virtual table, which acts like a table but actually it contains no data. That
is based on the result set of a SELECT statement. A VIEW consists rows and
columns from one or more than one tables. A VIEW is a query that?s stored as an
object. A VIEW is nothing more than a way to select a subset of table?s columns.
When you defined a view then you can reference it like any other table in a
database. A VIEW provides as a security mechanism also. VIEWS ensures that users
are able to modify and retrieve only that data which seen by them.
By using Views you can ensure about the security of data by restricting access to
the following data:
Specific columns of the tables.
Specific rows of the tables.
Specific rows and columns of the tables.
Subsets of another view or a subset of views and tables
Rows fetched by using joins.
Statistical summary of data in a given tables.

3.
CREATE VIEW Statement
•
•
•
•
•
•
•
CREATE VIEW Statement is used to create a new database view. The general
syntax of CREATE VIEW Statement is:
CREATE VIEW view_name [(column_list)] [WITH ENCRYPTION] AS
select_statement [WITH CHECK OPTION]
View_name specifies the name for the new view.
column_list specifies the name of the columns to be used in view.
column_list must have the same number of columns that specified in
select_statement. If column_list option is not available then view is created
with the same columns that specified in select_statement.
WITH ENCRYPTION option encrypts the text to the view in the syscomments
table.
AS option specifies the action that is performed by the view.
select_statement is used to specify the SELECT statement that defines a view.
The optional WITH CHECK OPTION clause applies to the data modification
statement like INSERT and UPDATE statements to fulfill the criteria given in the
select_statement defining the view. This option also ensures that the data can
visible after the modifications are made permanent.

4.
Rules for views
• A view can be created only in the current database.
• The view name must follow the rules for identifiers and
• The view name must not be the same as that of the base
table
• A view can be created only that time if there is a SELECT
permission on its base table.
• A SELECT INTO statement cannot be used in view
declaration statement.
• A trigger or an index cannot be defined on a view.
• The CREATE VIEW statement cannot be combined with
other SQL statements in a single batch.

7.
DROP VIEW Statement
• For dropping a view you can use the DROP VIEW
Statement. When view is dropped but it has no
effect on the underlying tables. After dropping a
view if you issue any query that reference a
dropped view then you get an error message. But
dropping a table that reference any view does not
drop the view automatically you have to dropt
the view explicitly. The general syntax of DROP
VIEW Statement is :