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;
Found Something Wrong in this Lesson?Report a Bug or Comment on This Lesson - Your input is what keeps Tizag improving with time! |