Sql · Filtering Results
WHERE
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — Introduction to the WHERE Clause
In SQL, the WHERE clause allows you to filter data based on specific conditions. It's like telling the database: "I only want to see rows that match these criteria."
The basic syntax of a WHERE clause is:
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Let's look at a simple example using our Codémon table:
SELECT * FROM codemon
WHERE type = 'water';
This query returns only the Codémon with the 'water' type. The WHERE clause filters out all other types.
String Values in SQL
In SQL, text values (strings) are usually enclosed in single quotes ('). For example: WHERE name = 'Aquachu'
Exercise 2 — Comparison Operators in WHERE
The WHERE clause uses comparison operators to filter data. Here are the most common ones:
- = : Equal to
- > : Greater than
- < : Less than
- >= : Greater than or equal to
- <= : Less than or equal to
- != or <> : Not equal to
SELECT name, power FROM codemon
WHERE power > 75;
SQL Comments
Lines starting with -- are comments in SQL. They're ignored when executing the query but help explain what the code does.
-- Find Codémon with power less than or equal to 75
SELECT name, power FROM codemon
WHERE power <= 75;
-- Find Codémon that are not fire type
SELECT name, type FROM codemon
WHERE type != 'fire';
Exercise 3 — Correct WHERE Clause Syntax
Which of the following queries has the correct syntax to find all Codémon with a power greater than 70?
Options
• ```SELECT * FROM codemon WHERE power > 70;```
• ```SELECT * FROM codemon HAVING power > 70;```
• ```SELECT * WHERE power > 70 FROM codemon;```
• ```SELECT * FROM codemon WHERE power IS > 70;```
Hint : Remember that WHERE always comes after FROM and before any potential GROUP BY, HAVING, or ORDER BY clauses. (we will learn about these later)
Exercise 4 — Complete the Query
Complete the SQL query to find all Codémon with the name 'Pyrochu'.
SELECT * FROM codemon ____ name ____ 'Pyrochu';
Options: WHERE HAVING = == EQUALS IS
Hint : In SQL, we use the WHERE clause to filter rows and the = operator to check for equality.
Exercise 5 — Filtering Text Values
When filtering text values (strings) with the WHERE clause, remember to use single quotes around the text value.
SELECT * FROM codemon
WHERE name = 'Florachu';
In many SQL systems, string comparisons are case-sensitive. This means 'Florachu' and 'florachu' would be treated as different values.
Common Error: Forgetting Quotes
A common mistake is forgetting to put quotes around string values:
Incorrect: WHERE name = Florachu
Correct: WHERE name = 'Florachu'
Exercise 6 — Match Operators with Descriptions
Match each SQL operator with its correct description.
Items to match
• =
• >
• <=
• !=
Definitions
• Equal to
• Greater than
• Less than or equal to
• Not equal to
Exercise 7 — Predict the Query Result
Given this Codémon table:
How many rows will be returned by the following query?
SELECT * FROM codemon WHERE power >= 75;
Hint : Check which Codémon have power values that are greater than or equal to 75.
Exercise 8 — Find Powerful Codémon
Complete the SQL query to find all Codémon from codemon Table with a power greater than 80.
SELECT name, type, power
FROM ____
____ power ____ 80;
Options: codemon pokemon WHERE HAVING > >= <
Hint : The query should select from the codemon table and filter with the WHERE clause to find rows where power is greater than 80.
Exercise 9 — Match Queries with Results
Match each SQL query with its expected result from the codemon table.
Items to match
• SELECT name FROM codemon WHERE power > 80;
• SELECT name FROM codemon WHERE type = 'water';
• SELECT name FROM codemon WHERE power <= 75;
• SELECT type FROM codemon WHERE name != 'Pyrochu';
Definitions
• Only Rockachu (power 85)
• Only the name Aquachu
• Aquachu, Florachu, Sparkle
• water, plant, electric, rock
Exercise 10 — What Happens When No Rows Match
What happens when you run a query with a WHERE condition that no rows match? Let's find out by looking for a Codémon type that doesn't exist in our table.
SELECT * FROM codemon WHERE type = 'ice';
As you can see in the "Message" window, the query runs successfully but returns no rows. This is not an error - it simply means no data matched your condition.
Empty Result Sets
Getting an empty result set (no rows) is perfectly normal in SQL. It just means your condition didn't match any data. This is different from getting an error, which would indicate a problem with your query syntax.