Filtering Data with WHERE Clauses

In SQL, the WHERE clause helps us filter records that match certain conditions. Imagine you’re searching for something in a big box of toys, but you only want the red ones. The WHERE clause is like telling SQL to only give you the red toys. You can use different conditions (like equal to, greater than, or less than) to filter data in many ways.

Structure of a WHERE Clause:

The basic structure of the WHERE clause looks like this:

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

• SELECT column1, column2, ... — tells SQL what data you want to see.
• FROM table_name — tells SQL which table to look in.
• WHERE condition — filters the data based on a condition.

Common Comparison Operators

Here are some common comparison operators you can use in the WHERE clause:

1. = (Equal): Filters rows where the value is equal to a certain value.
2. != or <> (Not Equal): Filters rows where the value is not equal to a certain value.
3. > (Greater Than): Filters rows where the value is greater than a certain value.
4. < (Less Than): Filters rows where the value is less than a certain value.
5. >= (Greater Than or Equal To): Filters rows where the value is greater than or equal to a certain value.
6. <= (Less Than or Equal To): Filters rows where the value is less than or equal to a certain value.
7. BETWEEN: Filters rows where the value is between two values.
8. LIKE: Filters rows based on a pattern (useful for string matching).
9. IN: Filters rows where the value is in a specified list of values.

Examples of WHERE Clause Usage:

Let’s assume we have a “students” table with the following columns:

• student_id (unique identifier for each student)
• name (student’s name)
• age (student’s age)
• score (student’s exam score)

Example 1: Equality (=)

We want to find the student with the name “Alice.”

SELECT * 
FROM students 
WHERE name = 'Alice';

This will give us the row where the student’s name is “Alice.”

Example 2: Not Equal (!= or <>)

Let’s say we want to find all students who are NOT named “Alice.”

SELECT * 
FROM students 
WHERE name != 'Alice';

This will return all students except the one named “Alice.”

Example 3: Greater Than (>)

If we want to find all students who scored more than 80:

SELECT * 
FROM students 
WHERE score > 80;

This will return all students whose score is greater than 80.

Example 4: Less Than (<)

To find all students who are younger than 18 years old:

SELECT * 
FROM students 
WHERE age < 18;

This will return all students who are younger than 18.

Example 5: BETWEEN

To find students whose age is between 18 and 25:

SELECT * 
FROM students 
WHERE age BETWEEN 18 AND 25;

This will return students whose age is between 18 and 25 (inclusive).

Example 6: LIKE (Pattern Matching)

If you want to find students whose names start with the letter “A”:

SELECT * 
FROM students 
WHERE name LIKE 'A%';

The % is a wildcard that represents any characters that follow “A”. So this will return all students whose names start with “A.”

Example 7: IN

If you want to find students whose scores are either 90, 85, or 80:

SELECT * 
FROM students 
WHERE score IN (90, 85, 80);

This will return students who scored 90, 85, or 80.

Practice Questions

Question 1: Find all students older than 20.

SELECT * 
FROM students 
WHERE age > 20;

Question 2: Find students who scored between 70 and 90.

SELECT * 
FROM students 
WHERE score BETWEEN 70 AND 90;

Question 3: Find students whose names end with “e”.

SELECT * 
FROM students 
WHERE name LIKE '%e';

Question 4: Find students who are either 15 or 16 years old.

SELECT * 
FROM students 
WHERE age IN (15, 16);

Question 5: Find students who do not have a score of 100.

SELECT * 
FROM students 
WHERE score != 100;

Interview Questions

Question 1: What is the purpose of the WHERE clause in SQL?

Answer: The WHERE clause is used to filter records in a SQL query based on specific conditions, allowing you to retrieve only the data that matches those conditions.

Question 2: How do you filter data using patterns in SQL?

Answer: You can use the LIKE operator with wildcards to filter data based on patterns. For example, % means any number of characters, and _ means exactly one character.

Question 3: What is the difference between = and IN in SQL?

Answer: The = operator is used to match a single value, while the IN operator is used to match a list of values. For example, age = 25 matches exactly 25, while age IN (20, 25, 30) matches 20, 25, or 30.

Question 4: Can you use multiple conditions in the WHERE clause?

Answer: Yes, you can use AND, OR, and NOT to combine multiple conditions. For example, WHERE age > 18 AND score > 80.

Question 5: What does the BETWEEN operator do in SQL?

Answer: The BETWEEN operator filters values that are within a specified range. For example, age BETWEEN 18 AND 25 will return ages from 18 to 25.

The WHERE clause is a powerful tool in SQL that lets you filter the data to meet your specific needs. By using comparison operators like =, >, <, and keywords like BETWEEN, LIKE, and IN, you can narrow down the results of your queries. Remember to practice with different conditions to get a good grasp of filtering data in SQL!

I hope this helps you understand Filtering Data with WHERE Clauses! Happy coding!