Types of Views in SQL – Simple, Complex & Materialized Views Explained (With Examples & Practice)

Hey, I'm Gaurav.

When I first heard about Views in SQL interviews, I thought they were just “some fancy SELECT statement”.

I was wrong.

Views turned out to be one of the most practical tools for writing clean, secure, and maintainable queries — especially in real projects and interviews.

Today, I’m sharing exactly how I went from confused to confident with Views. We’ll cover:

- What Views are

- Simple Views

- Complex Views

- Materialized Views

- When to use each

All examples use our familiar e-commerce database (`users`, `products`, `orders`). You can practice every question live here: [sqlpractice.in](https://sqlpractice.in)

Let’s go step by step.

What is a View? (Simple Explanation)

A View is like a virtual table — it’s a saved SQL query that you can treat as a table.

Noob thinking:

“Why should I create a view when I can just write the query every time?”

Better thinking:

- Some queries are used again and again.

- Writing the same long JOIN every time is error-prone and hard to maintain.

- Views let you hide complexity and give a clean interface.

1. Simple Views

A Simple View is based on one table and doesn’t contain GROUP BY, aggregates, or complex logic.

Example:

```sql

CREATE VIEW active_users AS

SELECT id, name, email, created_at

FROM users

WHERE created_at >= '2025-01-01';

```

Now you can use it like a normal table:

```sql

SELECT FROM active_users;

```

Advantages:

- Hides sensitive columns (e.g., hide phone number)

- Simplifies frequent filters

- Easy to maintain — change the view once, all queries using it update automatically

Practice Question 1 (Easy)

Create a view that shows only products with stock greater than 50.

[Try it live →](https://sqlpractice.in/?question=simple-view)

2. Complex Views

Complex Views can include:

- JOINs between multiple tables

- Aggregations (GROUP BY, HAVING)

- Subqueries

Example: View showing customer name, total orders, and total spent

```sql

CREATE VIEW customer_summary AS

SELECT

u.id,

u.name,

COUNT(o.id) AS total_orders,

SUM(o.total_price) AS total_spent,

MAX(o.ordered_at) AS last_order_date

FROM users u

LEFT JOIN orders o ON u.id = o.user_id

GROUP BY u.id, u.name;

```

Now you can query it simply:

```sql

SELECT FROM customer_summary

WHERE total_spent > 10000;

```

Practice Question 2 (Medium)

Create a view that shows product name and how many times it has been ordered.

[Try it live →](https://sqlpractice.in/?question=complex-view)

Practice Question 3 (Hard)

Create a view showing top 5 customers by total spending.

[Try it live →](https://sqlpractice.in/?question=complex-view-top-customers)

3. Materialized Views (The Performance Booster)

A Materialized View is a physical copy of the query result stored on disk.

Key Difference:

- Normal View = query runs every time you SELECT from it

- Materialized View = result is pre-computed and stored → much faster

Syntax (PostgreSQL):

```sql

CREATE MATERIALIZED VIEW customer_summary_mat AS

SELECT

u.name,

COUNT(o.id) AS total_orders,

SUM(o.total_price) AS total_spent

FROM users u

LEFT JOIN orders o ON u.id = o.user_id

GROUP BY u.name;

-- Refresh when needed

REFRESH MATERIALIZED VIEW customer_summary_mat;

```

When to use Materialized Views:

- Heavy reports that run frequently

- Complex aggregations on large tables

- Dashboards that don’t need real-time data

Practice Question 4 (Hard)

Create a materialized view for monthly sales summary and refresh it.

[Try it live →](https://sqlpractice.in/?question=materialized-view)

Common Interview Questions on Views

1. "What is the difference between View and Materialized View?"

2. "When would you use a View instead of a direct query?"

3. "Can we update data through a View?"

4. "What are the limitations of Views?"

Practice Question 5–7 (Interview Level)

- Create a view for active orders only

- Create a view that joins users, orders, and products

- Explain when you would choose Materialized View over normal View

[Full Views practice pack →](https://sqlpractice.in/?set=views-pack)

Final Thoughts

I used to think Views were just shortcuts.

Now I see them as clean interfaces between raw data and the application.

Simple Views → hide complexity

Complex Views → simplify reporting

Materialized Views → boost performance for heavy queries

Go practice creating and using Views on [sqlpractice.in](https://sqlpractice.in) — you’ll see how much cleaner your queries become.

Next article: Common Constraints in SQL (NOT NULL, UNIQUE, CHECK, DEFAULT, etc.)

Keep practicing — one concept at a time! 💪

Building sqlpractice.in to help everyone master SQL for interviews — completely free.

---

Would you like me to write the next one ("Common Constraints in SQL") right now?

Or do you want any changes in this Views article before publishing?

Let me know! 🚀