Published: 07 Jan 2025 | Reading Time: 10 min
Views in DBMS are virtual tables containing rows and columns obtained as a result of a query in SQL (Structured Query Language). Views streamline how users interact with data from one or more tables. Unlike traditional tables, views don't store data directly; instead, whenever the end-user requests the data according to specified needs, they generate results by executing a predefined query whenever accessed rather than revealing the complete information of the table.
Views in SQL are invaluable for simplifying complex queries, improving security, and presenting data in an easy-to-understand format. This article provides a comprehensive understanding of views in DBMS, their types, and how to create them.
In DBMS, a view is one or more populated tables that contain information from one or more tables. However, it should be noted that, unlike what is stored in a regular table, a view doesn't contain data physically. Instead, it contains only the blank cell, and its value is obtained from the underlying tables in terms of the defined query. This makes views an important concept in data systems to reduce data complexity to simple data access to increase data presentation security and flexibility.
Views are commonly used to:
Simplify Complex Queries: The creation of a view in RDBMS helps users access data through simple operations such as joins and filters, which they may have written and tested multiple times, yet they should not have to rewrite the whole complex join, filter or aggregation.
Enhance Security: Views offer a method of limiting the use of certain columns and/or rows of a database. One can easily understand this by defining views that will enable the DBA to display only certain information to certain users or applications.
Present Data Flexibly: Views work in the same manner as files but let data be shown according to user preferences without changing the structure in the database. That is, one user may only require data summarisation for overall sales, while another may require actual transactions. Views can meet all these requirements.
Creating a view in DBMS means using an SQL statement to define a virtual table based on a query. Basically, a view can simplify complex data by combining information from one or more tables and showing it to the user. Once a view is created, it acts like a regular table, but it does not store data. Instead, it displays data based on the query that is asked. To create a view, the CREATE VIEW command is used, followed by the SQL query that selects the data you want to display.
Here's how the pseudocode for views in DBMS goes to retrieve data from one or more tables with a specific query:
START
DEFINE ViewName
DEFINE SELECT Query (SELECT columns FROM table WHERE conditions)
CREATE VIEW ViewName AS
SELECT Query
END
When we define views in DBMS, they can be categorised based on their functionality and the way they interact with the underlying data. The main types of views in DBMS are:
A simple view in DBMS is derived from a single table that displays specific rows or columns based on set criteria. It's lightweight, easy to maintain, and perfect for basic use cases. Since it involves just one base table, it doesn't include joins, subqueries, or aggregate functions. Simple views show real-time changes made to the underlying table and don't store data physically. They support basic updates and queries, making them ideal for scenarios that need minimal data abstraction, such as presenting filtered or restricted data views.
Here is a simple view that shows employees who belong to the HR department, including their employee ID, first name and last name:
CREATE VIEW HR_Employees AS
SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE Department = 'HR';
A complex view in SQL DBMS is created from multiple tables and often incorporates joins, subqueries, or aggregate functions, making it ideal for handling sophisticated queries. Complex views can combine data from different sources, use advanced SQL features like grouping and aggregations, and may include computed columns. These views dynamically reflect changes in the underlying tables and are well-suited for reporting, analysis, and advanced data modelling tasks. They help summarise or analyse large datasets, providing a powerful tool for managing intricate data requirements.
This join view combines data from two separate Customers and Orders tables using a JOIN operation based on the CustomerID. It retrieves the CustomerID, Name, OrderID, and Amount for each order and gives a unified view of customer and order details.
CREATE VIEW OrderSummary AS
SELECT Customers.CustomerID, Customers.Name, Orders.OrderID, Orders.Amount
FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
This inline view is a temporary subquery used within a larger query. It first selects CustomerID and Name from the Customers table where the country is 'USA'. The inline view is then joined with the Orders table to display orders from customers based in the USA.
SELECT * FROM (
SELECT CustomerID, Name
FROM Customers
WHERE Country = 'USA'
) AS USA_Customers
JOIN Orders ON USA_Customers.CustomerID = Orders.CustomerID;
A materialised view physically stores the results of a query in the database, which eliminates the need to recalculate the data each time the view is accessed. This makes it a useful tool for improving performance, especially for resource-intensive queries. Unlike regular views, materialised views occupy physical storage and require periodic refreshes to stay up-to-date with the underlying data. These refreshes can be done on-demand or scheduled, depending on the system's needs.
Materialised views are commonly used in OLAP (Online Analytical Processing) systems where complex aggregations and joins are frequent. By storing precomputed results, they significantly reduce the database workload, avoid repetitive calculations and speed up query execution for large datasets.
Here's a materialised view of the sales summary that stores the total sales for each product:
CREATE MATERIALIZED VIEW SalesSummary AS
SELECT ProductID, SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY ProductID;
Consider a table called Employees with the following structure:
| EmployeeID | FirstName | LastName | Department | Salary |
|---|---|---|---|---|
| 1 | John | Doe | HR | 5000 |
| 2 | Jane | Smith | IT | 6000 |
| 3 | Sam | Brown | HR | 5500 |
| 4 | Lucy | White | IT | 7000 |
Now, let's create a view to display the employees from the "HR" department only:
CREATE VIEW HR_Employees AS
SELECT EmployeeID, FirstName, LastName, Department, Salary
FROM Employees
WHERE Department = 'HR';
Once this view is created, you can query the view just like a table:
SELECT * FROM HR_Employees;
| EmployeeID | FirstName | LastName | Department | Salary |
|---|---|---|---|---|
| 1 | John | Doe | HR | 5000 |
| 3 | Sam | Brown | HR | 5500 |
To create views from multiple tables, consider two tables: Employees and Departments:
| Emp_id | Emp_name | Dept_id | salary |
|---|---|---|---|
| 1 | Alice | 101 | 50000 |
| 2 | Bob | 102 | 55000 |
| 3 | Charlie | 101 | 45000 |
| 4 | Diana | 103 | 60000 |
| Dept_id | Dept_name |
|---|---|
| 101 | HR |
| 102 | IT |
| 103 | Finance |
We want to create a view called EmployeeDetails that shows each employee's name, salary, and department name by joining the Employees and Departments tables.
CREATE VIEW EmployeeDetails AS
SELECT E.emp_name, E.salary, D.dept_name
FROM Employees E
JOIN Departments D ON E.dept_id = D.dept_id;
Once the view is created, you can query the EmployeeDetails view like a regular table:
SELECT * FROM EmployeeDetails;
| Emp_name | salary | Dept_name |
|---|---|---|
| Alice | 50000 | HR |
| Bob | 55000 | IT |
| Charlie | 45000 | HR |
| Diana | 60000 | Finance |
Here's an example of updating and deleting an existing view using the same employees and departments tables:
If you need to update an existing view to include additional information or change the existing columns, you can use the CREATE OR REPLACE VIEW command. For instance, let's say we want to include the employee's emp_id in the EmployeeDetails view.
CREATE OR REPLACE VIEW EmployeeDetails AS
SELECT E.emp_id, E.emp_name, E.salary, D.dept_name
FROM Employees E
JOIN Departments D ON E.dept_id = D.dept_id;
Once executed, the updated view will include the emp_id:
| Emp_id | Emp_name | salary | Dept_name |
|---|---|---|---|
| 1 | Alice | 50000 | HR |
| 2 | Bob | 55000 | IT |
| 3 | Charlie | 45000 | HR |
| 4 | Diana | 60000 | Finance |
If you no longer need a view, you can delete it using the DROP VIEW command. For instance, if you want to remove the EmployeeDetails view, here's how you would do it:
DROP VIEW EmployeeDetails;
DROP VIEW EmployeeDetails: This command deletes the EmployeeDetails view from the database. After executing it, the view no longer exists, and you can't query it anymore.
Using the same EmployeeDetails view from the previous examples, let's see how to drop a view:
To remove the EmployeeDetails view from the database, you can use the DROP VIEW command:
DROP VIEW EmployeeDetails;
DROP VIEW EmployeeDetails: This command completely removes the EmployeeDetails view from the database. Once executed, you will no longer be able to query or access the EmployeeDetails view.
Once the view is dropped, any attempts to query it will result in an error:
SELECT * FROM EmployeeDetails;
This will return an error message:
ERROR: view "EmployeeDetails" does not exist
Consider the following Employees table:
| EmployeeID | FirstName | LastName | Department |
|---|---|---|---|
| 101 | John | Doe | HR |
| 102 | Jane | Smith | IT |
Now, we create a view that shows only employees from the HR department:
CREATE VIEW HR_Employees AS
SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE Department = 'HR';
INSERT INTO HR_Employees (EmployeeID, FirstName, LastName)
VALUES (103, 'Alice', 'Johnson');
The code inserts a new row into the HR_Employees view. The view is based on the employees' table and is filtered by the HR department. Therefore, the inserted data will be stored in the Employees table in the HR department. In this example, a new employee (EmployeeID = 103, FirstName = 'Alice', LastName = 'Johnson') is added. The row will automatically appear in the HR_Employees view because it meets the requirements for belonging to the HR department.
| EmployeeID | FirstName | LastName |
|---|---|---|
| 101 | John | Doe |
| 102 | Jane | Smith |
| 103 | Alice | Johnson |
For this example, consider two tables: Customers and Orders.
| CustomerID | Name | City |
|---|---|---|
| 1 | John Doe | New York |
| 2 | Jane Smith | Chicago |
| 3 | Mary Lee | Los Angeles |
| OrderID | CustomerID | Amount | OrderDate |
|---|---|---|---|
| 101 | 1 | 250 | 2024-01-15 |
| 102 | 2 | 300 | 2024-02-05 |
| 103 | 1 | 150 | 2024-03-12 |
| 104 | 3 | 450 | 2024-01-25 |
Now, let's create a view that combines data from both tables to show each customer's total order amount for the year 2024.
CREATE VIEW CustomerOrderSummary AS
SELECT Customers.CustomerID, Customers.Name, SUM(Orders.Amount) AS TotalAmount
FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.OrderDate BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY Customers.CustomerID, Customers.Name;
This view, CustomerOrderSummary, combines data from the Customers and Orders tables. It uses a JOIN to link customers with their orders and filter orders made in 2024. Then, the results are grouped by CustomerID and Name. The SUM function calculates the total order amount for each customer during the year 2024.
| CustomerID | Name | TotalAmount |
|---|---|---|
| 1 | John Doe | 400 |
| 2 | Jane Smith | 300 |
| 3 | Mary Lee | 450 |
The WITH CHECK OPTION clause is used in SQL to ensure that any data inserted or updated through a view must meet the criteria defined in the view's SELECT statement. If an insert or update operation violates the conditions set in the view, the operation will fail.
Let's consider the following Employees table:
| EmployeeID | FirstName | LastName | Department |
|---|---|---|---|
| 101 | John | Doe | HR |
| 102 | Jane | Smith | IT |
| 103 | Mary | Johnson | HR |
Now, we create a view that shows only employees from the HR department and apply the WITH CHECK OPTION clause so that no other department can be inserted through this view.
CREATE VIEW HR_Employees AS
SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE Department = 'HR'
WITH CHECK OPTION;
INSERT INTO HR_Employees (EmployeeID, FirstName, LastName)
VALUES (104, 'Alice', 'Johnson');
INSERT INTO HR_Employees (EmployeeID, FirstName, LastName, Department)
VALUES (105, 'Bob', 'Miller', 'IT'); -- This will fail because 'IT' is not part of the HR department
In this example, the HR_Employees view is restricted to only show employees from the HR department. The WITH CHECK OPTION won't allow any insert or update through this view because it must also meet the Department = 'HR' condition. The first INSERT statement succeeds because it's inserting an employee from the HR department. However, the second INSERT statement fails because it attempts to insert an employee into the IT department which violates the condition defined in the view.
Here are some views in DBMS applications:
What view serializability does is to make transaction schedules produce results equivalent to a serial execution where transactions run sequentially. For a schedule to be view serialisable, it must satisfy three conditions. Firstly, transactions must read data from the same source as in the serial schedule. Second, the updates must yield identical results. Thirdly, the final write must match the serial order. It is useful when conflict serializability is too restrictive, enabling greater concurrency. However, it is harder to implement and verify, which makes it more complex than conflict serializability.
For creating a view in RDBMS you can start by defining a virtual table based on a SQL query that retrieves data from one or more base tables. Consider the following example:
CREATE VIEW Employee_View AS
SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE Department = 'HR';
The Employee_View presents only the employees from the HR department. Whenever this view is queried, it pulls the relevant data from the Employees table.
Learning views in DBMS is crucial because they simplify complex data interactions, enhance security, and improve query performance. They allow you to work with abstracted data, reduce redundancy, and maintain consistency. Mastering views enables more efficient database management and ensures better data access control, making it an essential skill for anyone working with databases. To learn more, enroll in the CCBP Academy 4.0 program.
Views provide a customised and controlled representation of the database. It allows users to access relevant data without exposing sensitive information. They simplify data access and enhance security by limiting what data is visible to different users.
No, views are not physically stored as separate tables. They are virtual tables that generate content dynamically from the underlying base tables whenever they are queried.
While views are generally read-only, some DBMS systems support updatable views with certain restrictions. Those allow limited modifications through the view itself.
Materialised views store the actual data, which makes them faster to query, but they require periodic refreshes to stay in sync with the underlying tables. Regular views, on the other hand, don't store data and fetch it on demand whenever queried.
A virtual view is dynamically generated each time it's queried. A materialised view stores data persistently, requiring periodic refreshes to ensure consistency with the underlying data.