Play Store Application link – SQL in 18 steps – App on Google Play

Introduction

Imagine you manage an online store, and your sales team frequently needs a list of high-value customers (those who placed orders above $500). Instead of writing the same complex query every time, SQL Views let you save that query as a virtual table.

A View is a stored SQL query that acts like a table but doesn’t store actual data—it just presents data dynamically from underlying tables.

1. Creating and Using Views

Views simplify data access, improve security, and make complex queries reusable.

Example Scenario:

Create a view for high-value customers.

CREATE VIEW HighValueCustomers AS
SELECT c.customer_id, c.name, SUM(o.amount) AS total_spent
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
HAVING SUM(o.amount) > 500;

Now, the sales team can get high-value customers by simply running:

SELECT * FROM HighValueCustomers;

2. Updating Views

Some views allow updates if they directly map to a single table without aggregations.

Example Scenario:

Suppose we have a view of active customers:

CREATE VIEW ActiveCustomers AS
SELECT customer_id, name, status FROM Customers WHERE status = 'Active';

We can update this view like a table:

UPDATE ActiveCustomers SET status = 'Inactive' WHERE customer_id = 101;

But if the view has JOIN, GROUP BY, or DISTINCT, updating directly may not work.

3. Dropping Views

If a view is no longer needed, it can be removed using:

DROP VIEW HighValueCustomers;

This does not affect the original tables—just the saved query.

4. Materialized Views – Stored Query Results

Unlike regular views, Materialized Views store query results, making them faster for large datasets.

MySQL vs Oracle:

• MySQL doesn’t support materialized views natively but allows similar functionality using tables and triggers.
• Oracle supports materialized views with the REFRESH option to update stored data.

Example Scenario (Oracle):

Create a materialized view for product sales data:

CREATE MATERIALIZED VIEW ProductSales
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT product_id, SUM(amount) AS total_sales FROM Orders GROUP BY product_id;

This view stores the result and refreshes automatically when new orders arrive.

Conclusion

Views make SQL queries reusable and readable, while materialized views boost performance. Use them wisely to simplify reporting, improve security, and optimize query execution.