Sql · Filtering Results
Quizz
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — Identify the Correct Filter
Which SQL query will return ONLY Codémons with a power rating above 80?
Options
• SELECT * FROM codemon
WHERE power > 80;
• SELECT * FROM codemon
WHERE power >= 80;
• SELECT * FROM codemon
WHERE power = 80;
• SELECT power FROM codemon
WHERE power > 80;
Hint : Pay attention to the comparison operator. Some operators include the threshold value while others don't.
Exercise 2 — Filter by Type
Complete the query to find all Codémons that are either water or electric type.
SELECT *
FROM codemon
WHERE type ____ 'water' ____ type ____ 'electric';
Options: = == OR AND != =
Hint : Think about which logical operator should be used when you want to match either one condition OR another condition.
Exercise 3 — Match Queries with Descriptions
Match each SQL query with what it accomplishes. Every request starts with
SELECT * FROM codemon
.
Items to match
• WHERE power < 75;
• WHERE type = 'rock';
• WHERE power BETWEEN 70 AND 80;
• WHERE type <> 'water' AND type <> 'fire';
Definitions
• Find Codémons weaker than Aquachu
• Find only Rockachu
• Find Aquachu, Florachu and Sparkle
• Find non-water and non-fire Codémons
Exercise 4 — Filtering with NULL
Which query will return all Codémons that have a known type but an unknown level?
Options
• SELECT * FROM codemon
WHERE type IS NOT NULL AND level IS NULL;
• SELECT * FROM codemon
WHERE type IS NOT NULL AND level = NULL;
• SELECT * FROM codemon
WHERE type != NULL AND level = NULL;
• SELECT * FROM codemon
WHERE type IS NOT NULL OR level IS NULL;
Hint : Remember how NULL values must be handled in SQL and which operator should be used to test for NULL or NOT NULL.
Exercise 5 — Complex Filtering
Complete the query to find all fire-type Codémons with a level above 15 OR all plant-type Codémons with a power below 75.
SELECT *
FROM codemon
WHERE (type = 'fire' ____ level ____ 15) ____ (type = 'plant' ____ power ____ 75);
Options: AND > OR AND OR AND OR >= < <=
Hint : Think about how to structure compound conditions with AND and OR.
Exercise 6 — Using NOT in Filters
Which query will return all Codémons EXCEPT those with a power of 75?
Options
• SELECT * FROM codemon
WHERE NOT power = 75;
• SELECT * FROM codemon
WHERE power != 75;
• SELECT * FROM codemon
WHERE power <> 75;
• All of these queries would work
Hint : Consider the different ways to express negation in SQL and whether they're equivalent in this context.
Exercise 7 — Codémon Query Results
Match each SQL query with the Codémons it will return from the table. Every request starts with
SELECT * FROM codemon
.
Items to match
• WHERE level IS NULL OR power IS NULL;
• WHERE type IS NOT NULL AND level IS NOT NULL;
• WHERE NOT (level IS NULL);
• WHERE power IS NULL OR type = 'water';
Definitions
• Florachu, Pyrochu, Rockachu
• Aquachu, Pyrochu, Sparkle
• Aquachu, Pyrochu, Sparkle, Shadowchu
• Aquachu, Pyrochu, Rockachu
Exercise 8 — Power Range Query
Complete the query to find all Codémons with a power between 70 and 80 (inclusive).
SELECT *
FROM codemon
WHERE power ____ 70 ____ power ____ 80;
Options: >= AND <= OR BETWEEN >
Hint : Think about the logical operators needed when using comparison operators.
Note : The BETWEEN operator could also be used in a case like this. (but not in this specific exercise with these specific holes to fill)
Exercise 9 — Predict the Result
How many Codémons will the following query return?
SELECT * FROM codemon
WHERE level > 15 OR type = 'plant';
Hint : Analyze each Codémon in the table and check if it satisfies either of the conditions. Remember that OR means either condition can be true.
Exercise 10 — Complete NULL Filter
Complete the query to find all Codémons that have either a NULL power OR a NULL level, but NOT both.
SELECT *
FROM codemon
WHERE (power IS NULL AND level IS ____ NULL)
OR (power IS ____ NULL AND level IS NULL);
Options: NOT NOT NOT NOT NOT NOT
Hint : This is a complex logical condition. Think about how to express "either A or B, but not both" in SQL.
Exercise 11 — Equivalent Queries
Match each query with its equivalent alternative that would return the same results.
Items to match
• WHERE NOT level <= 15;
• WHERE level BETWEEN 15 AND 20;
• WHERE type <> 'fire';
• WHERE type = 'water' OR type = 'plant';
Definitions
• WHERE level > 15;
• WHERE level >= 15 AND level <= 20;
• WHERE NOT type = 'fire';
• WHERE type IN ('water', 'plant');
Exercise 12 — Operator Precedence
What does the following query return?
SELECT * FROM codemon
WHERE type = 'water' OR type = 'fire' AND power > 75;
Options
• All water-type Codémons and fire-type Codémons with power > 75 (only fire type)
• All Codémons either water or fire that have power > 75
• Only fire-type Codémons with power > 75
• All Codémons
Hint : This question tests your understanding of operator precedence in SQL. Remember that AND has higher precedence than OR.
Exercise 13 — Filtering Challenge
Complete the query to find Codémons that have a power not null, greater than 70 and are NOT water type.
SELECT *
FROM codemon
WHERE power ____ 70 ____ type ____ 'water'
____ power ____ NULL;
Options: > AND >< AND IS NOT OR !=
Hint : This is basic AND filtering, just pay attention to the NULL value filtering.
Exercise 14 — Finding Errors
Which query contains an error?
Options
• SELECT * FROM codemon
WHERE level = NULL;
• SELECT * FROM codemon
WHERE type IN ('water', 'fire');
• SELECT * FROM codemon
WHERE level BETWEEN 10 AND 20;
• SELECT * FROM codemon
WHERE power <> 80;
Hint : Here is an hint, don't look at the words, try to find a symbol that doesn't belong here.
Exercise 15 — Match Query with Count
Match each query with the number of Codémons it will return. All queries start with
SELECT * FROM codemon
.
Items to match
• WHERE power >= 75 AND type <> 'fire';
• WHERE type = 'water' OR type = 'plant';
• WHERE power > 80;
• WHERE power BETWEEN 70 AND 80;
Exercise 16 — Find Elite Codémons
Complete the query to find all 'elite' Codémons - those with both level above 15 and power above 75.
SELECT *
FROM codemon
WHERE level ____ 15 ____ power ____ 75
Options: > > >= >= < < <= <= OR AND
Hint : Think about which logical operator combines multiple conditions where ALL must be true.
Exercise 17 — Advanced NULL Filtering
Which query correctly finds Codémons that have no NULL values in any column?
Options
• SELECT * FROM codemon
WHERE name IS NOT NULL
AND type IS NOT NULL
AND level IS NOT NULL
AND power IS NOT NULL;
• SELECT * FROM codemon
WHERE NOT NULL
IN (name, type, level, power);
• SELECT * FROM codemon
WHERE ALL(name, type, level, power)
IS NOT NULL;
• SELECT * FROM codemon
WHERE (name, type, level, power)
IS NOT NULL;
Hint : Think brute force.
Exercise 18 — NOT IN Filter
Complete the query to find all Codémons that are neither water nor fire type.
SELECT *
FROM codemon
WHERE type ____ ____ ('water', 'fire')
Options: NOT IN , <> !=
Hint : Think about the SQL operator that lets you check if a value is not in a list of specified values.
Exercise 19 — Operator Precedence Challenge
Which query is equivalent to:
WHERE (type = 'water' OR type = 'fire')
AND level > 15;
Options
• WHERE type IN ('water', 'fire')
AND level > 15;
• WHERE type = 'water'
OR type = 'fire'
AND level > 15;
• WHERE type = 'water'
AND level < 15
OR type = 'fire'
AND level < 15;
• WHERE type IN ('water', 'fire', 'plant')
AND level > 15;
Hint : This tests your understanding of operator precedence and alternative syntax. Think about which queries would produce the exact same results.
Exercise 20 — NULL Filter Descriptions
Match each query with what it's trying to find. Every query starts with
SELECT * FROM codemon WHERE
.
Items to match
• type IS NULL;
• level IS NOT NULL AND power IS NULL;
• type IS NOT NULL AND level IS NOT NULL AND power IS NOT NULL;
• level IS NULL OR power IS NULL;
Definitions
• Codémons with unknown type
• Codémons with known level but unknown power
• Codémons with complete information (known type, level, and power)
• Codémons with at least one missing attribute between level and power
Exercise 21 — Find Specific Power Range
Complete the query to find Codémons with power that's either less than 75 or greater than 80.
SELECT *
FROM codemon
WHERE power ____ 75 ____ power ____ 80;
Options: < OR > AND <= >= NOT BETWEEN
Hint : This is testing your ability to express a range exclusion. Think about which logical operator to use to combine two conditions when at least one must be true.
Exercise 22 — Complex Filter Analysis
What will the following query return?
SELECT * FROM codemon
WHERE (type = 'water' OR type = 'fire')
AND (level IS NULL OR power > 80);
Options
• No Codémons
• Only Pyrochu
• Aquachu and Pyrochu
• All Codémons
Hint : Analyze the complex condition by breaking it down into parts and evaluating them step by step for each Codémon in the table.
Exercise 23 — Filtering Techniques
Match each filtering technique with its SQL implementation.
Items to match
• Range check
• Multiple value check
• Negation
• Exclusion
Definitions
• level BETWEEN 15 AND 20
• type IN ('water', 'fire')
• NOT type = 'fire'
• type <> 'plant'
Exercise 24 — Advanced Filtering Challenge
Complete the query to find all plant-type Codémons OR Codémons with level greater than 20, but NOT those with power less than 75.
SELECT *
FROM codemon
WHERE (type = ____ ____ level > 20)
____ ____ (power < 75);
Options: 'plant' OR AND NOT 'water' =
Hint : This tests your ability to construct complex filters with multiple conditions and proper use of parentheses for operator precedence.
Exercise 25 — Finding Missing Data
You need to find Codémons with at least two NULL values. Which query is correct?
Options
• SELECT * FROM codemon
WHERE type IS NULL AND level IS NULL
OR type IS NULL AND power IS NULL
OR level IS NULL AND power IS NULL;
• It's not possible to find Codémons with at least two NULL values.
• SELECT * FROM codemon
WHERE COUNT(NULL) >= 2;
• SELECT * FROM codemon
WHERE NULL >= 2;
Hint : Sometimes the easiest way is the one who looks almost too dumb.