Md Enayetur Rahman

Posted on Oct 18

CS50 SQL — Lecture 4 Notes: Views, Temp Views, CTEs & Triggers (with Soft Deletes)

CS50 SQL — Lecture 4 Notes: Views, Temp Views, CTEs & Triggers (with Soft Deletes)

Why Views Exist (and what a view actually is)

Definition: A view is a virtual table defined by a query. It’s stored in your schema like a table, queried like a table, but it doesn’t persist rows itself—its rows come from the underlying tables every time you query it.

Why use views?

Simplify multi‑table joins into a single, easy surface.
Aggregate raw, granular data into summarized statistics.
Partition big tables into logical slices (e.g., one view per year).
Secure data by hiding columns you don’t want consumers to see.
Stability & Reuse: You write a correct query once; everyone uses it consistently.

In SQLite, views don’t add much disk space. In other DBMSs, you may also have materialized views (persisted snapshots). This lecture focuses on plain (virtual) views.

Simplifying Multi‑Table Queries with a View

Scenario: International Booker Prize dataset with three tables:

authors(id, name, …)
books(id, title, year, …)
authored(author_id → authors.id, book_id → books.id) — many‑to‑many bridge

Without a view (nested subqueries / joins)

To get Fernanda Melchor’s book titles, you might write nested subqueries:

SELECT title
FROM books
WHERE id IN (
  SELECT book_id
  FROM authored
  WHERE author_id = (
    SELECT id FROM authors WHERE name = 'Fernanda Melchor'
  )
);

Or explicit joins:

SELECT b.title
FROM authors a
JOIN authored au ON a.id = au.author_id
JOIN books b    ON b.id = au.book_id
WHERE a.name = 'Fernanda Melchor';

Create a simplifying view

Define a “joined” surface once:

CREATE VIEW longlist AS
SELECT a.name, b.title
FROM authors a
JOIN authored au ON a.id = au.author_id
JOIN books b    ON b.id = au.book_id;

Now your query is trivial:

SELECT title FROM longlist WHERE name = 'Fernanda Melchor';

Ordering from the view:

SELECT name, title
FROM longlist
ORDER BY title ASC;

You could bake ORDER BY directly into the view, but most teams keep views “unordered” and order at query time for flexibility.

Aggregating with Views (AVG, ROUND, GROUP BY)

Scenario: A ratings(book_id, rating) table holds individual ratings. You want per‑book averages with nice formatting.

Derive the per‑book average

CREATE VIEW average_book_ratings AS
SELECT
  r.book_id,
  b.title,
  b.year,
  ROUND(AVG(r.rating), 2) AS rating
FROM ratings r
JOIN books b ON b.id = r.book_id
GROUP BY r.book_id;

Use it like a table:

SELECT title, rating
FROM average_book_ratings
ORDER BY rating DESC, title ASC;

Why not store averages in a column? Because views recompute from truth at query time. Add a new rating → requery the view → averages update automatically.

Temporary Views vs. Permanent Views

CREATE VIEW … → persists in schema until dropped.
CREATE TEMP VIEW … → exists only for the current connection. When you .quit / close the connection, the temp view disappears.

Example (temp view):

CREATE TEMP VIEW average_ratings_by_year AS
SELECT
  year,
  ROUND(AVG(rating), 2) AS rating
FROM average_book_ratings
GROUP BY year;

Now you can:

SELECT * FROM average_ratings_by_year;

…and lose it on disconnect (great for ad‑hoc analysis).

CTEs (Common Table Expressions): A View for One Query

A CTE (defined with WITH) is like a temporary, inline view that lives only during a single statement.

Pattern:

WITH cte_name AS (
  -- any SELECT query
)
SELECT ...
FROM cte_name
WHERE ...;

Recreate “avg by year” using a CTE (not a persisted view):

