Bits of .NET

📊 Views = Virtual Tables

Complex queries repeated everywhere. Views simplify them. Create once, query like a table.

📝 Create View

CREATE VIEW active_users AS
SELECT id, name, email, created_at
FROM users
WHERE status = 'active'
AND last_login > '2024-01-01';

-- Query the view
SELECT * FROM active_users;

-- View with JOIN
CREATE VIEW order_summary AS
SELECT 
    u.name as customer_name,
    o.id as order_id,
    o.total as order_total,
    o.order_date
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed';

-- Query view
SELECT * FROM order_summary WHERE order_total > 100;

-- View with computed columns
CREATE VIEW user_stats AS
SELECT 
    id,
    name,
    age,
    CASE 
        WHEN age < 18 THEN 'Minor'
        WHEN age < 65 THEN 'Adult'
        ELSE 'Senior'
    END as age_group
FROM users;

-- View with aggregate
CREATE VIEW monthly_sales AS
SELECT 
    DATE_TRUNC('month', order_date) as month,
    COUNT(*) as order_count,
    SUM(total) as total_revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date);

🎯 View Management

-- Drop view
DROP VIEW active_users;

-- Replace view
CREATE OR REPLACE VIEW active_users AS
SELECT id, name, email
FROM users
WHERE status = 'active';

-- Materialized View (PostgreSQL)
CREATE MATERIALIZED VIEW sales_summary AS
SELECT 
    DATE_TRUNC('month', order_date) as month,
    SUM(total) as revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date);

-- Refresh materialized view
REFRESH MATERIALIZED VIEW sales_summary;

-- View Benefits
- Simplify complex queries
- Hide complexity
- Security (restrict columns)
- Reusability
- Performance (if materialized)

💡 View Benefits

• Simplify complex queries
• Hide complexity
• Security (restrict columns)
• Reusability
• Performance (if materialized)