Views

Virtual Tables

A view is a named query that appears as a table but doesn't store data itself. When you query a view, the database executes the underlying query and returns the results. Views simplify complex queries, restrict access to certain data, and provide a consistent interface even when the underlying schema changes.

Creating Views

  -- Simple view
  CREATE VIEW CS_Students AS
  SELECT ID, Name, Age, Email
  FROM Students
  WHERE DeptID = 10;

  -- Use the view like a table
  SELECT * FROM CS_Students WHERE Age > 22;

  -- Complex view with JOIN and aggregation
  CREATE VIEW Student_Enrollment_Summary AS
  SELECT
      s.Name,
      COUNT(e.CourseID) AS CoursesEnrolled,
      AVG(e.GradeNum) AS AvgGrade
  FROM Students s
  LEFT JOIN Enrollments e ON s.ID = e.StudentID
  GROUP BY s.ID, s.Name;

  -- Query the view
  SELECT * FROM Student_Enrollment_Summary
  WHERE CoursesEnrolled > 3;

Why Use Views?

• Simplicity — Hide complex JOINs and subqueries behind a simple name.
• Security — Restrict access to certain columns or rows.
• Consistency — Provide a stable interface even if the underlying schema changes.
• Abstraction — Application code queries the view, not the base tables.

Updatable Views

  -- Simple views (no aggregation, no JOIN) are often updatable
  UPDATE CS_Students SET Age = 26 WHERE ID = 1;
  INSERT INTO CS_Students (ID, Name, Age, Email)
  VALUES (5, 'Eve', 21, 'eve@example.com');
  DELETE FROM CS_Students WHERE ID = 5;

  -- Views with JOINs, GROUP BY, or DISTINCT are usually read-only