SQL: VIEWS

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.


Creating a VIEW


The syntax for creating a VIEW is:


CREATE VIEW view_name AS
SELECT columns
FROM table
WHERE predicates;


For Example:


CREATE VIEW sup_orders AS
SELECT suppliers.supplier_id, orders.quantity, orders.price
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id
and suppliers.supplier_name = 'IBM';


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 sup_orders;


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;


For Example:


CREATE or REPLACE VIEW sup_orders AS
SELECT suppliers.supplier_id, orders.quantity, orders.price
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id
and suppliers.supplier_name = 'Microsoft';


Dropping a VIEW


The syntax for dropping a VIEW is:


DROP VIEW view_name;


For Example:


DROP VIEW sup_orders;


Frequently Asked Questions



Question: Can you update the data in a view?


Answer: A view is created by joining one or more tables. When you update
record(s) in a view, it updates the records in the underlying tables that
make up the view.


So, yes, you can update the data in a view providing you have the proper
privileges to the underlying tables.





Question: Does the view exist if the table is dropped from the database?


Answer: Yes, in Oracle, the view continues to exist even after one of the
tables (that the view is based on) is dropped from the database. However,
if you try to query the view after the table has been dropped, you will
receive a message indicating that the view has errors.


If you recreate the table (that you had dropped), the view will again be
fine.