Question

Which SQL Query below is correct for deleting all records from 'Products' table having product names starting with 'M'?

• A. DELETE FROM Products WHERE ProductName LIKE 'M%';
• B. REMOVE FROM Products WHERE ProductName LIKE 'M%';
• C. DROP FROM Products WHERE ProductName IS 'M%';
• D. REMOVE FROM Products WHERE ProductName IS 'M%';

Hint:

Remember:
- Use DELETE to remove records (rows) from a table.
- Use DROP to remove the entire table structure from the database.
- Use LIKE for wildcard matches, never use the IS operator with wildcards!

Answer 1

The Correct Option is A

Step 1: Understanding the Question:
The question asks for the correct SQL syntax to delete a subset of records from a table named Products based on a pattern-matching condition where the product names begin with the letter 'M'.

Step 2: Key SQL Commands and Operators:
- DELETE FROM: The DML (Data Manipulation Language) command used to remove specific rows from an existing table. Syntax: DELETE FROM table_name WHERE condition;.
- DROP: A DDL (Data Definition Language) command used to remove entire table structures or databases. It does not delete rows conditionally and cannot be used with a WHERE clause.
- LIKE: The comparison operator used in SQL pattern-matching.
- % Wildcard: Represents zero or more characters in standard SQL. The pattern 'M%' matches any string that starts with 'M'.
- REMOVE: This is not a valid SQL command for deleting table records.

Step 3: Detailed Explanation:
- To delete rows from a table, SQL utilizes the DELETE FROM command. Options (B) and (D) are immediately incorrect because they use the invalid keyword REMOVE.
- Option (C) is incorrect because DROP is used to delete the entire table structure, and the IS operator is used for NULL comparisons (IS NULL), not for wildcard text pattern matching.
- Option (A) is the correct formulation:
- DELETE FROM Products specifies that records should be removed from the Products table.
- WHERE ProductName LIKE 'M%' filters the deletion to target only rows where the value in ProductName starts with 'M'.

Step 4: Final Answer:
The correct SQL query to perform this deletion is: DELETE FROM Products WHERE ProductName LIKE 'M%';.
Hence, option (A) is the correct choice.