Lesson 5 - Creating and Using Views

Welcome to Views#

A CTE is brilliant for organizing a single query — but it vanishes the moment the query finishes. What if you have a query you run constantly, and you want to save it permanently so anyone can use it without rewriting it? That is exactly what a view does: it stores a SELECT statement under a name and behaves like a virtual table.

In this lesson you will create views, query them, build views on top of other views, and learn how to drop them safely.

By the end of this lesson, you will be able to:

• Explain what a view is and why views are useful
• Create a view with CREATE VIEW and query it like a table
• Write complex views that include JOIN, GROUP BY, and HAVING
• Build a view on top of another view
• Drop views with DROP VIEW, including dependent views with CASCADE

You should be comfortable with subqueries and CTEs from the earlier lessons. This lesson uses PostgreSQL, which supports a few features SQLite does not. Let’s begin.

What Is a View?#

A view is a virtual table backed by a saved SELECT statement. It is created fresh each time you query it, so it always reflects the current data in the underlying tables. Unlike a real table, a view stores no data of its own — only the query definition. But like a table, you can SELECT from it and join it with other views and tables.

Why Use Views?#

Views earn their keep in three ways:

• Hide complexity. Once a gnarly query is wrapped in a view, you reuse it by name without ever re-reading the underlying SQL.
• Improve security. You can expose only certain columns or rows of a table, granting users access to a safe subset instead of the whole table.
• Reshape data. A view can present data in a different format from the underlying tables — renamed columns, computed fields, masked values.

Views vs. CTEs#

Views and CTEs both organize complex logic, but they differ in important ways:

• A view is a database object stored permanently; a CTE exists only in memory for the duration of one query.
• Use a view for queries you run frequently; use a CTE for logic referenced once.
• Views can enforce access control; CTEs cannot.

We will work again with the Chinook digital music store.

Creating and Querying a View#

Creating a view is as simple as putting a name in front of a SELECT statement. The general syntax is:

CREATE VIEW view_name [ ( column_name [, ...] ) ]
AS
<select-statement>;

The part in square brackets — the list of column names — is optional. Include it only when you want to rename the view’s columns.

Your First View#

This view returns customers’ personal information but masks their email addresses, exposing only the part after the first four characters:

CREATE VIEW customer_email (
       customer_id, first_name, last_name, country, partial_email
  ) AS
SELECT customer_id, first_name,
       last_name, country,
       '****' || SUBSTRING(email, 5) AS email
  FROM customer;

Now query it exactly as you would a table:

SELECT *
  FROM customer_email
 LIMIT 3;

Output:

This is the security benefit in action: anyone with access to customer_email sees masked addresses, never the raw ones. CREATE VIEW is a SQL statement that ends with a semicolon, so you can run the CREATE VIEW and the SELECT back to back as two statements.

Optional Column Names#

Because the column list is optional, the simplest views skip it and keep the underlying column names. Suppose Chinook is running a promotion for German customers:

CREATE VIEW german_customers AS
SELECT *
  FROM customer
 WHERE country = 'Germany';

SELECT *
  FROM german_customers;

No renaming, no fuss — the view exposes the German customers and keeps the original column names.

Dropping a View#

You will often need to remove a view — because it is no longer used, or because you want to redefine it. If you try to recreate a view that already exists, you get an error like table view_name already exists. The fix is to delete the old one first with DROP VIEW:

DROP VIEW view_name;

For example, create a manager view, then drop it:

CREATE VIEW manager AS
SELECT employee_id, first_name, last_name, title, email
  FROM employee
 WHERE employee_id IN (SELECT DISTINCT reports_to FROM employee);

DROP VIEW manager;

After the DROP, querying manager returns an error because the view no longer exists. Get comfortable creating and dropping views as you iterate — it is a normal part of building them.

Creating Complex Views#

Views are most valuable when they wrap complex logic — joins, grouping, filtering — so you can reuse it with a simple SELECT.

A View With JOIN, GROUP BY, and HAVING#

Suppose the company wants to discount Rock albums that contain more than fifteen tracks. This view returns those albums and their current total price:

CREATE VIEW rock_albums_with_more_15_songs
       (genre_name, album_title, album_id, number_of_tracks, total_amount) AS
SELECT g.name, a.title, a.album_id, COUNT(t.track_id), SUM(t.unit_price)
  FROM album AS a
 INNER JOIN track AS t
    ON a.album_id = t.album_id
 INNER JOIN genre AS g
    ON t.genre_id = g.genre_id
 WHERE g.name = 'Rock'
 GROUP BY g.name, a.title, a.album_id
