The SQL 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 an SQL VIEW
The syntax for creating a VIEW in SQL is:
CREATE VIEW view_name AS SELECT columns FROM table WHERE predicates;
SQL View Creation - 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 SQL View (Create statement) 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 an SQL VIEW
You can modify the definition of a VIEW in SQL without dropping it by using the following syntax:
CREATE OR REPLACE VIEW view_name AS SELECT columns FROM table WHERE predicates;
SQL View Modify - 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';
This SQL View (Create/Replace statement) would update the definition of the SQL View without dropping it. If the SQL View did not yet exist, the SQL View would merely be created for the first time.
Dropping an SQL VIEW
The syntax for dropping a VIEW in SQL is:
DROP VIEW view_name;
SQL View Drop - Example
DROP VIEW sup_orders;
This SQL View (Drop statement) would drop/delete the SQL View called sup_orders.
Frequently Asked Questions
Question: Can you update the data in an SQL view?
Answer: A view in SQL 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 SQL View.
So, yes, you can update the data in an SQL View providing you have the proper privileges to the underlying SQL tables.
Question: Does the SQL View exist if the table is dropped from the database?
Answer: Yes, in Oracle, the SQL View continues to exist even after one of the tables (that the SQL View is based on) is dropped from the database. However, if you try to query the SQL View after the table has been dropped, you will receive a message indicating that the SQL View has errors.
If you recreate the table (the table that you had dropped), the SQL View will again be fine.