Bookmark and Share

SQL - Views

SQL VIEWS are data objects, and like SQL Tables, they can be queried, updated, and dropped. A SQL VIEW is a virtual table containing columns and rows except that the data contained inside a view is generated dynamically from SQL tables and does not physically exist inside the view itself.

Advertise on Tizag.com

SQL Create View Code:

CREATE VIEW virtualInventory
AS 
SELECT * FROM inventory;

With a successful execution of this query, we have now created a view data object of the inventory table. The virtualInventory view is considered a data object (like a table) and is now accessible to us the developer. Views can be queried exactly like any other SQL table.

SQL View Code:

USE mydatabase;

SELECT *
FROM virtualInventory;

SQL Results:

idproductquantityprice
119" LCD Screen25179.99
2HP Printer989.99
3Pen780.99
4Stapler37.99
5Hanging Files3314.99
6Laptop16499.99

Even though a SQL VIEW is treated like a data object in SQL, no data is actually stored inside of the view itself. The view is essentially a dynamic SELECT query, and if any changes are made to the originating table(s), these changes will be reflected in the SQL VIEW automatically.

SQL Code:

USE mydatabase;

UPDATE inventory
SET price = '1.29'
WHERE product = 'Pen';

Execute the following query to verify the results:

SQL Verification Query Code:

USE mydatabase;

SELECT *
FROM virtualInventory
WHERE product = 'Pen';

SQL Results:

idproductquantityprice
3Pen781.29

SQL - Drop View

Views can also be removed by using the DROP VIEW command.

SQL Drop View:

USE mydatabase;

DROP VIEW virtualInventory;
Bookmark and Share




Found Something Wrong in this Lesson?

Report a Bug or Comment on This Lesson - Your input is what keeps Tizag improving with time!

Advertise Here

More Tutorials!
Microsoft Office Tutorials Artist Tutorials