DAY-3

 Day 3 session plan on advanced filtering in SQL.

This session covers the following operators: AND, OR, NOT, IN, BETWEEN, LIKE, IS NULL. The plan includes explanations, sample datasets, and plenty of examples so that learners can see these operators in action.

Session Overview

Objective:
Learners will understand how to use advanced filtering conditions in SQL queries to refine result sets. They will learn how to combine multiple filters using logical operators and how to deal with ranges, patterns, and missing values.

Agenda:

1. Introduction to Advanced Filtering
• What is filtering and why it’s important
• Overview of logical operators vs. comparison operators
2. The AND Operator
• Definition and use cases
• Examples: Combining conditions that all must be true
3. The OR Operator
• Definition and use cases
• Examples: At least one condition must be true
4. The NOT Operator
• Definition and use cases
• Examples: Negating a condition
5. The IN Operator
• Definition and use cases
• Examples: Filtering by a set of values
6. The BETWEEN Operator
• Definition and use cases
• Examples: Filtering for a range of values
7. The LIKE Operator
• Definition and use cases
• Examples: Pattern matching with wildcards
8. The IS NULL Operator
• Definition and use cases
• Examples: Finding missing or undefined values
9. Practice Exercises
• Hands-on queries using a sample table (e.g., Employees)
10. Q&A and Wrap-Up

Detailed Content and Examples

1. Introduction to Advanced Filtering

Explain that filtering is essential for querying databases efficiently. Advanced filtering allows users to combine conditions to retrieve only the relevant data. Emphasize the difference between:

• Comparison operators (e.g., =, <, >) which compare values
• Logical operators (e.g., AND, OR, NOT) which combine conditions

2. The AND Operator

Purpose:
The AND operator requires that all specified conditions are true for a row to be included in the result set.

Syntax Example:

SELECT * FROM Employees WHERE Department = 'Sales' AND Salary > 50000;

Explanation:
This query retrieves all employees in the Sales department who have a salary greater than 50,000.

Additional Example:

SELECT EmployeeID, FirstName, LastName, HireDate FROM Employees

WHERE HireDate >= '2020-01-01'

  AND HireDate <= '2020-12-31';

Explanation:
This finds employees hired in the year 2020.

3. The OR Operator

Purpose:
The OR operator returns rows where at least one of the conditions is true.

Syntax Example:

SELECT * FROM Employees WHERE Department = 'IT' OR Department = 'Marketing';

Explanation:
This query selects employees who work in either the IT or Marketing department.

Additional Example:

SELECT EmployeeID, FirstName, LastName FROM Employees WHERE Salary < 40000

   OR JobTitle = 'Intern';

Explanation:
It retrieves employees with a salary less than 40,000 or who are interns.

4. The NOT Operator

Purpose:
NOT negates a condition. It filters out rows where the condition is true.

Syntax Example:

SELECT * FROM Employees WHERE NOT Department = 'HR';

Explanation:
This query retrieves all employees except those in the HR department.

Additional Example:

SELECT EmployeeID, FirstName, LastName, JobTitle FROM Employees

WHERE NOT (JobTitle = 'Manager');

Explanation:
This returns all employees who are not managers.

5. The IN Operator

Purpose:
IN allows you to specify multiple values in a WHERE clause.

Syntax Example:

SELECT * FROM Employees WHERE Department IN ('Finance', 'IT', 'HR');

Explanation:
This query selects employees who work in either Finance, IT, or HR.

Additional Example:

SELECT EmployeeID, FirstName, LastName, JobTitle FROM Employees

WHERE JobTitle IN ('Developer', 'Analyst', 'Tester');

Explanation:
This returns employees who have one of the listed job titles.

6. The BETWEEN Operator

Purpose:
BETWEEN is used to filter values within a certain range (inclusive).

Syntax Example:

SELECT * FROM Employees WHERE Salary BETWEEN 30000 AND 70000;

Explanation:
This query finds employees with a salary between 30,000 and 70,000.

Additional Example:

SELECT EmployeeID, FirstName, LastName, HireDate FROM Employees

WHERE HireDate BETWEEN '2019-01-01' AND '2021-12-31';

Explanation:
This retrieves employees hired between January 1, 2019, and December 31, 2021.

7. The LIKE Operator

Purpose:
LIKE is used for pattern matching with wildcards. Use % for multiple characters and _ for a single character.

Syntax Example:

SELECT * FROM Employees WHERE LastName LIKE 'S%';

Explanation:
This query selects employees whose last name starts with S.

Additional Examples:

• Pattern anywhere in the string:

SELECT * FROM Employees WHERE Email LIKE '%@company.com';

Explanation:
Retrieves employees with an email ending in @company.com.

• Single character wildcard:

SELECT * FROM Employees WHERE FirstName LIKE '_a%';

Explanation:
Finds employees whose first name has any one character followed by an a at the beginning (e.g., Sara, Alan).

8. The IS NULL Operator

Purpose:
IS NULL is used to check for missing or undefined values.

Syntax Example:

SELECT * FROM Employees WHERE ManagerID IS NULL;

Explanation:
This query retrieves employees who do not have a manager (possibly top-level executives).

Additional Example:

SELECT EmployeeID, FirstName, LastName, PhoneNumber FROM Employees

WHERE PhoneNumber IS NULL;

Explanation:
This finds employees with no phone number recorded.

9. Practice Exercises

Exercise 1:
Using the Employees table, write a query to select employees who:

• Are in the IT department,
• Have a salary greater than 50,000,
• And were hired between January 1, 2018, and December 31, 2020.

