Updatable views in PostgreSQL - Mini Project: Build & Apply

Creating and Using Updatable Views in PostgreSQL

📖 Scenario: You work at a bookstore that keeps track of books and their prices in a database. The manager wants a simple way to see and update the prices without changing other book details.

🎯 Goal: Build an updatable view in PostgreSQL that shows book titles and prices. Then, update the price through the view.

📋 What You'll Learn

Create a table called books with columns id, title, and price

Insert three specific books into the books table

Create a view called book_prices that shows only title and price

Update the price of a book using the book_prices view

💡 Why This Matters

🌍 Real World

Updatable views let users safely see and change parts of data without exposing the full table, useful in many business applications.

💼 Career

Database developers and administrators often create updatable views to simplify user access and maintain data integrity.

1

Create the books table and insert data

Create a table called books with columns id (integer primary key), title (text), and price (numeric). Then insert these three books exactly: (1, 'The Great Gatsby', 10.99), (2, '1984', 8.99), and (3, 'To Kill a Mockingbird', 12.50).

PostgreSQL

-- Create the books table and insert the three books
-- Your code here

Need a hint?

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

2

Create the book_prices view

Create a view called book_prices that selects only the title and price columns from the books table.

PostgreSQL

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

INSERT INTO books (id, title, price) VALUES
(1, 'The Great Gatsby', 10.99),
(2, '1984', 8.99),
(3, 'To Kill a Mockingbird', 12.50);

-- Create the book_prices view selecting title and price
-- Your code here

Need a hint?

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

3

Update a book price using the view

Write an UPDATE statement to change the price of '1984' to 9.99 using the book_prices view.

PostgreSQL

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

INSERT INTO books (id, title, price) VALUES
(1, 'The Great Gatsby', 10.99),
(2, '1984', 8.99),
(3, 'To Kill a Mockingbird', 12.50);

CREATE VIEW book_prices AS
SELECT title, price FROM books;

-- Update the price of '1984' to 9.99 using the book_prices view
-- Your code here

Need a hint?

Use UPDATE view_name SET column = value WHERE condition to update through the view.

4

Verify the update by selecting from the base table

Write a SELECT statement to get all columns from the books table to confirm the price of '1984' has changed.

PostgreSQL

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

INSERT INTO books (id, title, price) VALUES
(1, 'The Great Gatsby', 10.99),
(2, '1984', 8.99),
(3, 'To Kill a Mockingbird', 12.50);

CREATE VIEW book_prices AS
SELECT title, price FROM books;

UPDATE book_prices SET price = 9.99 WHERE title = '1984';

-- Select all columns from books to verify the update
-- Your code here

Need a hint?

Use SELECT * FROM books to see all columns and confirm the price change.