Views
Advanced⏱️ 10 mins read
What You'll Learn
A VIEW is a saved SQL query stored in the database that acts as a virtual table. It doesn't store data itself — it re-runs the underlying query each time it's accessed. Use views for: security (hide sensitive columns), simplifying complex joins, and providing a stable interface while underlying tables evolve.
Syntax
CREATE VIEW view_name AS SELECT ...;
SELECT * FROM view_name;
CREATE OR REPLACE VIEW view_name AS SELECT ...;
DROP VIEW view_name;Example
-- Create a view for active customers
CREATE VIEW active_customers AS
SELECT id, name, email, total_orders
FROM customers
WHERE last_order_date >= DATE_SUB(NOW(), INTERVAL 90 DAY);
-- Use it like a table
SELECT * FROM active_customers
WHERE total_orders > 5;
-- View with complex join
CREATE VIEW order_summary AS
SELECT
o.id, c.name, c.email,
o.total, o.status, o.order_date
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;Common Mistakes
Views don't automatically perform better than the raw query — they still execute the full query each time (unless it's a Materialized View in PostgreSQL). Don't confuse views with caching. Updating data through a view has restrictions.
Interview Tips
Mention Materialized Views (PostgreSQL) — they store the result and must be refreshed manually. Good for expensive analytics queries that don't need real-time data. Also: views can grant users access to specific columns, hiding sensitive data like salary.
Practice
Create a view showing each salesperson's name, their total sales amount, and their rank by total sales. Then query the view to show only the top 10 salespeople.