WITH average_book_ratings AS (
  SELECT
    r.book_id,
    b.title,
    b.year,
    ROUND(AVG(r.rating), 2) AS rating
  FROM ratings r
  JOIN books b ON b.id = r.book_id
  GROUP BY r.book_id
)
SELECT
  year,
  ROUND(AVG(rating), 2) AS rating
FROM average_book_ratings
GROUP BY year
ORDER BY year;

When to choose what

CTE: one‑off complex query; no schema changes.
TEMP VIEW: exploratory analysis within one session.
VIEW: shared interface for your team/app.

Partitioning Data with Views

Scenario: books contains all years. Your app has “/longlist/2021”, “/longlist/2022”, etc. Create small per‑year views.

CREATE VIEW longlist_2022 AS
SELECT id, title
FROM books
WHERE year = 2022;

CREATE VIEW longlist_2021 AS
SELECT id, title
FROM books
WHERE year = 2021;

Now your endpoints can SELECT * FROM longlist_2022; without repeating WHERE year = 2022 all over the codebase.

Naming: prefer descriptive names (longlist_2022) over bare years (2022). It’s clearer and less collision‑prone.

Securing Data (Anonymization) with Views

Scenario: Rideshare table has PII:

rides(id, origin, destination, rider)

Create a view without the rider’s identity:

CREATE VIEW analysis AS
SELECT
  id,
  origin,
  destination,
  'anonymous' AS rider
FROM rides;

Consumers query:

SELECT * FROM analysis;

Important SQLite note: SQLite does not enforce per‑object permissions; someone with the .db can still query the base table (rides). In PostgreSQL/MySQL/SQL Server you’d grant access only to the view and not the underlying base tables.

“Updating” Views using Triggers: INSTEAD OF

Views are read-only in SQLite (you’ll see cannot modify … because it is a view). To support “writes,” use INSTEAD OF triggers that translate a DELETE/INSERT on the view into an UPDATE/INSERT on the base table.

Example: Deleting through a view → soft delete the base row

Suppose this view lists “current” items (not deleted):

CREATE VIEW current_collections AS
SELECT id, title, accession_number, acquired
FROM collections
WHERE deleted = 0;

INSTEAD OF DELETE: flip the deleted flag in collections

CREATE TRIGGER delete_on_current
INSTEAD OF DELETE ON current_collections
FOR EACH ROW
BEGIN
  UPDATE collections
  SET deleted = 1
  WHERE id = OLD.id;     -- the row the user tried to delete from the view
END;

Now this works (and performs a soft delete under the hood):

DELETE FROM current_collections
WHERE title = 'Imaginative Landscape';

Example: Inserting through the view

Two cases when inserting into current_collections:
1) The museum already had this item (same accession_number) but it was deleted → undelete (set deleted = 0).
2) It’s a brand‑new item → insert a new base row.

We can express (1) with a conditional trigger using WHEN:

CREATE TRIGGER insert_when_exists
INSTEAD OF INSERT ON current_collections
FOR EACH ROW
WHEN NEW.accession_number IN (SELECT accession_number FROM collections)
BEGIN
  UPDATE collections
  SET deleted = 0
  WHERE accession_number = NEW.accession_number;
END;

And (2) with a second trigger for the non‑existing case:

CREATE TRIGGER insert_when_new
INSTEAD OF INSERT ON current_collections
FOR EACH ROW
WHEN NEW.accession_number NOT IN (SELECT accession_number FROM collections)
BEGIN
  INSERT INTO collections (title, accession_number, acquired, deleted)
  VALUES (NEW.title, NEW.accession_number, NEW.acquired, 0);
END;

Now app code can INSERT into the view, and triggers map that action to the correct base‑table behavior.

Soft Deletes + Views + Triggers = Clean UX

Base table:

CREATE TABLE collections (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  accession_number TEXT NOT NULL UNIQUE,
  acquired TEXT,             -- ISO date as TEXT in SQLite
  deleted INTEGER NOT NULL DEFAULT 0
);

View surfaces only “active” rows:

CREATE VIEW current_collections AS
SELECT id, title, accession_number, acquired
FROM collections
WHERE deleted = 0;

