Day 6: Virtual Tables (Views)

View Benefit	How it Works	Real-World Scenario
Simplicity	Wraps complex JOINs into a single query.	Instead of a 20-line JOIN, users just write `SELECT * FROM vw_Orders`.
Column-Level Security	Excludes specific columns from the SELECT statement.	Hiding the `Salary` or `SSN` column from regular staff.
Row-Level Security	Uses a WHERE clause to filter out specific rows.	Ensuring an IT manager can only see IT department employees.
Aggregate Security	Uses GROUP BY to show summaries instead of raw data.	Showing total sales per month without exposing individual customer receipts.

1. What is a View?

In simple terms, a View is just a saved SQL query. It acts exactly like a "virtual table". When you query a View, SQL Server dynamically runs the underlying query and fetches the live data from the real tables.

Important: A standard View does not store data itself. It only stores the query logic. If you update the main table, the View instantly reflects those changes!

2. Benefit 1: Simplifying Complex Queries

If you have a complex report that joins 5 different tables, you don't want to type that out every day. You can save that logic inside a View.

Creating the View:

CREATE VIEW vw_CustomerOrders AS
SELECT 
    C.CustomerName, 
    O.OrderDate, 
    P.ProductName
FROM Customers C
INNER JOIN Orders O ON C.CustomerID = O.CustomerID
INNER JOIN Products P ON O.ProductID = P.ProductID;

Using the View:

Now, anytime anyone needs that report, they just query the View as if it were a regular table:

SELECT * FROM vw_CustomerOrders;

3. Benefit 2: Column-Level Security

Views are fantastic for security. If you have an Employees table with sensitive data (like Salary or SSN), you can create a View that simply omits those columns. You then grant users access to the View, but block them from the main table.

CREATE VIEW vw_PublicEmployeeDirectory AS
SELECT 
    EmployeeID, 
    FirstName, 
    LastName, 
    Email, 
    Department 
    -- Notice Salary and SSN are intentionally left out!
FROM Employees;

4. Benefit 3: Row-Level Security

You can also secure data horizontally (by row). If you want regional managers to only see data for their specific region, you use a WHERE clause inside the View.

CREATE VIEW vw_EastRegionSales AS
SELECT 
    SaleID, 
    Salesperson, 
    Amount
FROM Sales
WHERE Region = 'East'; -- Locks the view to only East region rows

5. Benefit 4: Hiding Sensitive Info via Aggregation

Sometimes you need to let analysts see financial trends, but you legally cannot let them see individual user transactions. You can use a View with Aggregate functions (SUM, AVG) to hide the raw records and only show the safe, generalized totals.

CREATE VIEW vw_MonthlySalesSummary AS
SELECT 
    YEAR(OrderDate) AS SalesYear,
    MONTH(OrderDate) AS SalesMonth,
    SUM(TotalAmount) AS TotalRevenue
FROM Orders
GROUP BY 
    YEAR(OrderDate), 
    MONTH(OrderDate);

Now, an analyst can query vw_MonthlySalesSummary to see how the business is doing, without ever seeing who bought what!