UPDATE without WHERE (danger) in SQL - Mini Project: Build & Apply

Understanding the Danger of UPDATE without WHERE in SQL

📖 Scenario: You are managing a small bookstore database. You want to update the price of a specific book, but accidentally run an UPDATE query without a WHERE clause.This project will help you understand how UPDATE without WHERE affects all rows in a table.

🎯 Goal: Build a simple books table, then write an UPDATE query that changes the price of all books (without a WHERE clause), and finally fix it by adding a WHERE clause to update only one book.

📋 What You'll Learn

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

Insert three books with exact titles and prices

Write an UPDATE query that changes the price of all books to 20 (without WHERE)

Write an UPDATE query that changes the price of the book with id = 2 to 15 (with WHERE)

💡 Why This Matters

🌍 Real World

Database administrators and developers often update records. Understanding the impact of UPDATE without WHERE helps prevent accidental data loss or corruption.

💼 Career

This knowledge is crucial for roles like database administrators, backend developers, and data analysts who manage and modify data safely.

1

Create the books table and insert data

Create a table called books with columns id (integer), title (text), and price (integer). Then insert these three rows exactly: (1, 'Learn SQL', 10), (2, 'Mastering Databases', 12), and (3, 'Data Science Basics', 15).

SQL

-- Create table books and insert 3 rows
-- Your code here

Need a hint?

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

2

Write an UPDATE query without WHERE

Write an UPDATE query that sets the price of all books in the books table to 20. Do NOT use a WHERE clause.

SQL

CREATE TABLE books (id INTEGER, title TEXT, price INTEGER);
INSERT INTO books (id, title, price) VALUES
(1, 'Learn SQL', 10),
(2, 'Mastering Databases', 12),
(3, 'Data Science Basics', 15);
-- Write UPDATE query to set price to 20 for all books
-- Your code here

Need a hint?

Use UPDATE books SET price = 20; without a WHERE clause to update all rows.

3

Add a WHERE clause to update only one book

Write an UPDATE query that sets the price of the book with id = 2 in the books table to 15. Use a WHERE clause to update only that book.

SQL

CREATE TABLE books (id INTEGER, title TEXT, price INTEGER);
INSERT INTO books (id, title, price) VALUES
(1, 'Learn SQL', 10),
(2, 'Mastering Databases', 12),
(3, 'Data Science Basics', 15);
UPDATE books SET price = 20;
-- Write UPDATE query to set price to 15 only for book with id = 2
-- Your code here

Need a hint?

Use WHERE id = 2 to update only the book with that id.

4

Add a comment explaining the danger of UPDATE without WHERE

Add a SQL comment explaining why running UPDATE without a WHERE clause can be dangerous.

SQL

CREATE TABLE books (id INTEGER, title TEXT, price INTEGER);
INSERT INTO books (id, title, price) VALUES
(1, 'Learn SQL', 10),
(2, 'Mastering Databases', 12),
(3, 'Data Science Basics', 15);
UPDATE books SET price = 20;
UPDATE books SET price = 15 WHERE id = 2;
-- Add a comment about the danger of UPDATE without WHERE
-- Your code here

Need a hint?

Use a SQL comment starting with -- to explain the risk.