Creating Views

A view is a named query you can select from like a table.

Views help reduce repeated SQL and provide a stable, readable interface over underlying tables.

Core Concepts

flowchart LR
  A[(Base tables)] --> B[CREATE VIEW ... AS SELECT ...]
  B --> C[(Stored view definition)]
  C --> D[Query view with SELECT]
  D --> E[Computed result rows]

Object	Stores data physically?	Purpose
Table	Yes	Persist records
View	No	Reuse query logic

Simple View

CREATE VIEW v_customers_basic AS
SELECT customer_id, name
FROM customers_vw;

Expected output
View created successfully (no result rows).

Joined View

CREATE VIEW v_customer_orders AS
SELECT
  o.order_id,
  c.customer_id,
  c.name,
  o.total
FROM orders_vw AS o
INNER JOIN customers_vw AS c
  ON o.customer_id = c.customer_id;

Expected output
Joined view created successfully.

Code Examples

-- Setup base tables used in this lesson.
CREATE TABLE customers_vw (
  customer_id INTEGER PRIMARY KEY,
  name        TEXT NOT NULL
);

CREATE TABLE orders_vw (
  order_id     INTEGER PRIMARY KEY,
  customer_id  INTEGER NOT NULL,
  total        REAL NOT NULL
);

INSERT INTO customers_vw (customer_id, name)
VALUES (1, 'Ada'), (2, 'Alan');

INSERT INTO orders_vw (order_id, customer_id, total)
VALUES (1001, 1, 60.0), (1002, 1, 40.0), (1003, 2, 75.0);

Expected output
Base tables created and populated.

-- Create a reusable joined view.
CREATE VIEW v_customer_orders AS
SELECT
  o.order_id,
  c.customer_id,
  c.name,
  o.total
FROM orders_vw AS o
INNER JOIN customers_vw AS c
  ON o.customer_id = c.customer_id;

Expected output
View v_customer_orders created.

-- Verify view definition from sqlite_schema.
SELECT name, type
FROM sqlite_schema
WHERE type = 'view' AND name = 'v_customer_orders';

name	type
v_customer_orders	view

SQLite-Specific Nuances

SQLite Nuance

SQLite stores view definitions as SQL text in sqlite_schema.

A view does not hold its own data rows; results are computed when queried.

Common Pitfalls / Best Practices

Pitfall

Defining views with SELECT * from base tables.

Schema changes can alter view output shape unexpectedly.

Best Practice

List explicit columns in view definitions to keep contracts stable.

Quick Challenge

Create a view v_high_value_orders that returns order_id, customer_id, and total for orders where total >= 70.

View Solution

CREATE VIEW v_high_value_orders AS
SELECT order_id, customer_id, total
FROM orders_vw
WHERE total >= 70;

Expected output
View created and ready for filtered reporting queries.