SQL Tutorial
•
•
4 min read
Types of Views in SQL – Simple, Complex & Materialized Views Explained (With Examples & Practice)
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.