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.

Practice

1. What is the main purpose of using CREATE VIEW in PostgreSQL?

easy

A. To delete rows from a table

B. To save a SELECT query as a virtual table for easy reuse

C. To permanently store data in a new table

D. To create an index on a table column

Solution

1. Step 1: Understand what a view is

   A view is a virtual table that stores a SELECT query, not actual data.

2. Step 2: Identify the purpose of CREATE VIEW

   CREATE VIEW saves the SELECT query so you can reuse it easily without rewriting it.

3. Final Answer:

   To save a SELECT query as a virtual table for easy reuse -> Option B

4. Quick Check:

   CREATE VIEW = virtual table for queries [OK]

Hint: Views save SELECT queries as virtual tables [OK]

Common Mistakes:

• Thinking views store data permanently
• Confusing views with tables
• Using CREATE VIEW to delete data

2. Which of the following is the correct syntax to create a view named active_users showing users with status = 'active'?

easy

A. CREATE VIEW active_users AS SELECT * FROM users WHERE status = 'active';

B. CREATE active_users VIEW AS SELECT * FROM users WHERE status = 'active';

C. VIEW CREATE active_users AS SELECT * FROM users WHERE status = 'active';

D. CREATE VIEW active_users SELECT * FROM users WHERE status = 'active';

Solution

1. Step 1: Recall the correct CREATE VIEW syntax

   The syntax is: CREATE VIEW view_name AS SELECT ...

2. Step 2: Match the syntax with options

   CREATE VIEW active_users AS SELECT * FROM users WHERE status = 'active'; matches the correct syntax exactly.

3. Final Answer:

   CREATE VIEW active_users AS SELECT * FROM users WHERE status = 'active'; -> Option A

4. Quick Check:

   CREATE VIEW view_name AS SELECT ... [OK]

Hint: Syntax is CREATE VIEW name AS SELECT ... [OK]

Common Mistakes:

• Swapping CREATE and VIEW keywords
• Omitting AS keyword
• Missing semicolon at the end

3. Given the table orders with columns order_id, customer_id, and amount, what will be the result of this view?

CREATE VIEW big_orders AS SELECT order_id, amount FROM orders WHERE amount > 100;

What rows will SELECT * FROM big_orders; return?

medium

A. No rows, syntax error in view

B. All orders with amount less than or equal to 100

C. All orders regardless of amount

D. All orders with amount greater than 100

Solution

1. Step 1: Analyze the SELECT query inside the view

   The view selects order_id and amount from orders where amount is greater than 100.

2. Step 2: Understand what SELECT * FROM big_orders returns

   It returns all rows from the view, which are orders with amount > 100.

3. Final Answer:

   All orders with amount greater than 100 -> Option D

4. Quick Check:

   View filters orders with amount > 100 [OK]

Hint: View returns rows matching its SELECT query condition [OK]

Common Mistakes:

• Confusing greater than with less than
• Expecting all columns instead of selected ones
• Assuming syntax error without checking carefully

4. Identify the error in this CREATE VIEW statement:

CREATE VIEW recent_customers AS SELECT id, name FROM customers WHERE;

medium

A. Missing condition after WHERE keyword

B. Missing AS keyword before SELECT

C. View name cannot be recent_customers

D. SELECT must include all columns

Solution

1. Step 1: Check the syntax after WHERE

   The WHERE clause is incomplete; it must have a condition after WHERE.

2. Step 2: Verify other parts of the statement

   AS keyword is present, view name is valid, and SELECT columns are allowed to be partial.

3. Final Answer:

   Missing condition after WHERE keyword -> Option A

4. Quick Check:

   WHERE must have a condition [OK]

Hint: WHERE clause must have a condition [OK]

Common Mistakes:

• Leaving WHERE empty
• Forgetting AS keyword
• Thinking view names have restrictions

5. You want to create a view customer_orders that shows each customer's name and the total amount they spent. Which of these is the correct way to create this view assuming tables customers(id, name) and orders(customer_id, amount)?

hard

A. CREATE VIEW customer_orders AS SELECT c.name, o.amount FROM customers c, orders o WHERE c.id = o.customer_id;

B. CREATE VIEW customer_orders AS SELECT name, amount FROM customers JOIN orders ON id = customer_id;

C. CREATE VIEW customer_orders AS SELECT c.name, SUM(o.amount) FROM customers c JOIN orders o ON c.id = o.customer_id GROUP BY c.name;

D. CREATE VIEW customer_orders AS SELECT name, SUM(amount) FROM orders GROUP BY name;

Solution

1. Step 1: Understand the requirement

   We need each customer's name and total amount spent, so we must join customers and orders and sum amounts grouped by customer name.

2. Step 2: Check each option

   CREATE VIEW customer_orders AS SELECT c.name, SUM(o.amount) FROM customers c JOIN orders o ON c.id = o.customer_id GROUP BY c.name; correctly joins tables, sums amounts, and groups by customer name. Others miss grouping or join conditions.

3. Final Answer:

   CREATE VIEW customer_orders AS SELECT c.name, SUM(o.amount) FROM customers c JOIN orders o ON c.id = o.customer_id GROUP BY c.name; -> Option C

4. Quick Check:

   Join + SUM + GROUP BY = correct view [OK]

Hint: Use JOIN with GROUP BY and SUM for aggregation views [OK]

Common Mistakes:

• Missing GROUP BY when using SUM
• Not joining tables properly
• Selecting columns not in GROUP BY