Views

A view is a stored SELECT with a name—virtual table for simplifying repeated queries and tightening permissions without duplicating data.

CREATE VIEW

CREATE VIEW customer_spending AS
SELECT c.id,
       c.name,
       COUNT(o.id) AS order_count,
       COALESCE(SUM(o.total), 0) AS spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

SELECT * FROM customer_spending ORDER BY spent DESC;

Practice: Advanced features vary by engine—SQLite 3.25+ supports window functions; test your version with a simple query.

Views vs tables

Views do not store rows by default (except materialized views in Postgres and similar). Underlying table changes reflect immediately in view results.

Security and APIs

Grant SELECT on a view exposing safe columns instead of base tables with secrets. ORMs can map views like tables—still prefer migrations for view definitions.

Important interview questions and answers

1. Q: View vs CTE?
   A: View persists in schema for reuse; CTE is query-local.
2. Q: Updatable views?
   A: Some simple views allow INSERT/UPDATE—rules vary; often read-only in practice.

Self-check

1. What SQL statement defines a view?
2. Do views copy data to disk by default?

Tip: Grant SELECT on views to analytics users instead of exposing sensitive base tables.