Views: Virtual Tables

A view is a saved query that acts like a virtual table. Views don't store data themselves—they dynamically retrieve data from underlying tables whenever queried. Think of a view as a "window" into your data: you create complex queries once (with joins, filters, calculations) and then query the view like it's a simple table. Views simplify complex queries, enhance security by restricting access to specific columns or rows, ensure consistency by centralizing business logic, and make your database more maintainable. Whether you're hiding sensitive data, providing simplified interfaces for reporting tools, or standardizing frequently-used queries, views are an essential database design tool.

📚 What You'll Learn

• What views are and why they're useful
• How to create views with CREATE VIEW
• How to query views like regular tables
• How to update and drop views
• The difference between updatable and non-updatable views
• How to use views for security and access control
• How views simplify complex queries
• Performance implications of views
• Best practices for using views

1. Understanding Views

View vs Table

Table (Physical):
- Stores actual data on disk
- Takes up storage space
- Data persists independently

View (Virtual):
- Stores only the query definition
- No data storage (just SQL code)
- Data comes from underlying tables
- Always reflects current data

Why Use Views?

• Simplification: Hide complex joins and logic
• Security: Restrict access to specific columns/rows
• Consistency: Centralize business logic
• Abstraction: Shield users from schema changes
• Reusability: Write complex queries once, use many times

2. Creating Views

Basic CREATE VIEW Syntax

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table
WHERE condition;

Simple View Example

-- Create a view of active products
CREATE VIEW active_products AS
SELECT 
    product_id,
    name,
    price,
    stock,
    category_id
FROM products
WHERE status = 'active';

-- Now query the view like a table
SELECT * FROM active_products;
SELECT name, price FROM active_products WHERE price > 50;

View with Joins

-- View combining orders with customer information
CREATE VIEW order_details AS
SELECT 
    o.order_id,
    o.order_date,
    o.total,
    o.status,
    c.customer_id,
    c.name AS customer_name,
    c.email AS customer_email,
    c.country
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

-- Query the view
SELECT * FROM order_details WHERE country = 'USA';
SELECT customer_name, SUM(total) FROM order_details GROUP BY customer_name;

View with Calculations

-- Customer summary view
CREATE VIEW customer_summary AS
SELECT 
    c.customer_id,
    c.name,
    c.email,
    COUNT(o.order_id) AS total_orders,
    COALESCE(SUM(o.total), 0) AS total_spent,
    COALESCE(AVG(o.total), 0) AS avg_order_value,
    MAX(o.order_date) AS last_order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name, c.email;

-- Use the view
SELECT * FROM customer_summary WHERE total_spent > 1000;
SELECT name, total_orders FROM customer_summary ORDER BY total_spent DESC LIMIT 10;

3. Using Views

Views Act Like Tables

-- All standard SELECT operations work on views

-- Simple SELECT
SELECT * FROM customer_summary;

-- With WHERE
SELECT * FROM customer_summary WHERE total_orders > 5;

-- With ORDER BY
SELECT * FROM customer_summary ORDER BY total_spent DESC;

-- With LIMIT
SELECT * FROM customer_summary LIMIT 10;

-- Join views with tables
SELECT 
    cs.name,
    cs.total_spent,
    o.order_date,
    o.total
FROM customer_summary cs
INNER JOIN orders o ON cs.customer_id = o.customer_id
WHERE cs.total_spent > 5000;

-- Join views with other views
SELECT 
    cs.name,
    cs.total_orders,
    od.order_id,
    od.status
FROM customer_summary cs
INNER JOIN order_details od ON cs.customer_id = od.customer_id;

4. Modifying and Dropping Views

Replacing a View

-- CREATE OR REPLACE updates the view definition
CREATE OR REPLACE VIEW active_products AS
SELECT 
    product_id,
    name,
    price,
    stock,
    category_id,
    created_date  -- Added new column
FROM products
WHERE status = 'active' 
    AND stock > 0;  -- Added additional filter

Altering a View (MySQL 5.7.6+)

-- ALTER VIEW (alternative to CREATE OR REPLACE)
ALTER VIEW customer_summary AS
SELECT 
    c.customer_id,
    c.name,
    c.email,
    c.country,  -- Added column
    COUNT(o.order_id) AS total_orders,
    SUM(o.total) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name, c.email, c.country;

Dropping a View

-- Delete a view
DROP VIEW active_products;

