What are SQL Views?

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

-- Using the view
SELECT * FROM active_users;
SELECT name FROM active_users WHERE id = 1;

CREATE VIEW order_summary AS
SELECT
  o.id AS order_id,
  u.name AS customer_name,
  o.created_at,
  o.total,
  COUNT(oi.id) AS item_count
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
GROUP BY o.id, u.name, o.created_at, o.total;

-- Use like a table
SELECT * FROM order_summary WHERE total > 100;

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

-- Drop view
DROP VIEW active_users;
DROP VIEW IF EXISTS active_users;

CREATE VIEW user_emails AS
SELECT id, email FROM users;

-- This updates the underlying table
UPDATE user_emails SET email = 'new@email.com' WHERE id = 1;