CREATE VIEW syntax in PostgreSQL - Mini Project: Build & Apply

Create a Simple View in PostgreSQL

📖 Scenario: You work in a small bookstore. You have a table that stores information about books, including their title, author, and price. You want to create a view that shows only the books that cost more than $20, so your coworkers can quickly see the expensive books without looking at the full table.

🎯 Goal: Create a view named expensive_books that shows the title, author, and price of books costing more than 20 dollars from the books table.

📋 What You'll Learn

Create a table named books with columns id (integer), title (text), author (text), and price (numeric).

Insert exactly three rows into the books table with the following data: (1, 'The Great Gatsby', 'F. Scott Fitzgerald', 10.99), (2, 'War and Peace', 'Leo Tolstoy', 25.50), (3, 'Ulysses', 'James Joyce', 30.00).

Create a numeric variable price_threshold and set it to 20.

Create a view named expensive_books that selects title, author, and price from books where price is greater than price_threshold.

💡 Why This Matters

🌍 Real World

Views help you save complex queries and share filtered or summarized data easily with coworkers or applications.

💼 Career

Database views are commonly used in jobs to simplify data access, improve security by limiting columns or rows, and organize data for reports.

1

Create the books table and insert data

Create a table called books with columns id (integer), title (text), author (text), and price (numeric). Then insert these three rows exactly: (1, 'The Great Gatsby', 'F. Scott Fitzgerald', 10.99), (2, 'War and Peace', 'Leo Tolstoy', 25.50), and (3, 'Ulysses', 'James Joyce', 30.00).

PostgreSQL

-- Create the books table
-- Insert the three rows
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Define the price threshold variable

Create a numeric variable called price_threshold and set it to 20. Use the PostgreSQL DO block with DECLARE to define this variable.

PostgreSQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  author TEXT,
  price NUMERIC
);

INSERT INTO books (id, title, author, price) VALUES
  (1, 'The Great Gatsby', 'F. Scott Fitzgerald', 10.99),
  (2, 'War and Peace', 'Leo Tolstoy', 25.50),
  (3, 'Ulysses', 'James Joyce', 30.00);

-- Declare price_threshold variable and set it to 20
-- Your code here

Hint

Use a DO block with DECLARE to create a variable in PostgreSQL.

3

Write the query for the view

Write a SELECT query that selects title, author, and price from the books table where price is greater than 20.

PostgreSQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  author TEXT,
  price NUMERIC
);

INSERT INTO books (id, title, author, price) VALUES
  (1, 'The Great Gatsby', 'F. Scott Fitzgerald', 10.99),
  (2, 'War and Peace', 'Leo Tolstoy', 25.50),
  (3, 'Ulysses', 'James Joyce', 30.00);

DO $$
DECLARE
  price_threshold NUMERIC := 20;
BEGIN
  NULL; -- No operation, just declare variable
END $$;

-- Write SELECT query to get title, author, price where price > 20
-- Your code here

Hint

Use SELECT with a WHERE clause to filter by price.

4

Create the view expensive_books

Create a view named expensive_books using the CREATE VIEW statement. The view should select title, author, and price from the books table where price is greater than 20.

PostgreSQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  author TEXT,
  price NUMERIC
);

INSERT INTO books (id, title, author, price) VALUES
  (1, 'The Great Gatsby', 'F. Scott Fitzgerald', 10.99),
  (2, 'War and Peace', 'Leo Tolstoy', 25.50),
  (3, 'Ulysses', 'James Joyce', 30.00);

DO $$
DECLARE
  price_threshold NUMERIC := 20;
BEGIN
  NULL; -- No operation, just declare variable
END $$;

-- Create the view expensive_books
-- Your code here

Hint

Use CREATE VIEW view_name AS SELECT ... to create the view.

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