DBMS - Views in SQL

Views in SQL are like virtual tables that do not store data themselves but act as saved SQL queries that we can reuse. We can think of views as a snapshot of a query result. Views give us a simplified way to look at or interact with the data.

Views are quite handy in improving query readability. They can also be used for restricting access to sensitive data, or combining data from multiple tables into a single view. In this chapter, we will use a set of examples to demonstrate what views are, their types, and how to use them effectively in practical aspects.

What are SQL Views?

A view is a predefined SQL query stored in the database. It acts like a virtual table that updates automatically whenever the underlying data changes. We can use views to −

• Simplify complex queries
• Create customized perspectives for different users
• Restrict access to specific columns or rows

Sample Tables and Data

We will use the following set of tables in the examples of this chapter to demonstrate the practical aspects of using views in SQL −

Here is the Customers table −

This is the Products table −

And, the Orders table is as follows −

Creating Views in SQL

To work with views we must see how we can make a view from the existing table. We can create a view using the CREATE VIEW statement. When a view is created, we can query it like a table.

Example: View for Customer Orders

Let us create a view to display customer names along with the products they ordered.

CREATE VIEW CustomerOrders AS
SELECT c.first_name, c.last_name, p.product_name, o.quantity, o.order_date
FROM Orders o
JOIN Customers c ON o.customer_id = c.customer_id
JOIN Products p ON o.product_id = p.product_id;

Use the following query to fetch all the records from the CustomerOrders view −

SELECT * FROM CustomerOrders;

It will fetch all the records from the view −

This view combines data from three tables, which makes it easier to fetch customer orders.

Updating the Data Using Views

Some views allow updates, but it depends on the complexity of the view and database constraints.

Example: Update through a Simple View

Let us make a view to display product stock levels.

CREATE VIEW ProductStock AS
SELECT product_id, product_name, stock_quantity
FROM Products;

Use the following query to fetch the details from the ProductStock view −

SELECT * FROM ProductStock;

It will fetch the records from the ProductStock view −

Updating the Stock Quantity through the View

Let us now use the following query to update the ProductStock view −

UPDATE ProductStock
SET stock_quantity = stock_quantity - 1
WHERE product_id = 101;

Now, query the ProductStock view again −

SELECT * FROM ProductStock;

It will fetch the updated data from the ProductStock view −

Here, we can see the update reflects in the underlying Products table as well.

Types of Views in SQL

SQL views can be categorized into two main types −

• Simple Views
• Complex Views

Let's start with a simple example of Simple Views.

Simple Views

Simple views are based on a single table. Simple views allow data updates if no calculations or aggregate functions are used.

Example: Simple View for Customers

In this query, we are creating a simple view from the Customers table −

CREATE VIEW CustomerDetails AS
SELECT first_name, last_name, email
FROM Customers;

Complex Views

Complex Views combine data from multiple tables or include calculations. They can also be aggregate functions or GROUP BY clauses. Complex Views generally do not allow updates.

Example: Complex View for Total Revenue

Let us make a view to calculate the total revenue for each product −

CREATE VIEW ProductRevenue AS
SELECT p.product_name, SUM(o.quantity * p.price) AS total_revenue
FROM Orders o
JOIN Products p ON o.product_id = p.product_id
GROUP BY p.product_name;

Use the following query to fetch the data from the ProductRevenue view −

SELECT * FROM ProductRevenue;

This complex view fetches the revenue generated by each product −

Dropping the Views

Like we delete tables, we can delete views as well. If we no longer need a view, we can delete it using the DROP VIEW statement.

Example: Drop a View

Use the following query to drop the ProductStock view −

DROP VIEW ProductStock;

It removes the ProductStock view but does not affect the underlying Products table.

Advantages and Disadvantages of Using Views

The following table highlights the advantages and disadvantages of using views in SQL queries −

Conclusion

Views in SQL help simplify queries and make data management easier. In this chapter, we covered in detail how to use views in SQL. We understood how to create simple and complex views, how to query data from the views, and even how to update the data through the views. Using views, we can improve the readability of the SQL queries and also apply security and reusability features.