CREATE VIEW view_name AS
SELECT columns
FROM tables
WHERE conditions;

CREATE VIEW active_customers AS
SELECT customer_id, first_name, last_name, email
FROM customers
WHERE status = 'active';
 
-- Query it like a table
SELECT * FROM active_customers;

CREATE VIEW book_details AS
SELECT
    b.book_id,
    b.title,
    b.price,
    a.name AS author_name,
    a.nationality
FROM books b
JOIN authors a ON b.author_id = a.author_id;
 
-- Now this complex join is hidden:
SELECT * FROM book_details WHERE nationality = 'British';

CREATE VIEW order_summary AS
SELECT
    o.order_id,
    o.order_date,
    c.first_name,
    c.last_name,
    COUNT(oi.book_id) AS item_count,
    SUM(oi.quantity * oi.unit_price) AS total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id, o.order_date, c.first_name, c.last_name;

-- Select specific columns
SELECT order_id, total_amount FROM order_summary;
 
-- Filter results
SELECT * FROM order_summary WHERE total_amount > 100;
 
-- Join with other tables or views
SELECT
    os.order_id,
    os.total_amount,
    bd.title
FROM order_summary os
JOIN order_items oi ON os.order_id = oi.order_id
JOIN book_details bd ON oi.book_id = bd.book_id;
 
-- Aggregate view data
SELECT
    first_name,
    last_name,
    COUNT(*) AS order_count,
    SUM(total_amount) AS lifetime_value
FROM order_summary
GROUP BY first_name, last_name;

-- MySQL, SQL Server
ALTER VIEW active_customers AS
SELECT customer_id, first_name, last_name, email, phone
FROM customers
WHERE status = 'active';

-- PostgreSQL, MySQL, Oracle
CREATE OR REPLACE VIEW active_customers AS
SELECT customer_id, first_name, last_name, email, phone, created_at
FROM customers
WHERE status = 'active';

DROP VIEW active_customers;
 
-- Drop only if exists
DROP VIEW IF EXISTS active_customers;

-- This view is updatable
CREATE VIEW customer_contacts AS
SELECT customer_id, first_name, last_name, email, phone
FROM customers;
 
-- You can update through the view
UPDATE customer_contacts
SET phone = '555-1234'
WHERE customer_id = 1;
 
-- Insert through the view
INSERT INTO customer_contacts (first_name, last_name, email)
VALUES ('New', 'Customer', 'new@example.com');
 
-- Delete through the view
DELETE FROM customer_contacts WHERE customer_id = 99;

CREATE VIEW active_customers AS
SELECT customer_id, first_name, last_name, email, status
FROM customers
WHERE status = 'active'
WITH CHECK OPTION;
 
-- This works
UPDATE active_customers SET email = 'newemail@example.com' WHERE customer_id = 1;
 
-- This fails - would make row invisible to view
UPDATE active_customers SET status = 'inactive' WHERE customer_id = 1;
-- Error: CHECK OPTION failed

-- Users can see customer info but not payment details
CREATE VIEW public_customers AS
SELECT customer_id, first_name, last_name, city, country
FROM customers;
-- Excludes: email, phone, credit_card, ssn
 
GRANT SELECT ON public_customers TO reporting_team;
REVOKE SELECT ON customers FROM reporting_team;

CREATE VIEW sales_dashboard AS
SELECT
    DATE(o.order_date) AS sale_date,
    COUNT(DISTINCT o.order_id) AS orders,
    COUNT(DISTINCT o.customer_id) AS unique_customers,
    SUM(oi.quantity) AS items_sold,
    SUM(oi.quantity * oi.unit_price) AS revenue,
    AVG(o.total_amount) AS avg_order_value
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.status = 'completed'
GROUP BY DATE(o.order_date);
 
-- Analysts just query this:
SELECT * FROM sales_dashboard
WHERE sale_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY);

