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

Need a 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

Need a 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

Need a 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

Need a hint?

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