UPDATE single column in SQL - Mini Project: Build & Apply

Update a Single Column in a Database Table

📖 Scenario: You manage a small bookstore database. One of the books has a wrong price, and you need to fix it.

🎯 Goal: Update the price of a specific book in the books table using an UPDATE statement that changes only the price column.

📋 What You'll Learn

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

Insert three books with given ids, titles, and prices.

Set a variable for the new price value.

Write an UPDATE query to change the price of the book with id = 2 to the new price.

Ensure only the price column is updated.

💡 Why This Matters

🌍 Real World

Updating prices or details of products in a store database is a common task in retail and e-commerce.

💼 Career

Database administrators and developers often need to update records safely and correctly using SQL UPDATE statements.

1

Create the books table and insert data

Write SQL statements to 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 three rows
-- Your code here

Need a hint?

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

2

Set the new price value

Create a variable called new_price and set it to 9.99. Use a SQL variable declaration or a comment to indicate this value for the 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 new_price to 9.99
-- Your code here

Need a hint?

If your SQL environment does not support variables, use a comment to indicate the new price value.

3

Write the UPDATE statement to change the price

Write an UPDATE statement that sets the price column to 9.99 for the book where id = 2. Use the exact column and table names.

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

-- Declare new_price variable
-- For example, in PostgreSQL: \set new_price 9.99

-- Write UPDATE statement to set price to 9.99 where id = 2
-- Your code here

Need a hint?

Use UPDATE with SET and WHERE to change only the price of the book with id 2.

4

Complete the update with a commit statement

Add a COMMIT; statement after the UPDATE to save the changes permanently.

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

-- Declare new_price variable
-- For example, in PostgreSQL: \set new_price 9.99

UPDATE books
SET price = 9.99
WHERE id = 2;

-- Add COMMIT statement below
-- Your code here

Need a hint?

Use COMMIT; to save your update permanently in the database.