SQLite CREATE VIEW

A view is a saved SELECT query stored in the database under a name. Querying a view looks exactly like querying a regular table, but the data comes from the underlying query being executed on the fly. Views simplify complex queries, hide sensitive columns, and provide consistent naming for frequently used data shapes.

The View Analogy

PHYSICAL TABLE: raw_sales
─────────────────────────────────────────────────
id │ rep │ region │ product │ amount │ commission_pct

VIEW: rep_summary (saved query)
─────────────────────────────────────────────────
SELECT rep,
       COUNT(*) AS deals,
       SUM(amount) AS total,
       SUM(amount * commission_pct) AS earned
FROM raw_sales
GROUP BY rep;

Using the view:                Actual execution:
──────────────────────────     ─────────────────────────────────────
SELECT * FROM rep_summary; →   Run the GROUP BY query automatically
                               Return the computed result

CREATE VIEW Syntax

CREATE VIEW view_name AS
SELECT ...;

-- Safe creation (no error if already exists in SQLite 3.35+):
CREATE VIEW IF NOT EXISTS view_name AS
SELECT ...;

Sample Tables

CREATE TABLE employees (
  id      INTEGER PRIMARY KEY,
  name    TEXT,
  dept_id INTEGER,
  salary  REAL,
  active  INTEGER
);

CREATE TABLE departments (
  id   INTEGER PRIMARY KEY,
  name TEXT
);

INSERT INTO departments VALUES (1,'Engineering'),(2,'Marketing'),(3,'HR');
INSERT INTO employees VALUES
  (1,'Alice',1,85000,1),(2,'Bob',2,62000,1),
  (3,'Carol',1,91000,0),(4,'Dan',3,55000,1),(5,'Eve',2,70000,1);

Creating a View

-- View: active employees with their department name
CREATE VIEW IF NOT EXISTS active_employees AS
SELECT e.id,
       e.name,
       d.name AS dept,
       e.salary
FROM employees e
JOIN departments d ON e.dept_id = d.id
WHERE e.active = 1;

-- Use it exactly like a table:
SELECT * FROM active_employees;

id │ name  │ dept        │ salary
───┼───────┼─────────────┼────────
1  │ Alice │ Engineering │ 85000
2  │ Bob   │ Marketing   │ 62000
4  │ Dan   │ HR          │ 55000
5  │ Eve   │ Marketing   │ 70000

Querying a View Like a Table

-- Filter the view
SELECT name, salary FROM active_employees
WHERE dept = 'Marketing';

name │ salary
─────┼────────
Bob  │ 62000
Eve  │ 70000

-- Aggregate the view
SELECT dept, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM active_employees
GROUP BY dept;

dept         │ headcount │ avg_salary
─────────────┼───────────┼──────────
Engineering  │ 1         │ 85000
HR           │ 1         │ 55000
Marketing    │ 2         │ 66000

View for Hiding Sensitive Data

-- Full employee table has salary column
-- A public view hides it
CREATE VIEW IF NOT EXISTS public_employees AS
SELECT id, name, dept_id, active
FROM employees;

-- Salary column is not visible through this view
SELECT * FROM public_employees;

id │ name  │ dept_id │ active
───┼───────┼─────────┼────────
1  │ Alice │ 1       │ 1
2  │ Bob   │ 2       │ 1
...

View for a Complex Report

-- Monthly sales summary view
CREATE VIEW IF NOT EXISTS monthly_summary AS
SELECT
  strftime('%Y-%m', order_date) AS month,
  COUNT(*)                      AS orders,
  SUM(amount)                   AS revenue,
  ROUND(AVG(amount), 2)         AS avg_order
FROM orders
GROUP BY strftime('%Y-%m', order_date);

-- Now report generation is one line:
SELECT * FROM monthly_summary ORDER BY month;

Listing All Views

-- In the SQLite shell:
sqlite> .tables
active_employees   departments   employees   monthly_summary

-- Or query the schema table:
SELECT name FROM sqlite_master WHERE type = 'view';

name
──────────────────
active_employees
monthly_summary

Viewing a View's Definition

SELECT sql FROM sqlite_master WHERE type='view' AND name='active_employees';

CREATE VIEW active_employees AS
SELECT e.id, e.name, d.name AS dept, e.salary
FROM employees e
JOIN departments d ON e.dept_id = d.id
WHERE e.active = 1

Important: Views Are Not Stored Data

TABLE             VIEW
──────────────────────────────────────────────────────
Stores data       Stores only the query definition
Takes disk space  Negligible space (just SQL text)
Data is static    Always reflects current table data
Updates with DML  Cannot be directly updated (usually)

Key Takeaways

A view is a named, saved SELECT query — not a copy of data
Querying a view executes the underlying SELECT in real time
Views simplify complex queries and create consistent data shapes
Use views to hide sensitive columns from certain query paths
Views always reflect current data since they re-run their query each time

Previous lesson

Back to course

Next lesson