SQL Views

SQL 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 SQLTest AS

  SELECT *  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;

Alter/Updating an SQL VIEW

You can modify the definition of a VIEW in SQL without dropping it by using the following syntax:

ALTER VIEW view_name AS

  SELECT columns

  FROM table

  WHERE predicates;

SQL View Modify - Example

ALTER 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.