CREATE VIEW book_inventory_status AS
SELECT
    book_id,
    title,
    stock_quantity,
    CASE
        WHEN stock_quantity = 0 THEN 'Out of Stock'
        WHEN stock_quantity < 10 THEN 'Critical'
        WHEN stock_quantity < 25 THEN 'Low'
        WHEN stock_quantity < 50 THEN 'Normal'
        ELSE 'Well Stocked'
    END AS stock_status,
    CASE
        WHEN stock_quantity < 10 THEN TRUE
        ELSE FALSE
    END AS needs_reorder
FROM books;

-- Old structure: single 'name' column
-- New structure: 'first_name' + 'last_name' columns
 
-- Create view matching old structure
CREATE VIEW customers_legacy AS
SELECT
    customer_id,
    CONCAT(first_name, ' ', last_name) AS name,
    email,
    phone
FROM customers;
 
-- Old applications continue working
SELECT name FROM customers_legacy WHERE customer_id = 1;

CREATE MATERIALIZED VIEW monthly_sales AS
SELECT
    DATE_TRUNC('month', order_date) AS month,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_revenue
FROM orders
WHERE status = 'completed'
GROUP BY DATE_TRUNC('month', order_date);

-- Full refresh (replaces all data)
REFRESH MATERIALIZED VIEW monthly_sales;
 
-- Concurrent refresh (allows queries during refresh)
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;

-- Create with SCHEMABINDING
CREATE VIEW dbo.order_totals
WITH SCHEMABINDING
AS
SELECT
    customer_id,
    COUNT_BIG(*) AS order_count,
    SUM(total_amount) AS total_spent
FROM dbo.orders
GROUP BY customer_id;
 
-- Create clustered index to materialize
CREATE UNIQUE CLUSTERED INDEX idx_order_totals
ON dbo.order_totals(customer_id);

-- PostgreSQL: What tables does a view use?
SELECT DISTINCT
    cl.relname AS view_name,
    dep.relname AS depends_on
FROM pg_depend d
JOIN pg_class cl ON d.objid = cl.oid
JOIN pg_class dep ON d.refobjid = dep.oid
WHERE cl.relkind = 'v' AND dep.relkind = 'r';
 
-- MySQL: View definition
SELECT TABLE_NAME, VIEW_DEFINITION
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = 'your_database';

-- This fails if views reference the table
DROP TABLE books;
 
-- This drops the table AND dependent views
DROP TABLE books CASCADE;

CREATE VIEW customer_360 AS
SELECT
    c.customer_id,
    c.first_name,
    c.last_name,
    c.email,
    c.created_at AS member_since,
    COALESCE(os.total_orders, 0) AS total_orders,
    COALESCE(os.total_spent, 0) AS total_spent,
    COALESCE(os.avg_order_value, 0) AS avg_order_value,
    os.last_order_date,
    CASE
        WHEN os.last_order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY) THEN 'Active'
        WHEN os.last_order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY) THEN 'At Risk'
        WHEN os.total_orders > 0 THEN 'Churned'
        ELSE 'Never Ordered'
    END AS customer_status
FROM customers c
LEFT JOIN (
    SELECT
        customer_id,
        COUNT(*) AS total_orders,
        SUM(total_amount) AS total_spent,
        AVG(total_amount) AS avg_order_value,
        MAX(order_date) AS last_order_date
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
) os ON c.customer_id = os.customer_id;

-- Category hierarchy view
CREATE RECURSIVE VIEW category_tree AS
SELECT
    category_id,
    name,
    parent_id,
    name AS full_path,
    0 AS level
FROM categories
WHERE parent_id IS NULL
 
UNION ALL
 
SELECT
    c.category_id,
    c.name,
    c.parent_id,
    CONCAT(ct.full_path, ' > ', c.name),
    ct.level + 1
FROM categories c
JOIN category_tree ct ON c.parent_id = ct.category_id;

-- Structure data for API responses
CREATE VIEW api_book_response AS
SELECT
    b.book_id AS id,
    b.title,
    b.price,
    b.isbn,
    JSON_OBJECT(
        'id', a.author_id,
        'name', a.name,
        'nationality', a.nationality
    ) AS author,
    JSON_OBJECT(
        'quantity', b.stock_quantity,
        'available', b.stock_quantity > 0
    ) AS availability
FROM books b
JOIN authors a ON b.author_id = a.author_id;