SQL — Views

Creating views

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

SELECT * FROM active_users;

Views with joins

CREATE VIEW user_orders AS
SELECT
  u.name,
  u.email,
  COUNT(o.id) AS order_count,
  SUM(o.total) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name, u.email;

SELECT * FROM user_orders WHERE order_count > 5;

Modifying views

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

Dropping views

DROP VIEW IF EXISTS active_users;

Materialized views

-- PostgreSQL
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT
  DATE_TRUNC('month', order_date) AS month,
  SUM(total) AS sales
FROM orders
GROUP BY 1;

-- Refresh
REFRESH MATERIALIZED VIEW monthly_sales;

Views with check option

CREATE VIEW young_users AS
SELECT * FROM users WHERE age < 30
WITH CHECK OPTION;

-- This will fail:
INSERT INTO young_users (name, age) VALUES ('Bob', 35);

Mini Practice

Write SQL code that:

1. Creates a simple view
2. Creates a view with joins
3. Modifies a view with CREATE OR REPLACE
4. Creates a materialized view

Up Next

In the next lesson, you'll learn about Stored Procedures — reusable SQL code.