Working with Views

Views package complex queries into reusable, read-only virtual tables. They are perfect for the University schema when different audiences need simplified snapshots of the same underlying data.

Learning Goals

• Create views to present student, course, and enrollment data
• Understand view maintenance (CREATE OR REPLACE, DROP)
• Apply views to security and reporting scenarios
• Recognize limitations, including updatability and performance costs

Creating a View

Create a view to show each student's current term schedule without repeating joins in every query.

Student schedule view

CREATE VIEW vw_student_schedule AS
SELECT s.student_id,
       s.first_name || ' ' || s.last_name AS student_name,
       e.semester,
       c.course_code,
       c.course_title,
       d.dept_name,
       e.grade
FROM enrollment AS e
JOIN student    AS s ON s.student_id = e.student_id
JOIN course     AS c ON c.course_id  = e.course_id
JOIN department AS d ON d.dept_id    = c.dept_id;

Querying the view is just like querying a table:

SELECT *
FROM vw_student_schedule
WHERE student_id = 2
  AND semester = 'Spring 2025';

Views for Department Reporting

Summaries become easier to read when wrapped in a view.

Department enrollment summary

CREATE VIEW vw_department_enrollments AS
SELECT d.dept_name,
       e.semester,
       COUNT(*) AS enrollment_count,
       COUNT(DISTINCT e.student_id) AS unique_students
FROM enrollment AS e
JOIN course     AS c ON c.course_id = e.course_id
JOIN department AS d ON d.dept_id   = c.dept_id
GROUP BY d.dept_name, e.semester;

SELECT *
FROM vw_department_enrollments
WHERE semester = 'Spring 2025'
ORDER BY enrollment_count DESC;

Updating a View Definition

Use CREATE OR REPLACE VIEW when requirements change, such as adding instructor details.

Add instructor detail to the schedule view

CREATE OR REPLACE VIEW vw_student_schedule AS
SELECT s.student_id,
       s.first_name || ' ' || s.last_name AS student_name,
       e.semester,
       c.course_code,
       c.course_title,
       d.dept_name,
       i.first_name || ' ' || i.last_name AS instructor_name,
       e.grade
FROM enrollment AS e
JOIN student    AS s ON s.student_id = e.student_id
JOIN course     AS c ON c.course_id  = e.course_id
JOIN department AS d ON d.dept_id    = c.dept_id
LEFT JOIN instructor AS i ON i.instructor_id = c.instructor_id;

Drop a view when you no longer need it:

DROP VIEW IF EXISTS vw_department_enrollments;

Security Layer

Grant access to a view instead of the base table to hide sensitive columns. For example, advisors might only need high-level student data.

Hide email addresses from student assistants

CREATE VIEW vw_public_student_directory AS
SELECT student_id,
       first_name,
       last_name,
       COALESCE(dept_id, 0) AS dept_id,
       enrollment_year
FROM student;

Database administrators can then grant SELECT on the view without exposing emails stored in student.

Limitations and Performance Notes

• Views execute the underlying query every time; complex joins may still be expensive.
• Most databases do not allow updates through views that include joins, aggregates, or DISTINCT.
• SQLite views are always virtual. Engines like PostgreSQL offer materialized views if you need cached results (CREATE MATERIALIZED VIEW).
• Changing column names in base tables can break dependent views—plan migrations accordingly.

Summary

• Views simplify repeated query logic and deliver curated perspectives on your data.
• CREATE VIEW defines the virtual table; CREATE OR REPLACE updates it; DROP VIEW removes it.
• Use views for reporting, security, and API-like abstractions in the University schema.

Quiz

Show quiz

1. What does CREATE OR REPLACE VIEW allow you to do?
   A) Rename a table
   B) Update a view definition without dropping it
   C) Convert a view into a table
   D) Refresh a materialized view

2. Which view would you expose to limit access to student email addresses?
   A) vw_student_schedule
   B) vw_public_student_directory
   C) vw_department_enrollments
   D) Any view automatically hides email addresses

3. True or False: Views in SQLite store their own copies of the data.
   A) True
   B) False

4. Why might a view be non-updatable?
   A) It references multiple tables and includes derived columns
   B) The table has a primary key
   C) The view name starts with vw_
   D) The view is queried too often

---

Answers

1. B
2. B
3. B
4. A