What is view in sql?
What is view?
A view is, in essence, a virtual table. It does not physically exist. Rather, it is created by a query joining one or more tables. . A view is actually a composition of a table in the form of a predefined SQL query.
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 depend on the written SQL query to create a view.
How to create a view:
Creating a VIEW
The syntax for creating a view is:
CREATE VIEW view_name AS
SELECT columns
FROM table
WHERE predicates;
Example:
CREATE VIEW sub_employee AS
SELECT job.job_id, employee.name, employee.salary, employee. employee_id, employee.dept, FROM job, employee
WHERE job.job_id = employee.employee_id
and employee.dept = ‘software’;
This would create a virtual table based on the result set of the select statement. You can now query the view as follows:
SELECT *
FROM sub_employee;
How to update view:
Updating a view
You can update a view without dropping it by using the following syntax:
CREATE OR REPLACE VIEW view_name AS
SELECT columns
FROM table
WHERE predicates;
Example:
CREATE or REPLACE VIEW sup_orders AS
SELECT job.job_id, employee.name, employee.salary, employee. employee_id, employee.dept, FROM job, employee
WHERE job.job_id = employee.employee_id
and employee.dept = ‘software’;
How to drop view:
Dropping a view
The syntax for dropping a view is:
DROP VIEW view_name;
Example:
DROP VIEW sub_employee;