Using the DELETE Statement to Remove Data

The DELETE statement is used to remove records from a table. Its basic syntax is:

DELETE FROM table_name WHERE condition;

In this syntax, table_name specifies the target table. The WHERE clause is optional; if omitted, all rows in the table will be deleted. The condition defines wich specific rows to remove.

For example, to delete all records from an employees table:

DELETE FROM employees;

To delete only the record for an employee named 'David', use a condition:

DELETE FROM employees WHERE employee_name = 'David';

Using the TRUNCATE Statement for Fast Data Removal

The TRUNCATE statement offers a faster method to delete all data from a table. It operates by deallocating the data pages rather then logging individual row deletions.

Its syntax is:

TRUNCATE TABLE table_name;

For instance, to clear all data from a transactions table:

TRUNCATE TABLE transactions;

Practical Example: Removing Records from a Products Table

This example demonstrates the process of creating a table, inserting data, and then deleting specific records.

-- Create the products table
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100)
);

-- Insert sample data
INSERT INTO products VALUES (101, 'Laptop');
INSERT INTO products VALUES (102, 'Mouse');
INSERT INTO products VALUES (103, 'Keyboard');

-- View all data before deletion
SELECT * FROM products;

-- Delete the product named 'Mouse'
DELETE FROM products WHERE product_name = 'Mouse';

-- View the data after deletion
SELECT * FROM products;