CREATE VIEW syntax in PostgreSQL - Step-by-Step Execution

Concept Flow - CREATE VIEW syntax

Write SELECT query

↓

Use CREATE VIEW name AS

↓

Database stores view definition

↓

View acts like a virtual table

↓

Query view like a table

You write a SELECT query, then create a view with that query. The database saves it as a virtual table you can query later.

Execution Sample

PostgreSQL

CREATE VIEW active_customers AS
SELECT id, name FROM customers
WHERE active = TRUE;

This creates a view named active_customers showing only active customers from the customers table.

Execution Table

Step	Action	Evaluation	Result
1	Parse CREATE VIEW statement	Syntax valid	Proceed to next step
2	Evaluate SELECT query	SELECT id, name FROM customers WHERE active = TRUE	Query stored as view definition
3	Store view definition in database	View name: active_customers	View created successfully
4	Query view later	SELECT * FROM active_customers	Returns id and name of active customers
5	Modify underlying table data	Add or remove active customers	View reflects updated data automatically
6	Drop view if needed	DROP VIEW active_customers	View removed from database

💡 View creation ends after storing the SELECT query as a virtual table definition.

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
view_name	undefined	active_customers	active_customers	active_customers
view_definition	undefined	SELECT id, name FROM customers WHERE active = TRUE	Stored in database	Stored in database
underlying_table_data	customers data	unchanged	unchanged	changes reflected in view

Key Moments - 3 Insights

Why doesn't CREATE VIEW run the SELECT query immediately?

Because the view stores the SELECT query as a definition, not the data itself. The query runs only when you select from the view later (see execution_table step 4).

What happens if the data in the customers table changes after creating the view?

The view shows the updated data automatically because it runs the stored SELECT query each time you query the view (see variable_tracker underlying_table_data).

Can you insert data directly into a view?

Usually no, because a view is a virtual table based on a query. You insert data into the underlying tables, not the view itself.

Visual Quiz - 3 Questions

Look at the execution_table, what happens at step 3?

A The view is dropped

B The SELECT query runs and returns data

C The view definition is stored in the database

D The database checks for syntax errors

💡 Hint

Check the 'Result' column in execution_table row 3

According to variable_tracker, what is the value of view_definition after step 3?

A undefined

B Stored in database

C active_customers

D SELECT id, name FROM customers WHERE active = TRUE

💡 Hint

Look at the 'view_definition' row and 'After Step 3' column in variable_tracker

At which step does querying the view return actual data?

A Step 4

B Step 3

C Step 2

D Step 6

💡 Hint

See execution_table step 4 where the view is queried

Concept Snapshot

CREATE VIEW view_name AS
SELECT columns FROM table WHERE condition;

- Defines a virtual table saved in the database
- Does not store data, runs query on demand
- Query view like a normal table
- Changes in underlying tables reflect in view
- Drop view with DROP VIEW view_name;

Full Transcript

The CREATE VIEW syntax lets you save a SELECT query as a virtual table called a view. First, you write a SELECT query that defines what data you want. Then you use CREATE VIEW view_name AS followed by that query. The database stores this query as the view's definition but does not run it immediately. When you query the view later, the database runs the stored SELECT query and returns the current data. This means any changes in the underlying tables show up in the view automatically. You cannot insert data directly into a view; you insert into the original tables. To remove a view, use DROP VIEW view_name.