(Scroll to see more)
- SQL ALIASES
- SQL ALTER TABLE
- SQL AND
- SQL AND & OR
- SQL AVG
- SQL BETWEEN
- SQL COUNT
- SQL CREATE TABLE
- SQL CREATE TABLE AS
- SQL DATA TYPES
- SQL DELETE
- SQL DISTINCT
- SQL DROP TABLE
- SQL EXISTS
- SQL FROM
- SQL GLOBAL TEMP
- SQL GROUP BY
- SQL HAVING
- SQL IN
- SQL INSERT
- SQL INTERSECT
- SQL IS NOT NULL
- SQL IS NULL
- SQL JOIN
- SQL LIKE
- SQL LOCAL TEMP
- SQL MAX
- SQL MIN
- SQL MINUS
- SQL NOT
- SQL OR
- SQL ORDER BY
- SQL SELECT
- SQL SUM
- SQL UNION
- SQL UNION ALL
- SQL UPDATE
- SQL VIEW
- SQL WHERE
Learn how to create, update, and drop SQL VIEWS with syntax and examples.
The SQL VIEW is, in essence, a virtual table that does not physically exist. Rather, it is created by a SQL statement that joins one or more tables.
Create SQL VIEW
The syntax for the SQL CREATE VIEW Statement is:
CREATE VIEW view_name AS SELECT columns FROM tables WHERE conditions;
view_name is the name of the SQL VIEW that you wish to create.
Here is an example 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 example would create a virtual table based on the result set of the select statement. You can now query the SQL VIEW as follows:
SELECT * FROM sup_orders;
Update SQL VIEW
You can modify the definition of a SQL VIEW without dropping it by using the SQL CREATE OR REPLACE VIEW Statement.
The syntax for the SQL CREATE OR REPLACE VIEW Statement is:
CREATE OR REPLACE VIEW view_name AS SELECT columns FROM table WHERE conditions;
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 example would update the definition of the SQL VIEW called sup_orders without dropping it. If the SQL VIEW did not yet exist, the SQL VIEW would merely 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.
The syntax for the SQL DROP VIEW Statement is:
DROP VIEW view_name;
view_name is the name of the view that you wish to drop.
Here is an example of how to use the SQL DROP VIEW Statement:
DROP VIEW sup_orders;
This SQL DROP VIEW example would drop/delete the SQL VIEW called sup_orders.
Frequently Asked Questions
Question: Can you update the data in a 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 a 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.