Updatable views and limitations in SQL - Mini Project: Build & Apply

Creating and Using Updatable Views in SQL

📖 Scenario: You work in a small bookstore's database team. The store wants to simplify how employees see book information by creating a view that shows only the book title and price. They also want to update prices through this view.

🎯 Goal: Build an updatable view that shows book titles and prices, then update a price through the view.

📋 What You'll Learn

Create a table named books with columns id, title, and price.

Insert three specific books into the books table.

Create a view named book_prices that shows only title and price from books.

Update the price of a book through the book_prices view.

💡 Why This Matters

🌍 Real World

Updatable views simplify data access for users by showing only relevant columns and allowing updates without exposing the full table.

💼 Career

Database developers and administrators often create views to control data visibility and maintain data integrity while allowing controlled updates.

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 rows exactly: (1, 'The Great Gatsby', 10.99), (2, '1984', 8.99), and (3, 'To Kill a Mockingbird', 12.50).

SQL

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

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add 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.

SQL

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 ... syntax.

3

Update a book price through the view

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

SQL

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.

4

Explain a limitation of updatable views

Add a comment explaining one limitation of updatable views in SQL, such as why views with joins or aggregates are not updatable.

SQL

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';

-- Add a comment explaining a limitation of updatable views
-- Your code here

Need a hint?

Write a SQL comment starting with -- explaining a common limitation.