HAVING COUNT(t.track_id) > 15
 ORDER BY COUNT(t.track_id) DESC;

With the view in place, applying a 50% discount is trivial — you query the view as if the hard work were already a table:

SELECT album_title, genre_name, number_of_tracks,
       total_amount * 0.5 AS total_amount_after_discount
  FROM rock_albums_with_more_15_songs;

Output (first five rows):

All the join-and-group complexity is hidden behind one friendly name.

Building Views From Views#

When business logic gets layered, you can build a view on top of another view. This is cleaner than cramming everything into one enormous definition.

Layering Two Views#

First, a view of every track longer than 30 minutes:

CREATE VIEW songs_last_longer_30_min AS
SELECT name, genre_id, track_id,
       ROUND((milliseconds * 0.001) / 60, 2) AS minutes
  FROM track
 WHERE ROUND((milliseconds * 0.001) / 60, 2) > 30;

Now build a second view on top of the first to return only the long Drama tracks (genre 21):

CREATE VIEW drama_songs_last_longer_30_min
       (genre_name, song_name, length_in_min)
    AS
SELECT g.name, s.name, s.minutes
  FROM genre AS g
 INNER JOIN songs_last_longer_30_min AS s
    ON g.genre_id = s.genre_id
 WHERE g.genre_id = 21;

Query it like any table:

SELECT *
  FROM drama_songs_last_longer_30_min
 LIMIT 5;

Output:

The second view joined the genre table to the first view. Layering keeps each definition small and focused.

Dropping Views With Dependencies#

When one view depends on another, the database protects you from breaking the chain. If you try to drop songs_last_longer_30_min while drama_songs_last_longer_30_min still depends on it, PostgreSQL refuses:

cannot drop view songs_last_longer_30_min because other objects depend on it
DETAIL:  view drama_songs_last_longer_30_min depends on view songs_last_longer_30_min
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

To force the drop and remove the dependent views too, add the CASCADE option:

DROP VIEW songs_last_longer_30_min CASCADE;

This deletes songs_last_longer_30_min and every view that depends on it. Use CASCADE deliberately — it is powerful, and it will silently take dependent views with it.

Practice Exercises#

Use the Chinook database (PostgreSQL) for each exercise.

Exercise 1: A Simple View#

Create a view named usa_customers that returns all customers whose country is USA, then query it.

Exercise 2: A Revenue View#

Create a view genres_most_revenue with columns (genre_name, quantity, revenue) that joins invoice_line, track, and genre, groups by genre, and keeps only genres whose revenue (SUM(unit_price * quantity)) exceeds 100, ordered by revenue descending. Then query it.

Exercise 3: A View From a View#

Create a view customer_purchase returning (genre_name, unit_price, quantity, customer_id) by joining genre, track, invoice, and invoice_line. Then create a second view total_sales_customers on top of it that computes each genre’s total sales and customer count. Query the second view sorted by customer count descending, then drop customer_purchase with CASCADE.

Summary#

You learned that a view is a saved SELECT that behaves like a virtual table, storing the query rather than the data. You created simple and complex views, queried them like tables, layered views on top of one another, and removed them with DROP VIEW — using CASCADE to handle dependents.

Key Concepts#

• View — a virtual table defined by a saved SELECT; stores the query, not the data, and always reflects current data.
• CREATE VIEW — defines a view; the column-name list is optional.
• Querying a view — works exactly like querying a table, including in joins.
• View from a view — a view can be built on top of another view to layer logic.
• DROP VIEW ... CASCADE — deletes a view and every view that depends on it (PostgreSQL and most databases, not SQLite).

Why This Matters#

Views are how teams share trusted, reusable query logic across an organization. A well-named view hides complexity, enforces a consistent definition of a metric, and can restrict who sees which columns or rows. Analysts query views without knowing the joins underneath; security teams expose safe subsets of sensitive tables. Once you start saving your best queries as views, your whole database becomes easier to work with.

Next Steps#

You now have the full subquery, CTE, and view toolkit. It is time to put it to work on a realistic dataset, answering genuine business questions from start to finish.

Continue Building Your Skills#

Views turn your hard-won queries into durable building blocks. As you grow more advanced, you will combine views, CTEs, and subqueries fluidly — choosing whichever keeps the work clearest. Start a habit now: whenever you write a query you know you will run again, ask whether it deserves to be a view.