Filtering Data with SQL – The WHERE Clause

Lesson 4: Filtering Data with SQL – The WHERE Clause

Objective: In this lesson, we will learn how to filter data in SQL using the WHERE clause. This will help you retrieve specific records from a database based on conditions you set.

1. Introduction to the WHERE Clause

The WHERE clause in SQL is used to filter records that meet a specified condition. By using this clause, you can control which rows to retrieve from a table.

Syntax:

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

2. Common Operators in the WHERE Clause

SQL provides various operators to use in the WHERE clause. Here are some common ones:

• = Equal to
• != Not equal to
• > Greater than
• < Less than
• >= Greater than or equal to
• <= Less than or equal to
• BETWEEN To specify a range of values
• IN To specify multiple values
• LIKE To search for a pattern

3. Examples of Using the WHERE Clause

a. Using the Equal To (=) Operator

To select employees from the HR department:

SELECT * FROM Employees
WHERE Department = 'HR';

Output:

EmployeeID  Name      Department  Salary
1          John Doe  HR         50000
2          Jane Smith HR        60000

b. Using the Greater Than (>) Operator

To select employees with a salary greater than 50000:

SELECT * FROM Employees
WHERE Salary > 50000;

Output:

EmployeeID  Name        Department  Salary
2          Jane Smith  IT          60000

c. Using the BETWEEN Operator

To find employees with salaries between 40000 and 70000:

SELECT * FROM Employees
WHERE Salary BETWEEN 40000 AND 70000;

Output:

EmployeeID  Name        Department  Salary
1          John Doe    HR          50000
2          Jane Smith  IT          60000

d. Using the LIKE Operator

To find employees whose name starts with “J”:

SELECT * FROM Employees
WHERE Name LIKE 'J%';

Output:

EmployeeID  Name        Department  Salary
1          John Doe    HR          50000
2          Jane Smith  IT          60000

4. Combining Conditions Using AND/OR

You can combine multiple conditions using AND or OR to filter the data more precisely.

a. Using AND

To find employees from the HR department with a salary greater than 50000:

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

Output:

EmployeeID  Name        Department  Salary
2          Jane Smith  HR          60000

b. Using OR

To find employees who work in either the HR or IT department:

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

Output:

EmployeeID  Name        Department  Salary
1          John Doe    HR          50000
2          Jane Smith  IT          60000

5. Practice Exercise

Try these exercises to strengthen your understanding:

• Write a query to find employees with a salary greater than 40000 and working in the IT department.
• Write a query to find employees whose name starts with the letter “A”.
• Write a query to find employees with a salary between 30000 and 70000.

6. Conclusion

In this lesson, you learned how to filter data using the WHERE clause and various operators like =, >, BETWEEN, and LIKE. These skills are essential for retrieving specific data from your tables, allowing you to work more efficiently with SQL.

Keep practicing and experiment with different conditions to deepen your understanding!