-- Drop if exists (no error if doesn't exist)
DROP VIEW IF EXISTS customer_summary;

-- Drop multiple views
DROP VIEW IF EXISTS view1, view2, view3;

5. Updatable Views

Requirements for Updatable Views

Updatable View Example

-- Simple updatable view
CREATE VIEW high_stock_products AS
SELECT 
    product_id,
    name,
    price,
    stock
FROM products
WHERE stock > 50;

-- You CAN update through this view
UPDATE high_stock_products 
SET price = price * 1.10 
WHERE product_id = 101;

-- You CAN insert through this view
INSERT INTO high_stock_products (name, price, stock)
VALUES ('New Product', 29.99, 100);

-- You CAN delete through this view
DELETE FROM high_stock_products WHERE product_id = 101;

Non-Updatable View Example

-- This view is NOT updatable (has aggregates)
CREATE VIEW product_stats AS
SELECT 
    category_id,
    COUNT(*) AS product_count,
    AVG(price) AS avg_price,
    SUM(stock) AS total_stock
FROM products
GROUP BY category_id;

-- This will FAIL
UPDATE product_stats SET avg_price = 50 WHERE category_id = 1;
-- Error: The target table product_stats of the UPDATE is not updatable

6. Using Views for Security

Hiding Sensitive Columns

-- Employees table has sensitive data
-- CREATE TABLE employees (
--     employee_id, name, email, salary, ssn, hire_date, department_id
-- )

-- Create view without sensitive columns
CREATE VIEW public_employees AS
SELECT 
    employee_id,
    name,
    email,
    hire_date,
    department_id
FROM employees;
-- salary and ssn are hidden

-- Grant access to view, not base table
GRANT SELECT ON public_employees TO 'readonly_user'@'localhost';
-- Users can query public_employees but not employees table

Row-Level Security

-- Show only orders from specific region
CREATE VIEW regional_orders AS
SELECT 
    o.order_id,
    o.order_date,
    o.total,
    c.name AS customer_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE c.region = 'West';

-- Regional manager only sees their region's data
GRANT SELECT ON regional_orders TO 'west_manager'@'localhost';

Role-Based Views

-- View for sales team (shows pricing info)
CREATE VIEW sales_products AS
SELECT 
    product_id,
    name,
    price,
    cost,
    price - cost AS profit_margin,
    stock
FROM products
WHERE status = 'active';

-- View for customers (hides costs)
CREATE VIEW customer_products AS
SELECT 
    product_id,
    name,
    price,
    category_id,
    CASE WHEN stock > 0 THEN 'In Stock' ELSE 'Out of Stock' END AS availability
FROM products
WHERE status = 'active';

7. Views for Query Simplification

Complex Join as View

-- Complex query: order details with all relationships
CREATE VIEW complete_order_details AS
SELECT 
    o.order_id,
    o.order_date,
    o.status,
    c.customer_id,
    c.name AS customer_name,
    c.email,
    p.product_id,
    p.name AS product_name,
    cat.name AS category_name,
    oi.quantity,
    oi.price AS unit_price,
    oi.quantity * oi.price AS line_total
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
INNER JOIN categories cat ON p.category_id = cat.category_id;

-- Now simple queries on complex data
SELECT * FROM complete_order_details WHERE customer_name = 'Alice';
SELECT category_name, SUM(line_total) FROM complete_order_details GROUP BY category_name;

Business Logic Encapsulation

-- View with business rules
CREATE VIEW profitable_products AS
SELECT 
    product_id,
    name,
    price,
    cost,
    stock,
    price - cost AS profit,
    (price - cost) * stock AS potential_revenue,
    CASE
        WHEN (price - cost) / price > 0.50 THEN 'High Margin'
        WHEN (price - cost) / price > 0.30 THEN 'Good Margin'
        WHEN (price - cost) / price > 0.15 THEN 'Acceptable'
        ELSE 'Low Margin'
    END AS margin_category
FROM products
WHERE status = 'active'
    AND price > cost;  -- Only profitable items

-- Simple queries using business logic
SELECT * FROM profitable_products WHERE margin_category = 'High Margin';
SELECT margin_category, AVG(profit) FROM profitable_products GROUP BY margin_category;

8. View Performance

How Views Execute

-- When you query a view:
SELECT * FROM customer_summary WHERE total_spent > 1000;

-- MySQL actually executes:
SELECT * FROM (
    -- The entire view definition runs
    SELECT 
        c.customer_id,
        c.name,
        COUNT(o.order_id) AS total_orders,
        SUM(o.total) AS total_spent
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id, c.name
) AS customer_summary
WHERE total_spent > 1000;

Optimization Strategies

Materialized Views (Workaround)

-- MySQL doesn't have built-in materialized views
-- But you can simulate them with tables + scheduled updates

-- Create a real table to store results
CREATE TABLE customer_summary_cache AS
SELECT 
    c.customer_id,
    c.name,
    COUNT(o.order_id) AS total_orders,
    SUM(o.total) AS total_spent,
    NOW() AS last_updated
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;

-- Add index for fast queries
CREATE INDEX idx_total_spent ON customer_summary_cache(total_spent);

-- Refresh periodically (using event scheduler or cron)
-- This is much faster to query than a view!

9. View Best Practices

Naming Conventions

-- Use clear, descriptive names
✅ customer_order_summary
✅ active_products
✅ monthly_sales_report

-- Optionally use prefix to identify views
✅ v_customer_summary
✅ vw_order_details

-- Avoid confusing names
❌ data
❌ temp
❌ view1

10. 🎯 Practice Exercises

products (product_id, name, price, cost, stock, category_id, status)
categories (category_id, name)
orders (order_id, customer_id, order_date, total, status)
customers (customer_id, name, email, country)

-- Task 1: Active products with categories
CREATE VIEW active_products_detailed AS
SELECT 
    p.product_id,
    p.name AS product_name,
    p.price,
    p.stock,
    c.category_id,
    c.name AS category_name
FROM products p
INNER JOIN categories c ON p.category_id = c.category_id
WHERE p.status = 'active';

-- Query the view
SELECT * FROM active_products_detailed WHERE price > 50;


-- Task 2: High-value orders with customer info
CREATE VIEW high_value_orders AS
SELECT 
    o.order_id,
    o.order_date,
    o.total,
    o.status AS order_status,
    c.customer_id,
    c.name AS customer_name,
    c.email,
    c.country
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE o.total > 500;

-- Query the view
SELECT * FROM high_value_orders WHERE country = 'USA';
SELECT customer_name, SUM(total) FROM high_value_orders GROUP BY customer_name;


-- Task 3: Product profit margins
CREATE VIEW product_profitability AS
SELECT 
    product_id,
    name,
    price,
    cost,
    stock,
    price - cost AS profit_per_unit,
    (price - cost) * stock AS total_potential_profit,
    ROUND((price - cost) / price * 100, 2) AS profit_margin_percent,
    CASE
        WHEN (price - cost) / price > 0.50 THEN 'High'
        WHEN (price - cost) / price > 0.30 THEN 'Medium'
        ELSE 'Low'
    END AS margin_category
FROM products
WHERE status = 'active'
    AND price > cost;

-- Query the view
SELECT * FROM product_profitability WHERE margin_category = 'High';
SELECT margin_category, AVG(profit_margin_percent) 
FROM product_profitability 
GROUP BY margin_category;

employees (employee_id, name, email, salary, ssn, department_id, manager_id)
departments (department_id, name, location)

-- Task 1: Public employee directory (hide sensitive data)
CREATE VIEW employee_directory AS
SELECT 
    e.employee_id,
    e.name,
    e.email,
    e.department_id,
    d.name AS department_name,
    d.location
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;
-- salary and ssn are hidden

-- Query the view
SELECT * FROM employee_directory WHERE department_name = 'Engineering';


-- Task 2: Department-specific view (HR department only)
CREATE VIEW hr_employees AS
SELECT 
    e.employee_id,
    e.name,
    e.email,
    e.salary,
    e.manager_id,
    d.name AS department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
WHERE d.name = 'HR';

-- Grant to HR manager
-- GRANT SELECT ON hr_employees TO 'hr_manager'@'localhost';


-- Task 3: Manager's direct reports view
CREATE VIEW manager_team AS
SELECT 
    m.employee_id AS manager_id,
    m.name AS manager_name,
    e.employee_id,
    e.name AS employee_name,
    e.email,
    d.name AS department_name
FROM employees e
INNER JOIN employees m ON e.manager_id = m.employee_id
INNER JOIN departments d ON e.department_id = d.department_id;

-- A manager can query for their team
SELECT * FROM manager_team WHERE manager_id = 101;

customers (customer_id, name, country, registration_date)
orders (order_id, customer_id, order_date, total, status)
order_items (item_id, order_id, product_id, quantity, price)
products (product_id, name, category_id)
categories (category_id, name)

-- Comprehensive customer analytics view
CREATE VIEW customer_analytics AS
SELECT 
    c.customer_id,
    c.name,
    c.email,
    c.country,
    c.registration_date,
    
    -- Order statistics
    COUNT(DISTINCT o.order_id) AS total_orders,
    COUNT(DISTINCT CASE WHEN o.status = 'completed' THEN o.order_id END) AS completed_orders,
    
    -- Revenue metrics
    COALESCE(SUM(CASE WHEN o.status = 'completed' THEN o.total END), 0) AS lifetime_value,
    COALESCE(AVG(CASE WHEN o.status = 'completed' THEN o.total END), 0) AS avg_order_value,
    COALESCE(MIN(CASE WHEN o.status = 'completed' THEN o.total END), 0) AS min_order_value,
    COALESCE(MAX(CASE WHEN o.status = 'completed' THEN o.total END), 0) AS max_order_value,
    
    -- Recency metrics
    MIN(o.order_date) AS first_order_date,
    MAX(o.order_date) AS last_order_date,
    DATEDIFF(CURRENT_DATE, MAX(o.order_date)) AS days_since_last_order,
    
    -- Product diversity
    COUNT(DISTINCT oi.product_id) AS unique_products_purchased,
    COUNT(DISTINCT cat.category_id) AS unique_categories_purchased,
    
    -- Top category
    (
        SELECT cat2.name
        FROM order_items oi2
        INNER JOIN products p2 ON oi2.product_id = p2.product_id
        INNER JOIN categories cat2 ON p2.category_id = cat2.category_id
        INNER JOIN orders o2 ON oi2.order_id = o2.order_id
        WHERE o2.customer_id = c.customer_id
            AND o2.status = 'completed'
        GROUP BY cat2.name
        ORDER BY SUM(oi2.quantity * oi2.price) DESC
        LIMIT 1
    ) AS favorite_category,
    
    -- Customer segmentation
    CASE
        WHEN COALESCE(SUM(CASE WHEN o.status = 'completed' THEN o.total END), 0) >= 10000 
            THEN 'Platinum'
        WHEN COALESCE(SUM(CASE WHEN o.status = 'completed' THEN o.total END), 0) >= 5000 
            THEN 'Gold'
        WHEN COALESCE(SUM(CASE WHEN o.status = 'completed' THEN o.total END), 0) >= 1000 
            THEN 'Silver'
        ELSE 'Bronze'
    END AS customer_tier,
    
    -- Activity status
    CASE
        WHEN MAX(o.order_date) >= DATE_SUB(CURRENT_DATE, INTERVAL 60 DAY) 
            THEN 'Active'
        WHEN MAX(o.order_date) >= DATE_SUB(CURRENT_DATE, INTERVAL 180 DAY) 
            THEN 'At Risk'
        WHEN MAX(o.order_date) IS NOT NULL 
            THEN 'Churned'
        ELSE 'Never Ordered'
    END AS activity_status

FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN order_items oi ON o.order_id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.product_id
LEFT JOIN categories cat ON p.category_id = cat.category_id

GROUP BY 
    c.customer_id, c.name, c.email, c.country, c.registration_date;


-- Now simple queries on complex analytics
SELECT * FROM customer_analytics WHERE customer_tier = 'Platinum';

SELECT country, AVG(lifetime_value) AS avg_ltv
FROM customer_analytics
GROUP BY country
ORDER BY avg_ltv DESC;

SELECT activity_status, COUNT(*) AS customer_count
FROM customer_analytics
GROUP BY activity_status;

📝 Key Takeaways

• Views are virtual tables defined by stored queries
• Views don't store data—they query base tables dynamically
• Create views with CREATE VIEW view_name AS SELECT...
• Query views exactly like regular tables
• Update views with CREATE OR REPLACE VIEW or ALTER VIEW
• Drop views with DROP VIEW view_name
• Updatable views allow INSERT/UPDATE/DELETE (with restrictions)
• Views with aggregates, GROUP BY, DISTINCT, or UNION are read-only
• Use views for security (hide columns/rows)
• Use views to simplify complex queries
• Use views to centralize business logic
• Views execute their query every time they're queried
• Index base tables for view performance
• Avoid views on views (nested complexity)
• Views provide abstraction and maintainability