All 40 lessons

Lesson 35 of 40 · Advanced SQL

SQL Views

By Skillancy Editorial Team

Every example runs on the ShopKart dataset, already loaded in the free SQL Compiler. Click Try it in the SQL Compiler under a query to open it there, then press Run.

A view is a saved query that you can select from like a table. Instead of copying the same 15-line revenue query into every report, you save it once as a view and everyone queries SELECT * FROM order_revenue. A materialized view goes further and stores the result for speed.

Syntax

CREATE VIEW view_name AS
SELECT ...;

CREATE OR REPLACE VIEW view_name AS
SELECT ...;

DROP VIEW view_name;

Examples

1. Create a view

One row per order line with the business logic built in:

CREATE VIEW order_lines AS
SELECT o.order_id,
       o.order_date,
       o.status,
       c.customer_name,
       c.city,
       p.product_name,
       cat.category_name,
       oi.quantity,
       ROUND(oi.quantity * oi.unit_price * (1 - oi.discount_pct / 100), 2) AS net_amount
FROM orders o
JOIN customers c    ON c.customer_id = o.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p     ON p.product_id = oi.product_id
JOIN categories cat ON cat.category_id = p.category_id;

Try it in the SQL Compiler

Result:

Query OK, 0 row(s) affected.

2. Query the view like a table

SELECT customer_name, product_name, net_amount
FROM order_lines
WHERE status = 'Delivered'
ORDER BY net_amount DESC
LIMIT 4;

Result:

customer_name	product_name	net_amount
Rohan Gupta	Samsung 43" Smart TV	27191.50
Farhan Khan	Redmi Note 13 Pro	22799.05
Aarav Sharma	Redmi Note 13 Pro	21599.10
Priya Nair	Women's Silk Saree	8074.05

(4 rows)

Revenue by city, now a two-line query:

SELECT city, SUM(net_amount) AS revenue
FROM order_lines
WHERE status = 'Delivered'
GROUP BY city
ORDER BY revenue DESC
LIMIT 3;

Result:

city	revenue
Delhi	33183.50
Bengaluru	28591.30
Lucknow	24098.05

(3 rows)

3. Views are always current

A view re-runs its query each time, so new data shows up immediately:

UPDATE orders SET status = 'Delivered', delivery_date = '2026-02-14' WHERE order_id = 1024;

SELECT order_id, status, product_name
FROM order_lines
WHERE order_id = 1024;

Result:

order_id	status	product_name
1024	Delivered	Men's Cotton Kurta
1024	Delivered	Non-stick Tawa

(2 rows)

4. Replace a view

Add a column with CREATE OR REPLACE VIEW. In PostgreSQL, new columns must go at the end:

CREATE OR REPLACE VIEW order_lines AS
SELECT o.order_id, o.order_date, o.status, c.customer_name, c.city,
       p.product_name, cat.category_name, oi.quantity,
       ROUND(oi.quantity * oi.unit_price * (1 - oi.discount_pct / 100), 2) AS net_amount,
       o.payment_method
FROM orders o
JOIN customers c    ON c.customer_id = o.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p     ON p.product_id = oi.product_id
JOIN categories cat ON cat.category_id = p.category_id;

SELECT payment_method, COUNT(*) AS lines FROM order_lines GROUP BY payment_method ORDER BY lines DESC;

Try it in the SQL Compiler

Result:

payment_method	lines
UPI	19
Card	10
COD	5
NetBanking	4

(4 rows)

5. Views for access control

Share data without exposing sensitive columns. Here, the marketing team sees customers without phone or email:

CREATE VIEW customer_directory AS
SELECT customer_id, customer_name, city, state, is_prime
FROM customers;

SELECT * FROM customer_directory LIMIT 3;

Try it in the SQL Compiler

Result:

customer_id	customer_name	city	state	is_prime
1	Aarav Sharma	Bengaluru	Karnataka	true
2	Priya Nair	Kochi	Kerala	false
3	Rohan Gupta	Delhi	Delhi	true

(3 rows)

You can then grant access to the view but not the table.

Materialized views

A materialized view runs the query once and stores the rows. Reads are fast, but data only updates when you refresh it.

CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT DATE_TRUNC('month', order_date)::date AS month,
       SUM(net_amount) AS revenue
FROM order_lines
WHERE status = 'Delivered'
GROUP BY 1;

SELECT * FROM monthly_revenue WHERE month >= '2026-01-01' ORDER BY month;

Result:

month	revenue
2026-02-01	2703.05

(1 row)

Order 1024 is in February 2026. Now another order is delivered:

UPDATE orders SET status = 'Delivered', delivery_date = '2026-01-30' WHERE order_id = 1023;

SELECT * FROM monthly_revenue WHERE month >= '2026-01-01' ORDER BY month;

Result:

month	revenue
2026-02-01	2703.05

(1 row)

January 2026 still isn't there: the stored result is stale. Refresh it:

REFRESH MATERIALIZED VIEW monthly_revenue;

SELECT * FROM monthly_revenue WHERE month >= '2026-01-01' ORDER BY month;

Result:

month	revenue
2026-01-01	2196.00
2026-02-01	2703.05

(2 rows)

	View	Materialized view	Table
Stores data	no	yes	yes
Always current	yes	after `REFRESH`	when you update it
Read speed	same as the query	fast	fast
Typical use	reusable logic, access control	heavy dashboards	source data

Common mistakes

Stacking views on views on views. Each layer hides logic and can make queries slow and hard to debug.
Forgetting to refresh materialized views. Schedule REFRESH (for example nightly) or reports go stale.
Using SELECT * in a view definition. The column list is fixed when the view is created; new table columns won't appear.

Practice question

Create a view product_performance showing each product's name, category name, units sold and net revenue (delivered orders only, including products with no sales). Query the top 3.

Show solution

CREATE VIEW product_performance AS
SELECT p.product_name,
       cat.category_name,
       COALESCE(SUM(oi.quantity) FILTER (WHERE o.status = 'Delivered'), 0) AS units_sold,
       COALESCE(SUM(oi.quantity * oi.unit_price * (1 - oi.discount_pct / 100))
                FILTER (WHERE o.status = 'Delivered'), 0) AS net_revenue
FROM products p
JOIN categories cat      ON cat.category_id = p.category_id
LEFT JOIN order_items oi ON oi.product_id = p.product_id
LEFT JOIN orders o       ON o.order_id = oi.order_id
GROUP BY p.product_name, cat.category_name;

SELECT product_name, units_sold, ROUND(net_revenue, 0) AS net_revenue
FROM product_performance
ORDER BY net_revenue DESC
LIMIT 3;

Try it in the SQL Compiler

Result:

product_name	units_sold	net_revenue
Redmi Note 13 Pro	2	44398
Samsung 43" Smart TV	1	27192
Women's Silk Saree	2	15723

(3 rows)

Key takeaways

A view is a saved query; selecting from it runs the query.
Views centralise business logic and can hide sensitive columns.
CREATE OR REPLACE VIEW updates a view's definition.
Materialized views store results for speed and need REFRESH.

Frequently asked questions

Does a view store data?

A normal view does not. It stores the query, which runs every time you select from the view, so results are always current. A materialized view stores the result and must be refreshed.

Are views slower than tables?

Selecting from a normal view costs the same as running its query. If the query is heavy and read often, a materialized view is faster, at the cost of slightly stale data.

Can I insert into a view?

Simple views over a single table without GROUP BY, DISTINCT or joins are updatable in PostgreSQL. Views with aggregates or joins are read-only unless you add INSTEAD OF triggers.

← PreviousSQL Transactions Next →SQL Indexes and Query Optimization

Newsletter

Keep learning SQL

One email a week with SQL and analytics guides, new practice questions and cohort dates. No spam, unsubscribe anytime.

Prefer a one-page summary? Download the free SQL cheat sheet (PDF)