Trigger for deletes (soft‑delete):

CREATE TRIGGER delete_on_current
INSTEAD OF DELETE ON current_collections
FOR EACH ROW
BEGIN
  UPDATE collections SET deleted = 1 WHERE id = OLD.id;
END;

Trigger(s) for inserts (undelete or new insert):

CREATE TRIGGER insert_when_exists
INSTEAD OF INSERT ON current_collections
FOR EACH ROW
WHEN NEW.accession_number IN (SELECT accession_number FROM collections)
BEGIN
  UPDATE collections
  SET deleted = 0, title = NEW.title, acquired = NEW.acquired
  WHERE accession_number = NEW.accession_number;
END;

CREATE TRIGGER insert_when_new
INSTEAD OF INSERT ON current_collections
FOR EACH ROW
WHEN NEW.accession_number NOT IN (SELECT accession_number FROM collections)
BEGIN
  INSERT INTO collections (title, accession_number, acquired, deleted)
  VALUES (NEW.title, NEW.accession_number, NEW.acquired, 0);
END;

Now your “table‑like” API is the view:

-- read
SELECT * FROM current_collections ORDER BY acquired;

-- delete (soft)
DELETE FROM current_collections WHERE id = 42;

-- add / undelete
INSERT INTO current_collections (title, accession_number, acquired)
VALUES ('Spring Outing', '14.76', '1914-01-08');

Design Patterns, Gotchas, and a Checklist

Patterns

Join‑View Pattern: Create a “joined” view that includes commonly‑needed columns across entities (e.g., authors × authored × books).
Aggregate‑View Pattern: Predefine your favorite summaries (ROUND(AVG(…)), COUNT(*), etc.) in a view.
Partitioned Views: One view per logical slice (per year, per status). Turns WHERE clauses into readable names.
Security via Projection: Expose only columns needed to downstream users/apps. (In production DBMSs, pair with GRANT/REVOKE.)
Write‑through Views: Use INSTEAD OF triggers to emulate inserts/deletes on views and keep base tables consistent.
Soft Delete Everywhere: Add deleted flag to base tables; surface a “current” view; add triggers to translate user actions into safe updates.

Gotchas (SQLite)

Views are read‑only without triggers. You cannot INSERT/UPDATE/DELETE them directly.
No per‑object permissions: If someone has the .db, they can read base tables. For real permissions, use PostgreSQL/MySQL/SQL Server.
Date types are strings—keep consistent ISO formats if you plan to sort/compare.
CTEs live only for the statement; TEMP VIEWs live for the connection; VIEWs live until dropped.

Checklist for Writing a Good View

[ ] Is the query correct and explainable by others?
[ ] Are column names friendly (use AS to alias)?
[ ] Is precision handled (e.g., ROUND) where needed?
[ ] Is the view scope right (CTE vs TEMP VIEW vs VIEW)?
[ ] Do you need write‑through behavior? Add INSTEAD OF triggers.
[ ] Security: does the view expose only what it should?

Quick Reference (Copy/Paste)

Create view

CREATE VIEW name AS
SELECT … FROM … JOIN … WHERE …;

Create temp view (session‑only)

CREATE TEMP VIEW name AS SELECT …;

CTE (“inline view” for one query)

WITH cte AS (SELECT …)
SELECT … FROM cte WHERE …;

INSTEAD OF triggers on a view

CREATE TRIGGER trig_name
INSTEAD OF DELETE ON some_view
FOR EACH ROW
BEGIN
  UPDATE base_table SET deleted = 1 WHERE id = OLD.id;
END;

Soft‑delete design

ALTER TABLE t ADD COLUMN deleted INTEGER NOT NULL DEFAULT 0;

CREATE VIEW t_active AS
SELECT * FROM t WHERE deleted = 0;

Final Thought

Views are the API of your database. Use them to make complex schemas feel simple, guarantee consistent logic, and create safe surfaces for reading (and, with triggers, writing) data.