VIEW in SQL with example

View is a virtual table based on the result set of a SQL query in a simplified and unified manner. It is useful for simplifying data access, improving security, and enhancing maintainability.

View does not store data themselves, they dynamically fetch data from underlying tables based on the query definition. It is the best example of both encapsulation and abstraction in SQL

Syntax

CREATE VIEW view_name AS
SELECT column1, column2, column3 ...
FROM table_name
WHERE condition;

Example(MS SQL server)

We need to create a view that returns student ID, student name, class name, and enrollment date from 3 tables having student details, Class details, and Enrollment.

Table Creation, added tbl_ at the start of the table name to easily identify it as a table.

-- Students Table
CREATE TABLE tbl_Students (
    StudentID INT PRIMARY KEY,
    Name NVARCHAR(50),
    ClassID INT
);

-- Classes Table
CREATE TABLE tbl_Classes (
    ClassID INT PRIMARY KEY,
    ClassName NVARCHAR(50)
);

-- Enrollments Table
CREATE TABLE tbl_Enrollments (
    EnrollmentID INT PRIMARY KEY,
    StudentID INT,
    ClassID INT,
    EnrollmentDate DATE,
    FOREIGN KEY (StudentID) REFERENCES tbl_Students(StudentID),
    FOREIGN KEY (ClassID) REFERENCES tbl_Classes(ClassID)
);

Sample Data Insertion

-- Insert Data into Students Table
INSERT INTO tbl_Students (StudentID, Name, ClassID)
VALUES
    (1, 'Aarav', 101),
    (2, 'Aadhya', 102),
    (3, 'Rahul', 101);

-- Insert Data into Classes Table
INSERT INTO tbl_Classes (ClassID, ClassName)
VALUES
    (101, 'Mathematics'),
    (102, 'Science'),
    (103, 'History');

-- Insert Data into Enrollments Table
INSERT INTO tbl_Enrollments (EnrollmentID, StudentID, ClassID, EnrollmentDate)
VALUES
    (1, 1, 101, '2024-01-15'),
    (2, 2, 102, '2024-01-16'),
    (3, 3, 101, '2024-01-17');

Create a view named VW_StudentClassEnrollment, added VW at the starting of the view name to easily identify it as a View, not a table.

CREATE VIEW VW_StudentClassEnrollment AS
SELECT
  s.StudentID,
  s.Name AS StudentName,
  c.ClassName,
  e.EnrollmentDate
FROM
  tbl_Students s
INNER JOIN
  tbl_Enrollments e ON s.StudentID = e.StudentID
INNER JOIN
  tbl_Classes c ON e.ClassID = c.ClassID;
-- Get data from the view
SELECT * 
FROM VW_StudentClassEnrollment;

Result

StudentID	StudentName	ClassName	EnrollmentDate
1	Aarav	Mathematics	15-01-2024
2	Aadhya	Science	16-01-2024
3	Rahul	Mathematics	17-01-2024

Drop View

We can drop a view by using the command Drop view view_name;

Benefits of using view

Simplification: Views simplify complex SQL queries. Instead of writing a complex join or aggregation query multiple times, you define it once in a View and use the View in your queries.
Security: Views can restrict access to specific columns or rows in a table. You can grant users access to the View without giving them direct access to the underlying tables.
Example.
```
GRANT SELECT ON VW_StudentClassEnrollment TO some_user;
```
Maintainability: Views centralize the logic for complex queries. If the underlying tables change, you only need to update the View definition rather than every instance of the query in your application.
Abstraction: Views abstract the underlying table schema from users. They provide a simplified and consistent interface to the data.
Encapsulation: View restricts the direct access to the table user to create a view. If the query logic needs to change, it is updated in the View definition without modifying every instance where the query is used.

Advanced features

Updatable Views: Some Views can be updated directly if they meet certain criteria, such as having a one-to-one relationship with the underlying table and not containing any aggregate functions.
Indexed Views: In some databases, you can create indexed views to improve performance. Indexed views materialize the result set and store it physically, providing faster query performance.

MS SQL Server supports both updation and indexing on views.

The example query written in MS SQL Server is attached.