Expected Query:

SELECT * FROM Employees WHERE Department = 'IT'

  AND Salary > 50000

  AND HireDate BETWEEN '2018-01-01' AND '2020-12-31';

Exercise 2:
Write a query to find all employees whose first name starts with J or whose last name ends with son.

Expected Query:

SELECT * FROM Employees WHERE FirstName LIKE 'J%'

   OR LastName LIKE '%son';

Exercise 3:
Select employees who are not in the Sales or Marketing departments and whose salary is not between 40,000 and 80,000.

Expected Query:

SELECT * FROM Employees WHERE Department NOT IN ('Sales', 'Marketing')

  AND Salary NOT BETWEEN 40000 AND 80000;

Exercise 4:
Find employees with no email address recorded.

Expected Query:

SELECT * FROM Employees WHERE Email IS NULL;

Advanced Filtering (AND, OR, NOT, IN, BETWEEN, LIKE, IS NULL) with more examples for each operator. These examples will use a fictional Employees table with columns like:

Employees(

  EmployeeID INT,

  FirstName VARCHAR(50),

  LastName VARCHAR(50),

  Department VARCHAR(50),

  JobTitle VARCHAR(50),

  Salary INT,

  HireDate DATE,

  Email VARCHAR(100),

  ManagerID INT

)

1. AND – All conditions must be true

Examples:

-- Example 1

SELECT * FROM Employees WHERE Department = 'HR' AND Salary > 40000;

 

-- Example 2

SELECT FirstName, JobTitle FROM Employees

WHERE Department = 'IT' AND JobTitle = 'Developer';

 

-- Example 3

SELECT * FROM Employees

WHERE Department = 'Finance' AND HireDate < '2020-01-01';

2. OR – At least one condition must be true

Examples:

-- Example 1

SELECT * FROM Employees WHERE Department = 'Sales' OR Department = 'Marketing';

 

-- Example 2

SELECT FirstName, Salary FROM Employees WHERE Salary < 30000 OR Salary > 80000;

 

-- Example 3

SELECT * FROM Employees WHERE JobTitle = 'Intern' OR JobTitle = 'Trainee';

3. NOT – Negates a condition

Examples:

-- Example 1

SELECT * FROM Employees WHERE NOT Department = 'IT';

 

-- Example 2

SELECT * FROM Employees WHERE NOT (Salary BETWEEN 40000 AND 70000);

 

-- Example 3

SELECT * FROM Employees WHERE NOT (JobTitle LIKE '%Manager%');

4. IN – Matches any value in a list

Examples:

-- Example 1

SELECT * FROM Employees WHERE Department IN ('Sales', 'Finance', 'HR');

 

-- Example 2

SELECT FirstName, LastName FROM Employees

WHERE JobTitle IN ('Developer', 'Designer', 'QA Engineer');

 

-- Example 3

SELECT * FROM Employees WHERE ManagerID IN (101, 102, 103);

5. BETWEEN – Matches values within a range (inclusive)

Examples:

-- Example 1

SELECT * FROM Employees WHERE Salary BETWEEN 50000 AND 100000;

 

-- Example 2

SELECT * FROM Employees WHERE HireDate BETWEEN '2021-01-01' AND '2023-12-31';

 

-- Example 3

SELECT * FROM Employees WHERE EmployeeID BETWEEN 10 AND 50;

6. LIKE – Pattern matching using wildcards % and _

Examples:

-- Example 1: Starts with S

SELECT * FROM Employees WHERE FirstName LIKE 'S%';

 

-- Example 2: Ends with "son"

SELECT * FROM Employees WHERE LastName LIKE '%son';

 

-- Example 3: Contains "art"

SELECT * FROM Employees WHERE JobTitle LIKE '%art%';

 

-- Example 4: Second letter is 'a'

SELECT * FROM Employees WHERE FirstName LIKE '_a%';

 

-- Example 5: Any name with exactly 4 letters

SELECT * FROM Employees WHERE FirstName LIKE '____';

7. IS NULL – Checks for missing values

Examples:

-- Example 1

SELECT * FROM Employees WHERE Email IS NULL;

 

-- Example 2

SELECT * FROM Employees WHERE ManagerID IS NULL;

 

-- Example 3

SELECT FirstName, PhoneNumber FROM Employees WHERE PhoneNumber IS NULL;

BONUS: Combining multiple operators

Examples:

-- Example 1: Complex condition with AND, OR

SELECT * FROM Employees WHERE (Department = 'HR' OR Department = 'Finance')

  AND Salary > 50000;

 

-- Example 2: NOT with IN

SELECT * FROM Employees WHERE Department NOT IN ('HR', 'IT');

 

-- Example 3: LIKE with AND

SELECT * FROM Employees WHERE JobTitle LIKE '%Engineer%' AND Salary > 70000;

 

-- Example 4: BETWEEN with IN

SELECT * FROM Employees WHERE Salary BETWEEN 40000 AND 80000

  AND Department IN ('IT', 'Finance');

 

-- Example 5: NULL check with OR

SELECT * FROM Employees WHERE Email IS NULL OR ManagerID IS NULL;

Wrap-Up

• Recap: Review each filtering operator and discuss when and how to use them.
• Best Practices:
• Use parentheses () to group conditions when mixing operators (e.g., combining AND and OR).
• Test queries on sample data to ensure they return the expected results.
• Remember that the order of conditions can sometimes affect readability (and performance, in complex queries).
• Discussion:
  Encourage learners to ask questions and share additional examples or scenarios from their own projects.

Previous                                                                                                                                    Next