Programmings / SQL

SQL Views

SQL View is a virtual table based on the result-set of an SQL Statement. View have rows and columns like real table, but it doesn’t physically exist.

A view can contain all rows of a table or select rows from a table. A view can be created from one or many tables which depends on the written SQL query to create a view.

Views allow users to do the following:

  • Structure data in a way that users or classes of users find natural or intuitive.
  • Restrict access to the data such that a user can see and (sometimes) modify exactly what they need and no more.
  • Summarize data from various tables which can be used to generate reports.

Creating an SQL VIEW

Syntax:

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

Example:

Eg: Create a view for place look-up from tables employee_details table and  work_location table.

CREATE VIEW place_lookup AS
SELECT place
FROM employee_details
UNION
SELECT place
FROM work_location

Updating a VIEW

Syntax:

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

Example:

CREATE VIEW place_lookup AS
SELECT place,zip_code
FROM employee_details
UNION
SELECT place,zip_code
FROM work_location

Dropping a VIEW

You can delete a view with the DROP VIEW command.
Syntax:

DROP VIEW view_name

View populates data itself by the predefined query.

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s