Bird
Raised Fist0
SQLquery~30 mins

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

Choose your learning style10 modes available
LearnWhyDeepVisualTryChallengeProjectRecallTime
Delete Records with WHERE Condition in SQL
📖 Scenario: You manage a small library database that keeps track of books and their availability. Sometimes, books are removed from the collection when they are outdated or damaged.
🎯 Goal: Learn how to delete specific records from a database table using the DELETE statement with a WHERE condition to remove only certain rows.
📋 What You'll Learn
Create a table called books with columns id, title, and status
Insert specific book records into the books table
Write a DELETE query that removes only the books with status set to 'unavailable'
Verify that only the correct records are deleted
💡 Why This Matters
🌍 Real World
Deleting specific records is common when cleaning up outdated or incorrect data in databases, such as removing unavailable books from a library system.
💼 Career
Database administrators and developers often need to write DELETE queries with conditions to maintain accurate and relevant data in production databases.
Progress0 / 4 steps
1
Create the books table and insert records
Write SQL code to create a table called books with columns id (integer), title (text), and status (text). Then insert these exact records: (1, 'Python Basics', 'available'), (2, 'SQL Fundamentals', 'unavailable'), (3, 'Data Science 101', 'available'), (4, 'Old Database Guide', 'unavailable').
SQL
Hint

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

2
Set up a condition to identify unavailable books
Create a variable or comment that specifies the condition to find books where the status is 'unavailable'. Use the exact text status = 'unavailable' in your code as a comment or part of the query.
SQL
Hint

Use a comment or a variable to hold the condition text exactly as status = 'unavailable'.

3
Write the DELETE query with WHERE condition
Write a SQL DELETE statement that removes rows from the books table where the status is 'unavailable'. Use the exact syntax DELETE FROM books WHERE status = 'unavailable';.
SQL
Hint

Use DELETE FROM books WHERE status = 'unavailable'; to remove only the unavailable books.

4
Verify the remaining records
Add a SQL SELECT statement to show all remaining rows in the books table after deletion. Use the exact query SELECT * FROM books;.
SQL
Hint

Use SELECT * FROM books; to see all books left after deletion.

Practice

(1/5)
1. What does the DELETE statement with a WHERE condition do in SQL?
easy
A. Removes only the rows that match the condition specified in WHERE.
B. Removes all rows from the table regardless of the condition.
C. Updates the rows that match the condition.
D. Creates a new table with rows matching the condition.

Solution

  1. Step 1: Understand DELETE statement

    The DELETE statement removes rows from a table.

  2. Step 2: Effect of WHERE condition

    When a WHERE condition is present, only rows matching that condition are deleted.

  3. Final Answer:

    Removes only the rows that match the condition specified in WHERE. -> Option A

  4. Quick Check:

    DELETE with WHERE removes matching rows only [OK]
Hint: DELETE with WHERE deletes matching rows only, not all [OK]
Common Mistakes:
  • Thinking DELETE always removes all rows
  • Confusing DELETE with UPDATE
  • Assuming WHERE creates a new table
2. Which of the following is the correct syntax to delete rows where the column age is greater than 30 from a table named employees?
easy
A. REMOVE FROM employees WHERE age > 30;
B. DELETE employees WHERE age > 30;
C. DELETE FROM employees WHERE age > 30;
D. DELETE * FROM employees WHERE age > 30;

Solution

  1. Step 1: Identify correct DELETE syntax

    The correct syntax starts with DELETE FROM followed by the table name.

  2. Step 2: Check WHERE clause usage

    The WHERE clause filters rows to delete; it must follow the table name.

  3. Final Answer:

    DELETE FROM employees WHERE age > 30; -> Option C

  4. Quick Check:

    DELETE FROM + table + WHERE condition [OK]
Hint: Use DELETE FROM table WHERE condition; to delete specific rows [OK]
Common Mistakes:
  • Omitting FROM keyword
  • Using REMOVE instead of DELETE
  • Adding * after DELETE which is invalid
3. Given the table products with rows:
{id:1, name:'Pen', stock:10}, {id:2, name:'Pencil', stock:0}, {id:3, name:'Eraser', stock:5}
What will be the result of this query?
DELETE FROM products WHERE stock = 0;
medium
A. Deletes all products because stock = 0 is ignored.
B. Deletes the product with id 2 only.
C. Deletes no rows because stock = 0 is false for all.
D. Causes a syntax error.

Solution

  1. Step 1: Identify rows matching condition

    Only the product with stock = 0 is the one with id 2 (Pencil).

  2. Step 2: Effect of DELETE with WHERE

    DELETE removes only rows matching the condition, so only id 2 is deleted.

  3. Final Answer:

    Deletes the product with id 2 only. -> Option B

  4. Quick Check:

    DELETE WHERE stock=0 removes matching rows only [OK]
Hint: DELETE removes only rows matching WHERE condition [OK]
Common Mistakes:
  • Assuming all rows are deleted
  • Thinking condition is ignored
  • Expecting syntax error for valid query
4. What is wrong with this SQL statement?
DELETE FROM orders WHERE;
medium
A. It updates rows instead of deleting.
B. It deletes all rows because WHERE is empty.
C. It creates a new table instead of deleting.
D. Missing condition after WHERE causes syntax error.

Solution

  1. Step 1: Check syntax after WHERE

    WHERE clause must have a condition; empty WHERE is invalid syntax.

  2. Step 2: Result of missing condition

    SQL parser throws syntax error because condition is missing.

  3. Final Answer:

    Missing condition after WHERE causes syntax error. -> Option D

  4. Quick Check:

    WHERE must have a condition, else syntax error [OK]
Hint: WHERE must always have a condition, never empty [OK]
Common Mistakes:
  • Leaving WHERE empty thinking it deletes all
  • Confusing DELETE with UPDATE
  • Expecting query to run without error
5. You have a table users with columns id, name, and status. You want to delete all users whose status is either 'inactive' or 'banned'. Which query correctly deletes these users?
hard
A. DELETE FROM users WHERE status IN ('inactive', 'banned');
B. DELETE FROM users WHERE status = 'inactive' OR 'banned';
C. DELETE FROM users WHERE status = 'inactive' AND status = 'banned';
D. DELETE FROM users WHERE status = 'inactive', 'banned';

Solution

  1. Step 1: Understand condition for multiple values

    To delete rows where status is 'inactive' or 'banned', use OR or IN clause.

  2. Step 2: Compare options

    DELETE FROM users WHERE status = 'inactive' OR 'banned'; -- OR 'banned' (literal string) evaluates to true, deleting all rows.
    DELETE FROM users WHERE status IN ('inactive', 'banned'); -- correctly matches either value.
    DELETE FROM users WHERE status = 'inactive' AND status = 'banned'; -- AND condition is impossible, deletes none.
    DELETE FROM users WHERE status = 'inactive', 'banned'; -- invalid comma syntax.

  3. Final Answer:

    DELETE FROM users WHERE status IN ('inactive', 'banned'); -> Option A

  4. Quick Check:

    Use WHERE status IN (...) for multiple values [OK]
Hint: Use WHERE column IN (values) to delete multiple matches [OK]
Common Mistakes:
  • Using AND instead of OR for multiple conditions
  • Incorrect syntax with commas in WHERE
  • Not using IN for multiple values
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard