📎 This article includes 2 downloadable practice files ↓

📘 SQL Beginner Course · Lesson 11 of 12

By now you’ve typed the same three-table JOIN several times. A view saves a query under a name, so it can be used like a table. Reports become short, and everyone uses the same definition of “sales value”.

Create a view

CREATE VIEW order_lines AS
SELECT o.order_id, o.order_date, c.name AS customer, c.region,
       p.name AS product, p.category, o.qty, o.qty * p.price AS value, o.status
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN products  p ON p.product_id  = o.product_id;

Use it like a table

SELECT customer, SUM(value) AS sales
FROM order_lines
WHERE status = 'Delivered'
GROUP BY customer
ORDER BY sales DESC
LIMIT 3;

SELECT strftime('%Y-%m', order_date) AS month, SUM(value) AS sales
FROM order_lines WHERE status = 'Delivered'
GROUP BY month ORDER BY month;

No joins needed. Excel and Power BI can connect to a view directly, which is a clean way to feed reports.

Views store the query, not the data

Every time you select from the view, the underlying query runs on current data. Add an order, and the view shows it immediately. (Materialised views in PostgreSQL/Oracle store results for speed, and must be refreshed.)

Change or remove

DROP VIEW order_lines;            -- then CREATE it again with changes
-- SQL Server / PostgreSQL: CREATE OR REPLACE VIEW (or ALTER VIEW)

Why teams use views

• One definition: “sales value” calculated the same way everywhere.
• Simplicity: analysts query a friendly view instead of 10 raw tables.
• Security: give access to a view without salary or PAN columns, not to the base table.

Limits

• Views on views on views get slow and hard to debug.
• Most views can’t be updated with INSERT/UPDATE.
• If a base table column is renamed, the view breaks.

Where beginners go wrong

Practice

Download shop.db and this lesson’s exercise file below. Open the database in the free DB Browser for SQLite (or sqlite3 shop.db), paste the exercises into the Execute SQL tab and write a query for each task. The expected result is printed under every task; answers are at the bottom of the file.