Lesson 24 — Virtual Tables

Views

A view is a saved SELECT query that acts like a table. It simplifies complex queries, enforces security (hide sensitive columns), and provides a stable interface over a changing schema.

SQL — Create and use view 
-- Create a view:
CREATE VIEW v_learner_summary AS
SELECT
    l.learner_id,
    l.full_name,
    l.course,
    l.city,
    l.mark,
    CASE
        WHEN l.mark >= 80 THEN 'Distinction'
        WHEN l.mark >= 60 THEN 'Merit'
        WHEN l.mark >= 50 THEN 'Pass'
        ELSE 'Not yet competent'
    END AS grade_label,
    COALESCE(p.employer_name, 'Unplaced') AS placement_status
FROM   learners l
LEFT JOIN placements p ON l.learner_id = p.learner_id
WHERE  l.is_active = TRUE;

-- Use the view exactly like a table:
SELECT * FROM v_learner_summary;
SELECT * FROM v_learner_summary WHERE course = 'Java';
SELECT city, COUNT(*) FROM v_learner_summary GROUP BY city;
SQL — Security views 
-- DROP VIEW:
DROP VIEW IF EXISTS v_learner_summary;

-- REPLACE (MySQL) — drop and recreate in one statement:
CREATE OR REPLACE VIEW v_learner_summary AS
SELECT l.learner_id, l.full_name, l.course, l.mark
FROM   learners l
WHERE  l.is_active = TRUE;

-- Security view — hide sensitive columns:
CREATE VIEW v_learner_public AS
SELECT
    learner_id,
    full_name,
    course,
    city
    -- id_number, email, phone EXCLUDED for privacy
FROM learners;

-- Grant access to the view without granting access to the base table:
-- GRANT SELECT ON v_learner_public TO reporting_user;
SQL — Updatable views 
-- Updatable views (simple views only):
-- A view is updatable if it is based on one table with no GROUP BY, DISTINCT, or aggregate
CREATE VIEW v_active_learners AS
SELECT learner_id, full_name, course, mark
FROM   learners
WHERE  is_active = TRUE;

-- This UPDATE goes through to the base table:
UPDATE v_active_learners
SET    mark = 82.0
WHERE  learner_id = 1;

-- WITH CHECK OPTION — prevents updates that would remove the row from the view:
CREATE OR REPLACE VIEW v_active_learners AS
SELECT learner_id, full_name, course, mark
FROM   learners
WHERE  is_active = TRUE
WITH CHECK OPTION;

Practice Task

Your Turn

Create: (1) a view v_pass_list showing only learners who passed (mark >= 50), with their grade label; (2) a view v_course_stats showing count, average, min, max per course; (3) a security view v_contact_list showing only learner_id, full_name, and email (no marks or ID numbers); (4) query all three views with WHERE clauses.

Common Mistakes

  • Views do not store data — they re-execute the underlying query each time.
  • Complex views with many JOINs can be slow — a materialised view (PostgreSQL) or indexed view (SQL Server) stores the result.
  • Circular view definitions — view A references view B which references view A.
  • Dropping a base table removes all dependent views — plan schema changes carefully.

Professional Tip

Use views to create a stable API for your data. Reporting tools, BI dashboards, and application queries should SELECT from views, not from base tables directly. This lets you change the underlying structure without breaking every query.

Mini Quiz

When does a view update its data?