SQL Views

A View is a virtual table based on a SELECT query.

CREATE VIEW student_view AS
SELECT name, marks
FROM students;

The view can then be queried like a normal table.

Views can make SQL applications easier to manage.

• Simplify complex queries.
• Reuse frequently used queries.
• Hide unnecessary columns.
• Help control access to sensitive data.
• Create reusable reports.

The basic syntax for creating a view is:

CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name;

The SELECT statement defines the data that the view displays.

Suppose we have a students table.

CREATE VIEW student_details AS
SELECT id, name, marks
FROM students;

Now student_details can be queried as a virtual table.

After creating a view, use SELECT just like you would with a table.

SELECT *
FROM student_details;

This displays the rows and columns provided by the view.

You can select only the required columns from a view.

SELECT name, marks
FROM student_details;

Views can therefore be used like normal tables in SELECT queries.

A view can contain a WHERE condition.

CREATE VIEW active_students AS
SELECT id, name, marks
FROM students
WHERE status = 'Active';

The view shows only active students.

A view can be based on a query that sorts its source data.

CREATE VIEW student_marks AS
SELECT name, marks
FROM students
ORDER BY marks DESC;

When designing views, focus on the data the view needs to expose and apply ordering where appropriate in the final SELECT.

A view can combine data from multiple tables using JOIN.

CREATE VIEW student_courses AS
SELECT s.name, c.course_name
FROM students s
INNER JOIN courses c
ON s.course_id = c.id;

This creates a reusable student-course report.

A view can contain both JOIN and WHERE conditions.

CREATE VIEW active_course_students AS
SELECT s.name, c.course_name
FROM students s
INNER JOIN courses c
ON s.course_id = c.id
WHERE s.status = 'Active';

This view displays active students along with their courses.

Aliases can make view column names easier to understand.

CREATE VIEW student_report AS
SELECT
    name AS student_name,
    marks AS total_marks
FROM students;

The view exposes the selected columns using meaningful names.

A view can contain calculated expressions.

CREATE VIEW fee_report AS
SELECT
    name,
    total_fee,
    paid_fee,
    total_fee - paid_fee AS due_fee
FROM students;

The view calculates the outstanding fee for each student.

Aggregate functions can be used to create summary views.

CREATE VIEW course_summary AS
SELECT
    course_id,
    COUNT(*) AS total_students,
    AVG(marks) AS average_marks
FROM students
GROUP BY course_id;

This view provides a summary for each course.

Views can store grouped reports.

CREATE VIEW course_count AS
SELECT course_id, COUNT(*) AS total_students
FROM students
GROUP BY course_id;

The result shows the number of students in each course.

A view can filter grouped results using HAVING.

CREATE VIEW popular_courses AS
SELECT course_id, COUNT(*) AS total_students
FROM students
GROUP BY course_id
HAVING COUNT(*) > 10;

This view shows courses having more than 10 students.

In MySQL, SHOW CREATE VIEW can be used to see the SQL definition of a view.

SHOW CREATE VIEW student_details;

This is useful when you need to inspect how a view was created.

In MySQL, SHOW FULL TABLES can help identify views in the current database.

SHOW FULL TABLES
WHERE Table_type = 'VIEW';

This displays objects whose type is VIEW.

You can replace an existing view definition using CREATE OR REPLACE VIEW.

CREATE OR REPLACE VIEW student_details AS
SELECT id, name, marks, city
FROM students;

This changes the query used by the view.

MySQL also provides ALTER VIEW for changing a view definition.

ALTER VIEW student_details AS
SELECT id, name, marks
FROM students;

The SELECT definition of the view is changed without changing the underlying table.

Use DROP VIEW to remove a view.

DROP VIEW student_details;

Dropping a view does not delete the underlying table data.

IF EXISTS prevents an error when the specified view does not exist.

DROP VIEW IF EXISTS student_details;

This is useful in scripts where the view may or may not already exist.

A view can expose only selected columns from a table.

CREATE VIEW public_student_info AS
SELECT id, name, course_id
FROM students;

If the original table contains sensitive columns, the view can omit them from the displayed result. Access control still depends on the database permissions granted to users.

Views are useful for frequently required reports.

CREATE VIEW payment_report AS
SELECT
    s.name,
    s.total_fee,
    s.paid_fee,
    s.total_fee - s.paid_fee AS due_fee
FROM students s;

Instead of writing the same calculation repeatedly, applications can query the view.

A view can be filtered just like a normal table.

SELECT *
FROM payment_report
WHERE due_fee > 0;

This returns only students who have outstanding fees.

You can sort the result of a view.

SELECT *
FROM payment_report
ORDER BY due_fee DESC;

This displays students with the highest outstanding fee first.

A view can be used as a source for another view.

CREATE VIEW pending_students AS
SELECT name, due_fee
FROM payment_report
WHERE due_fee > 0;

This creates another virtual table based on the existing view.

• Using an incorrect SELECT query while creating the view.
• Forgetting to use meaningful column aliases.
• Assuming a view always stores a separate copy of the data.
• Deleting a view when the intention was to delete table data.
• Forgetting that permissions are still required to access the view.

Always test the SELECT query before creating a complex view.

Suppose the students table contains fee information.

CREATE VIEW student_payment_report AS
SELECT
    id,
    name,
    total_fee,
    paid_fee,
    total_fee - paid_fee AS due_fee
FROM students;

Now display students with pending fees:

SELECT name, total_fee, paid_fee, due_fee
FROM student_payment_report
WHERE due_fee > 0
ORDER BY due_fee DESC;

Consider the following tables:

CREATE TABLE courses (
    id INT PRIMARY KEY,
    course_name VARCHAR(100),
    fee DECIMAL(10,2)
);

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    course_id INT,
    paid_fee DECIMAL(10,2),
    status VARCHAR(20)
);

Create a view that combines student and course information:

CREATE VIEW student_course_report AS
SELECT
    s.id,
    s.name,
    c.course_name,
    c.fee AS total_fee,
    s.paid_fee,
    c.fee - s.paid_fee AS due_fee,
    s.status
FROM students s
INNER JOIN courses c
ON s.course_id = c.id;

Now query the view:

SELECT *
FROM student_course_report
WHERE due_fee > 0
ORDER BY due_fee DESC;

This provides a reusable student payment report without rewriting the JOIN and fee calculation every time.