Simplifying Data Access with Views
- Shunmuga Priya
- Sep 1
- 4 min read
Data is stored in tables. Multiple tables are connected to form a complex structure, and complex queries are required to extract meaningful information. These queries are usually written by a database engineer who has strong knowledge of database systems.
However, if a business user without database expertise wants to access the data, it can be very difficult.
Workflow: Database Engineer → Complex Query → Saved Query → Business User
The database engineer writes a complex query and saves it for future use. Business users can access this saved query whenever needed. This saved structure is called a view.
Views in SQL are considered as a virtual table.
Views act as virtual tables that do not store data physically. Instead, they execute the underlying query whenever accessed.
A view also has rows and columns as they are in a real table in the database.
Views can either be read-only or updatable depending on their structure.
The view is created based on a table, any changes that are performed on the table reflect into the view any changes performed on the view reflect on the table also.
The normal table is referred to as a physical table, where real data is stored in the database. A view, on the other hand, is considered a virtual table. It does not store data itself but instead provides a dynamic representation of data derived from one or more physical tables.
Initially the View table is empty. When you query a view, the SQL engine triggers the queue that is attached to the view. This query accesses the physical table, retrieves the relevant data, and fills the result in the view table. The view acts as an abstraction layer, allowing users to interact with a simplified or filtered version of the data without exposing the full structure or contents of the underlying tables.
In this example, consider a student table with three attributes: Roll No, Name, and Marks. Based on this table, there are three different views created for different users:
The teacher can see the full table, including Roll No, Name, and Marks.
The student can only see Roll No and Name, but not Marks.
The office staff can view only the Name, without access to Roll No or Marks.
Each user sees only the part of the table they’re authorized to access. These views act as filters, ensuring that sensitive data is protected and only visible to the right people.
Key Features:
OR REPLACE: Updates an existing view without dropping it.
TEMP/TEMPORARY: Creates a view that exists only for the session.
WITH CHECK OPTION: Ensures that updates or inserts through the view satisfy its conditions.
Advantages of View
To restrict data access
To make complex queries easy
To provide data independence
To present different views of the same data
Operations on View
Create View
Update View
Delete View
Create View
Syntax:
CREATE VIEW view _name AS
SELECT column1, column2…
FROM table_name
WHERE condition;
Example: Given tables in Database
Stud_Marks
Stud_Details
1.Create a SQL view that displays only the Name and Address columns from the Stud_Details Table.
2.Create View on Both tables Stud_Details and Stud_Marks(Multiple Views).
Create View on Single Table | Create View on Multiple Table |
CREATE VIEW DetailsView AS SELECT Name, Address FROM Stud_Details WHERE ID<4; | CREATE VIEW MARKSVIEW AS SELECT Stud_Details.Name, Stud_Details.Address, Stud_Marks.Marks FROM Stud_Details,Stud_Marks WHERE Stud_Details.Name=Stud_Marks.Name; |
Select * from detailsview | Select * from MarksView |
Output:  | Output:  |
Update View
Syntax:
CREATE OR REPLACE VIEW view_name AS
SELECT column1,column2..
FROM table_name
WHERE condition;
The view was first created with Name and Address. To include ID, use CREATE OR REPLACE VIEW to redefine it with all three columns.
Create View DetailsView | Update View DetailsView |
CREATE VIEW DetailsView AS SELECT Name, Address FROM Stud_Details WHERE ID<4; | CREATE OR REPLACE VIEW DetailsView AS SELECT ID,Name,Address FROM Stud_Details WHERE ID<4; |
Select * from detailsview | Select * from DetailsView |
Output: | Output:  |
Delete View
Syntax:
DROP VIEW DetailsView
Create View DetailsView | Delete View DetailsView |
CREATE VIEW DetailsView AS SELECT Name, Address FROM Stud_Details WHERE ID<4; | DROP VIEW DetailsView |
Select * from detailsview | |
Output: | Output: View deleted |
Types of Views
1.Simple View
A view built from a single table without joins or aggregations.
Why use it?
Hide unnecessary columns
Simplify queries
Enforce limited access
2. Complex View
A view involving joins, group by, or aggregates.
Why use it?
Simplify reporting queries
Centralize business logic
3.Materialized View
A pre-computed, stored result set of a query. Unlike normal views, it stores data on disk.
Why use it?
Speed up expensive queries
Power analytics dashboards
4.Updatable View
A view where you can INSERT, UPDATE, DELETE rows directly.
Why use it?
Provide restricted updates without exposing full table
5.Read-Only View
A view that cannot be updated (complex logic, joins, or aggregates).
Why use it?
Safely expose analytics reports
6. Recursive View
A view built using a recursive CTE to handle hierarchies.
Why use it?
Org charts, folder structures, category trees
Difference between Table and View
Conclusion
Views in postgresql are a powerful tool for simplifying data interaction and enhancing database management. Views offer benefits like abstraction, security, and code resuability, making them a valuable addition to any database design and application development.
