SQL Views - Saving Queries Like Virtual Tables

Introduction

Imagine you write a long query to generate a student report — and you need to run it every week. Wouldn’t it be better to save it like a virtual table and reuse it? That’s what a VIEW lets you do. SQL Views simplify repetitive queries by treating them as reusable objects.

What is a SQL View?

A View is a virtual table based on a SQL query. It doesn’t store data itself — it stores a query definition. Every time you use the view, the underlying query runs and returns live results.

Syntax – Creating a View

CREATE VIEW view_name AS
SELECT columns
FROM table_name
WHERE condition;

Sample Tables – students and results

CREATE TABLE students (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50),
  class VARCHAR(10)
);

CREATE TABLE results (
  roll_no INT,
  subject VARCHAR(30),
  marks INT
);

INSERT INTO students VALUES
(1, 'Aarav Sharma', '10A'),
(2, 'Diya Iyer', '9B'),
(3, 'Sneha Patil', '10A');

INSERT INTO results VALUES
(1, 'Maths', 85),
(2, 'Maths', 92),
(3, 'Maths', 78),
(1, 'Science', 88),
(2, 'Science', 90),
(3, 'Science', 84);

1. Create a View for Student Scores

CREATE VIEW student_scores AS
SELECT s.name, s.class, r.subject, r.marks
FROM students s
JOIN results r ON s.roll_no = r.roll_no;

Now, you can query this view like a regular table:

SELECT * FROM student_scores;

name           | class | subject | marks
----------------+--------+---------+-------
Aarav Sharma   | 10A   | Maths   | 85
Aarav Sharma   | 10A   | Science | 88
Diya Iyer      | 9B    | Maths   | 92
Diya Iyer      | 9B    | Science | 90
Sneha Patil    | 10A   | Maths   | 78
Sneha Patil    | 10A   | Science | 84

2. View for High Scorers

Create a view of students scoring above 85:

CREATE VIEW high_scores AS
SELECT s.name, r.subject, r.marks
FROM students s
JOIN results r ON s.roll_no = r.roll_no
WHERE r.marks > 85;

SELECT * FROM high_scores;

name           | subject | marks
----------------+---------+-------
Diya Iyer      | Maths   | 92
Diya Iyer      | Science | 90
Aarav Sharma   | Science | 88

3. Updating a View (Re-creating)

SQL doesn’t support direct updates to a view. Instead, you drop and re-create it:

DROP VIEW high_scores;

CREATE VIEW high_scores AS
SELECT s.name, r.subject, r.marks
FROM students s
JOIN results r ON s.roll_no = r.roll_no
WHERE r.marks > 80;

4. Deleting a View

DROP VIEW student_scores;

5. Read-Only vs Updatable Views

• Most Views are read-only.
• Some databases allow updating through a view if it references a single base table without aggregation or joins.

Use Cases in a School Context

• student_scores – for parent-teacher meetings
• high_scores – for creating a leaderboard
• average_class_scores – for academic performance analysis

Best Practices

• Use views to encapsulate complex logic and simplify reports.
• Keep views small and fast — avoid excessive joins and calculations inside views.
• Document what each view represents and when it was last updated (especially in production systems).

Summary

SQL Views act like saved queries — virtual tables that simplify your life by keeping logic in one place. Instead of repeating joins or filters every time, you define a view once and query it again and again. Think of them as smart shortcuts in your database toolkit.

What’s Next?

In the next lesson, we’ll explore Common Table Expressions (CTEs) — which allow temporary result sets for readable, modular queries inside a WITH clause.