SQL : Filtering Data with WHERE

Objectives:

• Understand the WHERE clause syntax and its usage
• Use comparison operators to filter data
• Apply logical operators to refine query results

1. Introduction to WHERE Clause (5 minutes)

Syntax Overview:

• The WHERE clause is used to filter records that meet specified conditions.

  SELECT column1, column2, ...
  FROM table_name
  WHERE condition;

Example:

SELECT * FROM TestTable WHERE ID = 1;

2. Comparison Operators (10 minutes)

Common Comparison Operators:

• = : Equal to
• <> or != : Not equal to
• > : Greater than
• < : Less than
• >= : Greater than or equal to
• <= : Less than or equal to

Examples:

• Select rows where ID is 1:

  SELECT * FROM TestTable WHERE ID = 1;

• Select rows where DateOfBirth is after ‘1990-01-01’:

  SELECT * FROM TestTable WHERE DateOfBirth > '1990-01-01';

Exercise:

• Write SQL queries to:

1. Select rows where ID is greater than 1.
2. Select rows where Name is not equal to ‘John Doe’.

3. Logical Operators (10 minutes)

Logical Operators:

• AND : Combines multiple conditions; all conditions must be true.
• OR : Combines multiple conditions; at least one condition must be true.
• NOT : Negates a condition.

Examples:

• Select rows where ID is 1 and DateOfBirth is ‘1990-01-01’:

  SELECT * FROM TestTable WHERE ID = 1 AND DateOfBirth = '1990-01-01';

• Select rows where ID is 1 or ID is 2:

  SELECT * FROM TestTable WHERE ID = 1 OR ID = 2;

• Select rows where Name is not ‘John Doe’:

  SELECT * FROM TestTable WHERE NOT Name = 'John Doe';

Exercise:

• Write SQL queries to:

1. Select rows where ID is 1 and Name is ‘John Doe’.
2. Select rows where ID is less than 3 or DateOfBirth is after ‘1990-01-01’.
3. Select rows where Name is not ‘Jane Smith’.

4. Combining Conditions (10 minutes)

Using Parentheses:

• Parentheses are used to group conditions and control the order of operations.

  SELECT * FROM TestTable WHERE (ID = 1 OR ID = 2) AND Name = 'John Doe';

Example:

SELECT * FROM TestTable WHERE (ID = 1 AND DateOfBirth = '1990-01-01') OR Name = 'John Doe';

Exercise:

• Write SQL queries to:

1. Select rows where (ID = 1 OR ID = 2) AND Name = 'John Doe'.
2. Select rows where ID is 1 and (Name = 'John Doe' OR Name = 'Jane Smith').

5. Practical Exercise: Filtering Data (5 minutes)

• Task:
• Use the Students table created earlier.
• Insert additional sample data into Students table: INSERT INTO Students (StudentID, FirstName, LastName, EnrollmentDate) VALUES (4, 'Alice', 'Brown', '2024-01-18'), (5, 'Bob', 'White', '2024-01-19');
• Exercises:

1. Select all columns from Students where EnrollmentDate is ‘2024-01-15’.
2. Select FirstName and LastName from Students where StudentID is greater than 2.
3. Select rows where LastName is ‘Smith’ and EnrollmentDate is after ‘2024-01-15’.

Exercises

1. Using WHERE Clause:

• Exercise: Write SQL queries to:
  • Select rows where ID is 1.
  • Select rows where DateOfBirth is after ‘1990-01-01’.

1. Comparison Operators:

• Exercise: Write queries to:
  • Select rows where ID is greater than 1.
  • Select rows where Name is not equal to ‘John Doe’.

1. Logical Operators:

• Exercise: Write queries to:
  • Select rows where ID is 1 and DateOfBirth is ‘1990-01-01’.
  • Select rows where ID is 1 or ID is 2.
  • Select rows where Name is not ‘John Doe’.

1. Combining Conditions with Parentheses:

• Exercise: Write queries to:
  • Select rows where (ID = 1 OR ID = 2) AND Name = 'John Doe'.
  • Select rows where ID is 1 and (Name = 'John Doe' OR Name = 'Jane Smith').

1. Sample Queries for Students Table:

• Exercise: Execute the following queries:
  1. Select all columns from Students where EnrollmentDate is ‘2024-01-15’.
  2. Select FirstName and LastName from Students where StudentID is greater than 2.
  3. Select rows where LastName is ‘Smith’ and EnrollmentDate is after ‘2024-01-15’.

Homework:

• Practice Questions:
• Write SQL queries to:
  • Select rows where StudentID is between 1 and 3.
  • Retrieve students whose EnrollmentDate is on or after ‘2024-01-16’.