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

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

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

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

Hint

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

Practice

1.

What does the WHERE clause do in an UPDATE statement?

easy

A. It adds new rows to the table.

B. It changes the table structure.

C. It limits which rows get updated.

D. It deletes rows from the table.

Solution

1. Step 1: Understand the purpose of UPDATE

   The UPDATE statement changes data in existing rows of a table.

2. Step 2: Role of WHERE clause in UPDATE

   The WHERE clause specifies which rows should be changed, so only those rows are updated.

3. Final Answer:

   It limits which rows get updated. -> Option C

4. Quick Check:

   WHERE clause = row filter [OK]

Hint: WHERE filters rows to update only those needed [OK]

Common Mistakes:

• Thinking WHERE adds or deletes rows
• Believing UPDATE changes table structure
• Ignoring WHERE updates all rows

2.

Which of the following is the correct syntax to update the salary to 5000 for employees with id 3?

UPDATE employees SET salary = 5000 WHERE id = 3;

easy

A. UPDATE employees SET salary 5000 WHERE id = 3;

B. UPDATE employees salary = 5000 WHERE id = 3;

C. UPDATE employees SET salary = 5000 id = 3;

D. UPDATE employees SET salary = 5000 WHERE id = 3;

Solution

1. Step 1: Check UPDATE syntax

   The correct syntax requires SET before column assignment and WHERE for condition.

2. Step 2: Validate each option

   UPDATE employees SET salary = 5000 WHERE id = 3; uses SET and WHERE correctly; others miss SET or WHERE or have wrong syntax.

3. Final Answer:

   UPDATE employees SET salary = 5000 WHERE id = 3; -> Option D

4. Quick Check:

   SET + WHERE = correct UPDATE syntax [OK]

Hint: Always use SET before column assignments in UPDATE [OK]

Common Mistakes:

• Omitting SET keyword
• Forgetting WHERE clause
• Missing equal sign (=) in assignment

3.

Given the table products:

id | name    | price
---+---------+-------
1  | Pen     | 10
2  | Pencil  | 5
3  | Eraser  | 3

What will be the result of this query?

UPDATE products SET price = price + 2 WHERE price < 10;

medium

A. All products will have their price increased by 2.

B. Only products with price less than 10 will have their price increased by 2.

C. No products will be updated because price is not changed.

D. Syntax error due to missing WHERE clause.

Solution

1. Step 1: Identify rows matching WHERE condition

   Prices less than 10 are 5 and 3 (Pencil and Eraser).

2. Step 2: Apply UPDATE only to matching rows

   Only Pencil and Eraser prices increase by 2, Pen remains 10.

3. Final Answer:

   Only products with price less than 10 will have their price increased by 2. -> Option B

4. Quick Check:

   WHERE filters rows updated [OK]

Hint: WHERE limits which rows get updated, others stay same [OK]

Common Mistakes:

• Assuming all rows update without WHERE
• Thinking price must be reset, not incremented
• Confusing syntax error with correct query

4.

Find the error in this query:

UPDATE employees SET salary = 6000 WHERE;

medium

A. Missing condition after WHERE clause.

B. Missing SET keyword.

C. Incorrect table name.

D. No error, query is correct.

Solution

1. Step 1: Analyze the WHERE clause

   The WHERE clause is incomplete; it must have a condition after it.

2. Step 2: Check other parts of query

   SET keyword and table name are correct; only WHERE is incomplete.

3. Final Answer:

   Missing condition after WHERE clause. -> Option A

4. Quick Check:

   WHERE needs a condition [OK]

Hint: WHERE must always have a condition after it [OK]

Common Mistakes:

• Leaving WHERE empty
• Confusing missing SET with WHERE error
• Assuming query runs without WHERE condition

5.

You have a table students with columns id, name, and grade. You want to increase the grade by 5 for all students who scored less than 50, but not exceed 50 after update. Which query correctly does this?

hard

A. UPDATE students SET grade = CASE WHEN grade + 5 > 50 THEN 50 ELSE grade + 5 END WHERE grade < 50;

B. UPDATE students SET grade = grade + 5 WHERE grade < 50 AND grade + 5 <= 50;

C. UPDATE students SET grade = grade + 5 WHERE grade < 50;

D. UPDATE students SET grade = 50 WHERE grade < 50;

Solution

1. Step 1: Understand the requirement

   Increase grade by 5 only for grades less than 50, but cap max grade at 50.

2. Step 2: Analyze each option

   UPDATE students SET grade = 50 WHERE grade < 50; sets all below 50 to 50 directly, no increase by 5. UPDATE students SET grade = grade + 5 WHERE grade < 50 AND grade + 5 <= 50; increases by 5 only if result ≤ 50, skipping some rows. UPDATE students SET grade = grade + 5 WHERE grade < 50; increases by 5 without capping at 50. UPDATE students SET grade = CASE WHEN grade + 5 > 50 THEN 50 ELSE grade + 5 END WHERE grade < 50; uses CASE to increase by 5 but caps at 50 correctly.

3. Final Answer:

   UPDATE students SET grade = CASE WHEN grade + 5 > 50 THEN 50 ELSE grade + 5 END WHERE grade < 50; -> Option A

4. Quick Check:

   Use CASE to cap values in UPDATE [OK]

Hint: Use CASE in UPDATE to handle conditional value changes [OK]

Common Mistakes:

• Not capping grade at 50
• Updating all rows without WHERE
• Skipping rows that would exceed 50