Lesson 38: Views: Stored Queries for Security and Simplicity

A View is a virtual table whose contents are defined by a SELECT query. It does not store data physically; rather, it retrieves data from the underlying tables every time it is queried.

Benefits of Using Views

1. Security and Access Control

Views allow you to hide sensitive columns or rows from certain users. You can grant access to the view while denying direct access to the base table.

• Example: Creating a view for the HR department that excludes employee salary information.

2. Simplicity and Code Reusability

Views encapsulate complex logic (like multi-table joins or complex calculations) into a simple, named object. Instead of rewriting a complex join query every time, users simply query the view.

3. Data Independence

If the underlying table structure changes (e.g., a column name changes), you only need to update the view definition, and the application code querying the view remains unchanged.

Creating a View

sql CREATE VIEW ActiveEmployees AS SELECT EmployeeID, Name, Department FROM Employees WHERE Status = 'Active';

Querying a View

Once created, you treat the view exactly like a normal table:

sql SELECT Name, Department FROM ActiveEmployees WHERE Department = 'IT';