SQL : Deleting Data

Objectives:

• Learn the DELETE statement syntax
• Understand how to delete single and multiple rows
• Use the WHERE clause to specify conditions for deletion

1. Introduction to DELETE Statement (5 minutes)

Syntax Overview:

• The DELETE statement is used to remove rows from a table.

  DELETE FROM table_name
  WHERE condition;

Example:

DELETE FROM TestTable
WHERE ID = 1;

2. Deleting a Single Row (10 minutes)

Basic Syntax:

• Delete a single row based on a condition.

  DELETE FROM Students
  WHERE StudentID = 1;

Exercise:

• Write SQL queries to delete the student with StudentID 2 from the Students table.

3. Deleting Multiple Rows (10 minutes)

Syntax for Multiple Rows:

• Delete multiple rows using a condition.

  DELETE FROM Students
  WHERE EnrollmentDate < '2024-01-20';

Example:

DELETE FROM Students
WHERE LastName = 'Smith';

Exercise:

• Write SQL queries to:

1. Delete all students whose EnrollmentDate is before ‘2024-01-20’.
2. Remove students with LastName ‘Evans’.

4. Using WHERE Clause to Specify Rows (10 minutes)

Importance of WHERE Clause:

• Ensure you specify the WHERE clause to avoid deleting all rows.

  DELETE FROM Students
  WHERE LastName = 'Smith';

Example:

DELETE FROM Students
WHERE FirstName = 'John' AND LastName = 'Doe';

Exercise:

• Write SQL queries to:

1. Delete students with FirstName ‘Jane’ and LastName ‘Smith’.
2. Remove all students with EnrollmentDate before ‘2024-01-15’.

5. Practical Exercise: Deleting Data (10 minutes)

• Task:
• Use the Students table.
• Insert more sample data into Students table:

  INSERT INTO Students (StudentID, FirstName, LastName, EnrollmentDate)
  VALUES 
      (18, 'Anna', 'Brown', '2024-01-30'),
      (19, 'Mark', 'Jones', '2024-01-31');

• Exercises:

1. Delete the student with StudentID 16.
2. Remove students whose LastName is ‘Hall’.
3. Delete all students with EnrollmentDate before ‘2024-01-29’.

Exercises

1. Deleting a Single Row:

• Exercise: Write SQL queries to delete the row with StudentID 3 from the Students table.

1. Deleting Multiple Rows:

• Exercise: Write SQL queries to:
  • Delete students with EnrollmentDate before ‘2024-01-25’.
  • Remove all students with LastName ‘Green’.

1. Using WHERE Clause:

• Exercise: Write SQL queries to:
  • Delete students with FirstName ‘Alice’ and LastName ‘Brown’.
  • Remove students with EnrollmentDate after ‘2024-01-30’.

1. Sample Deletion Tasks:

• Exercise: Execute the following tasks:
  1. Delete the student with StudentID 17.
  2. Remove all students with LastName ‘Jones’ and EnrollmentDate before ‘2024-01-30’.

Homework:

• Practice Questions:
• Write SQL queries to:
  • Delete students with FirstName ‘Hannah’ and LastName ‘Moore’.
  • Remove all students with EnrollmentDate before ‘2024-02-01’.