Level 3 - Question 4: Creating and Using Views

Scenario

Your company wants to simplify access to high-value customer data. Instead of writing complex queries repeatedly, you need to create a reusable view.

Task

Create a view named high_value_customers that shows customer name, email, and total order amount for customers who have spent more than $500. Then write a query to select from this view.

Sample Schema: `customers`

customer_id	name	email
1	John Doe	john@example.com
2	Jane Smith	jane@example.com

Sample Schema: `orders`

order_id	customer_id	amount
101	1	300
102	1	250
103	2	100

Solution:

-- Create the view
CREATE VIEW high_value_customers AS
SELECT c.name, c.email, SUM(o.amount) AS total_spent
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.name, c.email
HAVING SUM(o.amount) > 500;

-- Query the view
SELECT * FROM high_value_customers
ORDER BY total_spent DESC;

This view encapsulates the logic for identifying high-value customers. Once created, it can be queried like a regular table, simplifying future queries and ensuring consistency.

Previous Question Next Question