UPDATE with WHERE condition in SQL - Mini Project: Build & Apply

Update Records with a WHERE Condition in SQL

📖 Scenario: You are managing a small bookstore database. You want to update the price of a specific book based on its title.

🎯 Goal: Write an SQL UPDATE statement that changes the price of a book only if the book's title matches a certain value.

📋 What You'll Learn

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

Insert three specific book records into the books table

Write an UPDATE statement to change the price of the book titled 'The Great Gatsby'

Use a WHERE clause to update only the correct book

💡 Why This Matters

🌍 Real World

Updating specific records in a database is common when prices, statuses, or other details change for certain items.

💼 Career

Database administrators and developers often write UPDATE queries with WHERE clauses to maintain accurate and current data.

1

Create the books table and insert data

Create a table called books with columns id (integer), title (text), and price (decimal). 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 the three rows
-- Your code here

Need a hint?

Use CREATE TABLE to make the table and INSERT INTO with multiple rows to add the books.

2

Set the new price value

Create a variable or placeholder called new_price and set it to 14.99. This will be the new price for the book you want to update.

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  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);

-- Set the new price value
-- Your code here

Need a hint?

In many SQL environments, variables are set differently. For this project, just note the new price value 14.99 to use in the next step.

3

Write the UPDATE statement with WHERE

Write an UPDATE statement to change the price of the book titled 'The Great Gatsby' to 14.99. Use a WHERE clause to update only the row where title = 'The Great Gatsby'.

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  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);

-- Update the price of 'The Great Gatsby' to 14.99
-- Your code here

Need a hint?

Use UPDATE books SET price = 14.99 WHERE title = 'The Great Gatsby' to update only the correct book.

4

Verify the update with a SELECT query

Write a SELECT statement to retrieve all columns from the books table where the title is 'The Great Gatsby'. This will confirm the price was updated.

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  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);

UPDATE books
SET price = 14.99
WHERE title = 'The Great Gatsby';

-- Select the updated book to verify the price
-- Your code here

Need a hint?

Use SELECT * FROM books WHERE title = 'The Great Gatsby' to see the updated row.