Basic about VIEW – using and example

This SQL tutorial explains how to create, update, and drop SQL VIEWS with syntax and examples.

Description

The SQL VIEW is, in essence, a virtual desk that does now not physically exist. Rather, it is created by a SQL statement that joins one or greater tables.

Create SQL VIEW

Syntax

The syntax for the CREATE VIEW announcement in SQL is:

CREATE VIEW view_name AS
  SELECT columns
  FROM tables
  [WHERE conditions];

view_name The identify of the SQL VIEW that you want to create. WHERE conditions Optional. The conditions that have to be met for the records to be included in the VIEW.

Example

Here is an instance of how to use the SQL CREATE VIEW:

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

This SQL CREATE VIEW instance would create a digital table based totally on the result set of the choose statement. You can now query the SQL VIEW as follows:

SELECT *
FROM sup_orders;

Update SQL VIEW

You can adjust the definition of a SQL VIEW barring shedding it via using the SQL CREATE OR REPLACE VIEW Statement.

Syntax

The syntax for the SQL CREATE OR REPLACE VIEW Statement is:

CREATE OR REPLACE VIEW view_name AS
  SELECT columns
  FROM table
  [WHERE conditions];

Example

Here is an example of how you would use the SQL CREATE OR REPLACE VIEW Statement:

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

This SQL CREATE OR REPLACE VIEW instance would replace the definition of the SQL VIEW called sup_orders without shedding it. If the SQL VIEW did now not yet exist, the SQL VIEW would in basic terms be created for the first time.

Drop SQL VIEW

Once a SQL VIEW has been created, you can drop it with the SQL DROP VIEW Statement.

Syntax

The syntax for the SQL DROP VIEW Statement is:

DROP VIEW view_name;

view_name The name of the view that you desire to drop.

Example

Here is an instance of how to use the SQL DROP VIEW Statement:

DROP VIEW sup_orders;

This SQL DROP VIEW instance would drop/delete the SQL VIEW known as sup_orders.

Frequently Asked Questions

Question: Can you update the data in a SQL VIEW?

Answer: A VIEW in SQL is created through joining one or greater tables. When you replace 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 a SQL VIEW imparting you have the suitable 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 primarily based on) is dropped from the database. However, if you strive to question the SQL VIEW after the desk 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.