SQL – Views – Explanation and Examples

Views are virtual tables which groups necessary data from two or more real tables and represent them into a single virtual table. Views can also be used to present the limited data from a single table or when we have to manipulate some data before presenting.

A view is a virtual table based on the result-set of the Sql query. These are searchable objects in the database. It does not contain its own data but only the data from already existing tables.

Why Views?

• View is used when we have to give access to limited amount of data while denying the access to the original table.
• So views are created mainly for security purposes but they appear like a normal table to the user.
• Then you might ask why views instead of tables? Views combine multiple table subsets and contains only the selected data to be viewed by users. Hence it is better(performance and resource utilization) to transform the data once and return it each time it is accessed instead of transforming it each time programmatically.
• Hence it acts as an abstraction of the data in the table which is not known by the end user.
• Main purpose of views is it restricts access to certain columns and rows in the table and hides the real table name thereby providing security.
• Views are used along with JOINS in Sql to merge tables with specific information.

Where Views?

Views do not exist physically and stored in the database as objects. They are temporarily created for security purposes by hiding information from original table and encapsulating the table name.

Types of views

There are two types:

• Simple view: These views are created only from single table
• Complex view: These are created from multiple tables and groups more data. JOINS are applied in complex views

Creating Views

Views displays only those data present in the query at the time of creation. It can be created using same or different databases.

Syntax:

 
CREATE VIEW view_name AS
SELECT column1,column2,...columnN
FROM tables
[WHERE conditions];
 

Parameters:

• view_name: Name of the view you want to create
• column1,column2: The column names you want to combine from different tables
• tables: Name of one or more tables you want to extract data from
• WHERE condition: it is an optional one. The conditions for the records to satisfy before adding in views

Example:

1) Simple view

Consider the “student”  table,

 
CREATE VIEW my_view AS
SELECT Name, Grade
FROM  student;

Output:

SELECT * FROM my_view

Only the  selected rows are present in “my_view” and it hides all the other confidential information.

2) Complex view

Suppose, there is another table “Student_Id”

We need only the information such as a student’s ID, name and grade for the users to view. This can be done using views as:

 
CREATE VIEW Student_view AS
SELECT Student_Id.ID, student.Name, student.Grade
FROM  Student_Id,student;
 

Output:

 
SELECT * FROM Student_view

Updating views

Updating existing views refers to modifying the definition of the views without actually dropping it. For this, we use CREATE OR REPLACE VIEW statement.

Syntax:

 
CREATE OR REPLACE VIEW view_name AS
SELECT v1.column_name1, v2.column_name2, v2.column_name3 etc
FROM table_name1 AS v1, table_name2 AS v2...
WHERE  [condition];
 

Example:

We will add another column in the view “Student_view” as follows:

 
CREATE OR REPLACE VIEW Student_view AS
SELECT Student_Id.ID, student.Name, student.Grade, student.Points
FROM  Student_Id,student;
WHERE student.Age > 13
 

Output:

 
SELECT * FROM Student_view

In the above table, the view is updated with additional column and reduced in row based on the condition in the update query.

Dropping views

Dropping views means deleting the entire view which is similar to dropping the table.

Syntax:

 
DROP VIEW view_name

Example:

 
DROP VIEW Student_view

“Student_view” View will be deleted completely including the view structure.

Learning never exhausts the mind! keep learning!!

Do share and subscribe and comment below for any suggestion, query or opinion.
Keep Coding! Happy Coding! 🙂

Recommended -