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.

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:

id	product	quantity	price
1	19" LCD Screen	25	179.99
2	HP Printer	9	89.99
3	Pen	78	0.99
4	Stapler	3	7.99
5	Hanging Files	33	14.99
6	Laptop	16	499.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:

id	product	quantity	price
3	Pen	78	1.29

SQL - Drop View

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

SQL Drop View:

USE mydatabase;

DROP VIEW virtualInventory;

Posted in: SQL