Working with Views

Views package complex queries into reusable, read-only virtual tables. They are perfect for the marketplace schema when different audiences need simplified snapshots of the same underlying data.

Learning Goals

• Create views to present customer, order, and product data
• Understand view maintenance (CREATE OR REPLACE, DROP)
• Apply views to security and reporting scenarios
• Recognize limitations, including updatability and performance costs

Creating a View

Create a view to show each customer's order history without repeating joins in every query.

Customer order history view

CREATE VIEW vw_customer_orders AS
SELECT o.order_id,
       c.customer_id,
       CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
       o.order_date,
       o.status,
       o.order_total
FROM orders AS o
JOIN customer AS c ON c.customer_id = o.customer_id;

Querying the view is just like querying a table:

SELECT *
FROM vw_customer_orders
WHERE customer_id = 2
  AND status = 'Delivered';

Views for Category Reporting

Summaries become easier to read when wrapped in a view.

Category revenue summary

CREATE VIEW vw_category_revenue AS
SELECT cat.category_name,
       COUNT(*) AS items_sold,
       SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_item AS oi
JOIN product AS p ON p.product_id = oi.product_id
JOIN category AS cat ON cat.category_id = p.category_id
GROUP BY cat.category_name;

SELECT *
FROM vw_category_revenue
ORDER BY revenue DESC;

Updating a View Definition

Use CREATE OR REPLACE VIEW when requirements change, such as adding seller details to product reporting.

Add seller detail to the category view

CREATE OR REPLACE VIEW vw_category_revenue AS
SELECT cat.category_name,
       s.store_name,
       COUNT(*) AS items_sold,
       SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_item AS oi
JOIN product AS p ON p.product_id = oi.product_id
JOIN category AS cat ON cat.category_id = p.category_id
LEFT JOIN seller AS s ON s.seller_id = p.seller_id
GROUP BY cat.category_name, s.store_name;

Drop a view when you no longer need it:

DROP VIEW IF EXISTS vw_category_revenue;

Security Layer

Grant access to a view instead of the base table to hide sensitive columns. For example, support agents might only need high-level customer data.

Hide customer emails from support

CREATE VIEW vw_public_customer_directory AS
SELECT customer_id,
       first_name,
       last_name,
       COALESCE(preferred_category_id, 0) AS preferred_category_id,
       signup_year
FROM customer;

Database administrators can then grant SELECT on the view without exposing emails stored in customer.

Limitations and Performance Notes

• Views execute the underlying query every time; complex joins may still be expensive.
• Most databases do not allow updates through views that include joins, aggregates, or DISTINCT.
• MySQL views are always virtual. If you need cached results, use a summary table or a scheduled refresh job.
• Changing column names in base tables can break dependent views - plan migrations accordingly.

Summary

• Views simplify repeated query logic and deliver curated perspectives on your data.
• CREATE VIEW defines the virtual table; CREATE OR REPLACE updates it; DROP VIEW removes it.
• Use views for reporting, security, and API-like abstractions in the marketplace schema.

Quiz

Show quiz

1. What does CREATE OR REPLACE VIEW allow you to do?
   A) Rename a table
   B) Update a view definition without dropping it
   C) Convert a view into a table
   D) Refresh a materialized view

2. Which view would you expose to limit access to customer email addresses?
   A) vw_customer_orders
   B) vw_public_customer_directory
   C) vw_category_revenue
   D) Any view automatically hides email addresses

3. True or False: Views in MySQL store their own copies of the data.
   A) True
   B) False

4. Why might a view be non-updatable?
   A) It references multiple tables and includes derived columns
   B) The table has a primary key
   C) The view name starts with vw_
   D) The view is queried too often

---

Answers

1. B
2. B
3. B
4. A