Updatable views in MySQL - Mini Project: Build & Apply

Creating and Using Updatable Views in MySQL

📖 Scenario: You are managing a small bookstore database. You want to create a simplified view of the books table that only shows the book title and price. You also want to be able to update the price through this view.

🎯 Goal: Create an updatable view in MySQL that shows book titles and prices. Then update the price of a book using this 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 selects title and price from books.

Update the price of a book through the book_prices view.

💡 Why This Matters

🌍 Real World

Updatable views let you simplify complex tables for users and still allow them to change data safely.

💼 Career

Database developers and administrators often create views to control data access and simplify user interactions.

1

Create the books table and insert data

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

MySQL

-- 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 named book_prices that selects only the title and price columns from the books table.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  price DECIMAL(5,2)
);

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

-- Your code here

Need a hint?

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

3

Update a book price using the book_prices view

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

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  price DECIMAL(5,2)
);

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;

-- 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 books table

Write a SELECT statement to show all columns from the books table to confirm the price update.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  price DECIMAL(5,2)
);

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

-- Your code here

Need a hint?

Use SELECT * FROM books to see all rows and columns.