A VIEW is a virtual table, through which a selective portion of the data from one or more tables can be seen. Views do not contain data of their own. They are used to restrict access to the database or to hide data complexity. A view is stored as a SELECT statement in the database. DML operations on a view like INSERT, UPDATE, DELETE affects the data in the original table upon which the view is based.
The Syntax to create a sql view is
For Example: to create a view on the product table the sql query would be like
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.
The Syntax to create a sql view is
CREATE VIEW view_name
AS
SELECT column_list
FROM table_name [WHERE condition]; For Example: to create a view on the product table the sql query would be like
CREATE VIEW view_product
AS
SELECT product_id, product_name
FROM product; 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;
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.