Skip to content

SQL Views (The Filtered Window) 🪟

Mentor's Note: A View is a "Virtual Table". It doesn't actually store data; it's just a saved SQL query. Think of it like a filtered window into your database. You see the data you need, but the messy details (like complex Joins) are hidden behind the frame. 💡

🌟 The Scenario: The Bank Teller 🏦

Imagine a bank's "Customers" table. It contains names, phone numbers, and Private PINs. - The Problem: You want the Tellers to see names and balances, but NEVER the PINs. - The View Solution: You create a View called teller_customer_view. - The Result: Tellers query the View. They think it's a real table, but they can only see what you've allowed through the window! 🛡️

💻 1. Creating a View

You can turn any complex SELECT statement into a View.

-- Scenario: Create a simple dashboard for active students
CREATE VIEW active_students_dashboard AS
SELECT id, name, email, join_date
FROM students
WHERE status = 'Active';

💻 2. Simplifying Joins 🕸️

Instead of writing a 10-line Join every morning, save it as a View!

-- Scenario: Hide the mess of Joining Employees and Departments
CREATE VIEW employee_dept_report AS
SELECT E.emp_name, D.dept_name, D.location
FROM employees E
JOIN departments D ON E.dept_id = D.id;

-- Now, just query the view!
SELECT * FROM employee_dept_report;

💻 3. Managing Views

Views can be updated (replaced) or deleted easily.

-- Replace the view with new logic
CREATE OR REPLACE VIEW active_students_dashboard AS
SELECT id, name, city FROM students WHERE status = 'Active';

-- Remove the view
DROP VIEW active_students_dashboard;

🎨 Visual Logic: View vs Table

graph TD
    A[(Physical Table: Raw Data)] --> B[VIEW: Saved Query Logic]
    B --> C[User Query: SELECT * FROM View]
    C -- "Query translated" --> A
    A -- "Filtered data returned" --> C

📊 Benefits of Views

  1. Security: Hide sensitive columns. 🛡️
  2. Simplicity: Turn complex joins into one-line queries. 🧹
  3. Consistency: Ensures everyone uses the same "Calculated Logic" (e.g., how to calculate Net Salary).

📈 Learning Path