SQL Views — Saving Queries Like Virtual Tables

Several school reports need the same filtered list of high-scoring students. Copying the filter into every report invites inconsistent changes.

A regular view stores a query definition that can be used as a named row source. It usually computes results from the underlying data rather than storing a snapshot of those results.

CREATE VIEW gives the query a name. Reading that name applies the view’s query to its underlying tables according to the engine’s rules. A materialized view is a different feature that stores results and needs an update or refresh policy.

Related concepts: Indexes and Denormalize Carefully and Users and Permissions.

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.

SQL · portable concepts; preserved examples identify their engine

CREATE VIEW view_name AS
SELECT columns
FROM table_name
WHERE condition;

SQL · portable concepts; preserved examples identify their engine

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);

SQL · portable concepts; preserved examples identify their engine

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

SQL · portable concepts; preserved examples identify their engine

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

SQL · portable concepts; preserved examples identify their engine

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;

SQL · portable concepts; preserved examples identify their engine

DROP VIEW student_scores;

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

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

• 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).

A view is not automatically a performance improvement or a complete access-control solution. Whether it can be updated depends on its definition and engine. ORDER BY in the outer report should specify the desired display order.

Create a reusable high-score view, then read the visible student names.

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

SQL · this practice variation runs with SQLite 3; the preserved examples retain their stated dialect

CREATE VIEW practice_high_scores AS SELECT roll_no, name, marks FROM practice_students WHERE marks >= 80;
SELECT name, marks FROM practice_high_scores ORDER BY roll_no;

The view names the selection rule. It does not create independent copied marks that need their own manual synchronization.

A regular view stores a query definition that can be used as a named row source. It usually computes results from the underlying data rather than storing a snapshot of those results.

The view names the selection rule. It does not create independent copied marks that need their own manual synchronization.

Continue your understanding with Indexes and Denormalize Carefully and Users and Permissions.

Source for the engine-specific behavior: MySQL 8.4 reference documentation.