Author: Shameer
•8:02 AM
SQL - View

             A SQL View is a virtual table, which is based on SQL SELECT query. A view consists of rows and columns just like a table,except that the real tables store data, while the views won’t store data. The difference between a view and a table is that views are definitions built on top of a single table or multiple tables (or on top of another view), and do not hold data themselves. If data is changing in the underlying table, the same change is reflected in the view. The view’s data is generated dynamically when the view is referenced. A view references one or more existing database tables or other views. In effect every view is a filter of the table data referenced in it and this filter can restrict both the columns and the rows of the referenced tables.

Views ensure the security of data by restricting access to the following data:

• Specific rows of the tables.
• Specific columns of the tables.
• Specific rows and columns of the tables.
• Rows fetched by using joins.
• Statistical summary of data in a given tables.
• Subsets of another view or a subset of views and tables.

Some common examples of views are:
• A subset of rows or columns of a base table.
• A union of two or more tables.
• A join of two or more tables.
• A statistical summary of base tables.
• A subset of another view, or some combination of views and base table.

CREATING VIEWS

A view can be created by using the CREATE VIEW statement

SYNTAX

CREATE VIEW view_name [column_name (s)] AS
SELECT column_name(s)
FROM table_name
WHERE condition [WITH CHECK OPTION]

column_name specifies the name of the column to be used in view. If the column_name option is not specified, then the view is created with the same columns as specified in the select_statement.

WITH CHECK OPTION forces the data modification statements to fulfill the criteria given in the SELECT statement defining the view. It also ensures that the data is visible after the modifications are made permanent.

A view can be referenced and used from another view, from a SQL query, and from stored procedure. You can reference a view as you would reference any real SQL database table:

SELECT * FROM view_name

Example

Customer_Info


Product_Info


     CREATE VIEW V_PRODUCT_SALES AS
      SELECT A1.Customer_Name Customer_Info, SUM (A2.Sales) Product_Info
        FROM Customer_Info A1, Product_Info A2
      WHERE A1.Product_Name = A2.Product_Name
GROUP BY A1.Customer_Name

To find the contents of the VIEW

SELECT * FROM V_PRODUCT_SALES



UPDATING A VIEW

SYNTAX


CREATE OR REPLACE VIEW view_name AS
 SELECT column_name(s)
   FROM table_name
 WHERE condition


DROPPING A VIEW

SYNTAX

DROP VIEW view_name

Advantages:

1. A view hides the complexity of the database tables from end users.
2. Views take very little space to store as it is not storing the actual data.
3. A view serves as a security mechanism. This ensures that users are able to retrieve and modify only the data seen by them. Users cannot see or access the remaining data in the underlying table.
4. A view can also serves as a mechanism to simplify query execution. Complex queries can be stored in the form as a view, and data from the view can be retrieved using simple queries.

Restrictions imposed on views:

• A view can be created only in the current database.
• The name of a view must follow the rules for identifiers and must not be the same as that of the base table.
• A view can be created only 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.
This entry was posted on 8:02 AM and is filed under . You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

4 comments:

On August 1, 2011 at 3:10 AM , DataDiary.com said...

thanks for your help and code and tips i get good idea from it....

 
On March 4, 2013 at 5:09 AM , Anonymous said...

What's Taking place i'm new to this, I stumbled upon this I have found It
absolutely useful and it has aided me out loads. I'm hoping to contribute & help different customers like its helped me. Good job.

Also visit my webpage :: graduate certificates

 
On August 11, 2015 at 12:24 AM , asitbangalorereviews said...

Good collection...
Mysql Interview Questions and Answers

 
On December 18, 2019 at 3:30 AM , yanmaneee said...

moncler outlet
goyard handbags
golden goose sneakers
golden goose
supreme clothing
supreme t shirt
golden goose
yeezy
yeezy shoes
golden goose