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

Understanding DELETE without WHERE in SQL

📖 Scenario: You are managing a small library database. It has a table called books that stores information about books available in the library.Sometimes, you need to remove books from the database. But if you use the DELETE command without a WHERE clause, it will remove all books, which can be dangerous.

🎯 Goal: Learn how to safely delete records from the books table by understanding what happens when you use DELETE without a WHERE clause.

📋 What You'll Learn

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

Insert three specific book records into the books table

Write a DELETE statement without a WHERE clause to remove all records

Add a SELECT statement to verify the table is empty after deletion

💡 Why This Matters

🌍 Real World

Database administrators and developers often need to remove data safely. Understanding DELETE without WHERE helps prevent accidental data loss.

💼 Career

Knowing how to write safe DELETE queries is essential for roles like database administrator, backend developer, and data analyst.

1

Create the books table

Write a SQL statement to create a table called books with columns id as INTEGER, title as TEXT, and author as TEXT.

SQL

-- Create the books table with id, title, and author columns
-- Your code here

Need a hint?

Use CREATE TABLE books (id INTEGER, title TEXT, author TEXT); to create the table.

2

Insert book records

Insert these three books into the books table: (1, 'The Hobbit', 'J.R.R. Tolkien'), (2, '1984', 'George Orwell'), and (3, 'Pride and Prejudice', 'Jane Austen'). Write three separate INSERT statements.

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  author TEXT
);
-- Insert the three book records below
-- Your code here

Need a hint?

Use three separate INSERT INTO books (id, title, author) VALUES (...); statements for each book.

3

Delete all records without WHERE

Write a DELETE statement to remove all records from the books table without using a WHERE clause.

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  author TEXT
);
INSERT INTO books (id, title, author) VALUES (1, 'The Hobbit', 'J.R.R. Tolkien');
INSERT INTO books (id, title, author) VALUES (2, '1984', 'George Orwell');
INSERT INTO books (id, title, author) VALUES (3, 'Pride and Prejudice', 'Jane Austen');
-- Delete all records from books without WHERE
-- Your code here

Need a hint?

Use DELETE FROM books; to remove all rows.

4

Verify table is empty

Write a SELECT statement to get all records from the books table to confirm it is empty after deletion.

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  author TEXT
);
INSERT INTO books (id, title, author) VALUES (1, 'The Hobbit', 'J.R.R. Tolkien');
INSERT INTO books (id, title, author) VALUES (2, '1984', 'George Orwell');
INSERT INTO books (id, title, author) VALUES (3, 'Pride and Prejudice', 'Jane Austen');
DELETE FROM books;
-- Select all records from books to check if empty
-- Your code here

Need a hint?

Use SELECT * FROM books; to see all rows in the table.