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
CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition
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
CREATE OR REPLACE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition
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.
DROP VIEW view_name
View populates data itself by the predefined query.