Kishore Analytics Solutions: VIEWS

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.

Wednesday, January 9, 2013

VIEWS