Sql · Inner Join
Quizz
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — Identifying INNER JOIN Results
Which of the following best describes what this query will return?
SELECT trainers.name, codemon.name
FROM trainers
INNER JOIN ownership ON trainers.trainer_id = ownership.trainer_id
INNER JOIN codemon ON ownership.codemon_id = codemon.codemon_id;
Options
• Names of all trainers paired with the names of the Codémons they own
• All trainers, including those who don't own any Codémons
• All Codémons, including those not owned by any trainer
• All possible combinations of trainers and Codémons, regardless of ownership
Hint : Think about what INNER JOIN returns - only the matching records between tables based on the join condition.
Exercise 2 — Complete the JOIN Query
Complete the query to find all trainers and their Codémons, showing the trainer's name, hometown, and their Codémon's name and type.
SELECT trainers.name, trainers.____ , codemon.name, codemon.type
FROM trainers
____ JOIN ownership ON trainers.trainer_id = ownership.____
____ JOIN codemon ON ownership.codemon_id = codemon.codemon_id;
Options: hometown badges INNER LEFT trainer_id name INNER RIGHT
Hint : Consider which column from trainers table would contain location information, and what type of join is needed to match records between tables.
Exercise 3 — Filtering JOIN Results
Which query will correctly display the names of trainers who own Fire-type Codémons?
Options
• SELECT trainers.name
FROM trainers
INNER JOIN ownership ON trainers.trainer_id = ownership.trainer_id
INNER JOIN codemon ON ownership.codemon_id = codemon.codemon_id
WHERE codemon.type = 'Fire';
• SELECT trainers.name
FROM trainers
INNER JOIN ownership ON trainers.trainer_id = ownership.trainer_id
INNER JOIN codemon ON ownership.codemon_id = codemon.codemon_id
WHERE type = 'Fire';
• SELECT name
FROM trainers
INNER JOIN ownership ON trainer_id = trainer_id
INNER JOIN codemon ON codemon_id = codemon_id
WHERE codemon.type = 'Fire';
• SELECT trainers.name
FROM trainers, ownership, codemon
WHERE trainers.trainer_id = ownership.trainer_id
AND ownership.codemon_id = codemon.codemon_id
AND type = 'Fire';
Hint : Look for proper table qualifiers for columns and the correct filtering condition for the Fire type.
Exercise 4 — Table Aliases and Their Meaning
Match each table alias with the table it commonly represents in our database:
Items to match
• t
• c
• o
• t1 INNER JOIN t2
Definitions
• trainers
• codemon
• ownership
• Self-join on the same table
Exercise 5 — Using Table Aliases
Complete the query using table aliases (t for trainers, o for ownership, c for codemon) to find trainers who own Codémons above level 10.
SELECT ____ .name AS trainer_name, ____ .name AS codemon_name, c.level
FROM trainers ____
INNER JOIN ownership ____ ON t.trainer_id = o.trainer_id
INNER JOIN codemon ____ ON ____ .codemon_id = c.codemon_id
WHERE c.level > 10
ORDER BY c.level DESC;
Options: t c o t o c o
Hint : Remember that when using table aliases, you must be consistent throughout the query. Look at which tables the columns are coming from.
Exercise 6 — Predicting JOIN Results
How many rows will the following query return based on the provided database?
SELECT trainers.name, codemon.name
FROM trainers
INNER JOIN ownership ON trainers.trainer_id = ownership.trainer_id
INNER JOIN codemon ON ownership.codemon_id = codemon.codemon_id;
Hint : Look at the ownership table to determine how many trainer-codemon relationships exist, as INNER JOIN will return one row for each matching relationship.
Exercise 7 — Complex JOIN Query
Complete the query to find trainers with at least 6 badges who own Water-type Codémons. Show the trainer's name, the Codémon's name, and when it was caught.
SELECT t.name, c.name, o.____
FROM trainers t
INNER JOIN ____ o ON t.trainer_id = o.trainer_id
INNER JOIN codemon c ON o.codemon_id = c.codemon_id
WHERE t.badges ____ 6
AND c.type = ____ ;
Options: date_caught level ownership codemon >= = 'water'
Hint : Check the database schema to identify the correct column for the capture date, the ownership table name, and remember that strings in SQL are case-sensitive.
Exercise 8 — Identifying JOIN Errors
Which of the following queries contains an error?
Options
• SELECT t.name, c.name
FROM trainers t
INNER JOIN ownership o ON t.name = o.trainer_id
INNER JOIN codemon c ON o.codemon_id = c.codemon_id;
• SELECT t.name, c.name
FROM trainers t
INNER JOIN ownership o ON t.trainer_id = o.trainer_id
INNER JOIN codemon c ON o.codemon_id = c.codemon_id;
• SELECT t.name, c.name
FROM trainers t
INNER JOIN ownership o ON t.trainer_id = o.trainer_id
INNER JOIN codemon c ON o.codemon_id = c.codemon_id
WHERE c.level > 10;
• SELECT t.name, c.name
FROM trainers t
INNER JOIN ownership o ON t.trainer_id = o.trainer_id
INNER JOIN codemon c ON o.codemon_id = c.codemon_id
ORDER BY t.name;
Hint : Look carefully at the join conditions - are we comparing compatible column types? Are we joining on the correct columns?
Exercise 9 — Common JOIN Operations
Match each SQL operation with its purpose in a JOIN query:
Items to match
• GROUP BY
• ON
• COUNT(*)
• HAVING
Definitions
• Groups rows with the same values
• Specifies the join condition
• Counts the number of rows
• Filters groups after GROUP BY
Exercise 10 — Understanding INNER JOIN Behavior
What would happen if we added a new Codémon to the codemon table but didn't add a corresponding entry in the ownership table?
Options
• The new Codémon would not appear in results of INNER JOIN queries between trainers, ownership, and codemon tables
• The new Codémon would appear with NULL values for trainer information
• The query would produce an error because every Codémon must have an owner
• The new Codémon would be automatically assigned to the trainer with the most badges
Hint : Think about what INNER JOIN returns - only matching records that satisfy the join condition. What happens to records that don't have matches?
Exercise 11 — Aggregating Joined Data
Complete the query to find the average level of Codémons owned by each trainer. Show the trainer's name and the average level, sorted by average level in descending order.
SELECT t.name, ____ (c.level) AS avg_level
FROM trainers t
INNER JOIN ownership o ON t.trainer_id = o.trainer_id
INNER JOIN codemon c ON o.codemon_id = c.codemon_id
____ BY t.name
ORDER BY avg_level ____ ;
Options: AVG SUM GROUP HAVING DESC ASC
Hint : Think about which SQL functions calculate averages, how to group results by trainer, and how to sort in descending order.
Exercise 12 — Choosing the Starting Table
When joining tables, which is generally the best table to start with in your FROM clause?
Options
• The table with the filtering condition that will eliminate the most rows
• The table with the most columns
• The table with the fewest columns
• The order doesn't matter in modern SQL databases
Hint : Starting with the table that has the most restrictive filtering condition helps reduce the dataset early in the query execution process.
Exercise 13 — JOIN Terminology
Match each JOIN term with its correct description:
Items to match
• INNER JOIN
• Table alias
• Foreign key
• Join condition
Definitions
• Returns only matching rows
• Shorthand name for a table
• Column that references another table
• The ON clause criteria
Exercise 14 — JOIN with Subquery
Complete the query to find trainers who own Codémons of the same type as 'Aquachu'.
SELECT DISTINCT t.name
FROM trainers t
INNER JOIN ownership o ON t.trainer_id = o.trainer_id
INNER JOIN codemon c ON o.codemon_id = c.codemon_id
WHERE c.type = (SELECT ____ FROM codemon WHERE ____ = 'Aquachu');
Options: type name level trainer_id
Hint : The subquery needs to get Aquachu's type to compare with other Codémons. Think about which columns you need to select and filter on.
Exercise 15 — Analyzing JOIN Data
Based on our database, which query would help us find trainers who own both Electric and Water type Codémons?
Options
• SELECT t.name
FROM trainers t
INNER JOIN ownership o1 ON t.trainer_id = o1.trainer_id
INNER JOIN codemon c1 ON o1.codemon_id = c1.codemon_id
INNER JOIN ownership o2 ON t.trainer_id = o2.trainer_id
INNER JOIN codemon c2 ON o2.codemon_id = c2.codemon_id
WHERE c1.type = 'water' AND c2.type = 'electric';
• SELECT t.name
FROM trainers t
INNER JOIN ownership o ON t.trainer_id = o.trainer_id
INNER JOIN codemon c ON o.codemon_id = c.codemon_id
WHERE c.type = 'water' AND c.type = 'electric';
• SELECT t.name
FROM trainers t
INNER JOIN ownership o ON t.trainer_id = o.trainer_id
INNER JOIN codemon c ON o.codemon_id = c.codemon_id
WHERE c.type IN ('water', 'electric')
GROUP BY t.name
HAVING COUNT(DISTINCT c.type) = 2;
• SELECT t.name
FROM trainers t
INNER JOIN ownership o ON t.trainer_id = o.trainer_id
INNER JOIN codemon c ON o.codemon_id = c.codemon_id
WHERE c.type = 'water' OR c.type = 'electric';
Hint : Consider which approach correctly identifies trainers who own BOTH types, not just one or the other. One approach uses self-joins, another uses GROUP BY with HAVING.
Exercise 16 — Find Trainers by Badges and Codémon Types
Complete the query to find trainers who have more than 5 badges and own either Plant or Rock type Codémons.
SELECT ____ .name, t.badges, c.name, c.type
FROM trainers t
INNER JOIN ownership o ON t.trainer_id = o.trainer_id
INNER JOIN codemon c ON o.codemon_id = c.codemon_id
WHERE t.badges > 5
AND c.type ____ ('____', '____');
Options: t c IN = plant rock water fire
Hint : Think about which table alias contains the trainer name, how to test if a value is in a list of options, and the correct type names based on the database.
Exercise 17 — Most Recently Caught Codémon
Which query would correctly find the most recently caught Codémon and its trainer?
Options
• SELECT t.name AS trainer, c.name AS codemon, o.date_caught
FROM trainers t
INNER JOIN ownership o ON t.trainer_id = o.trainer_id
INNER JOIN codemon c ON o.codemon_id = c.codemon_id
ORDER BY o.date_caught DESC
LIMIT 1;
• SELECT t.name AS trainer, c.name AS codemon, MAX(o.date_caught)
FROM trainers t
INNER JOIN ownership o ON t.trainer_id = o.trainer_id
INNER JOIN codemon c ON o.codemon_id = c.codemon_id;
• SELECT t.name AS trainer, c.name AS codemon, o.date_caught
FROM trainers t
INNER JOIN ownership o ON t.trainer_id = o.trainer_id
INNER JOIN codemon c ON o.codemon_id = c.codemon_id
WHERE o.date_caught = MAX(o.date_caught);
• SELECT t.name AS trainer, c.name AS codemon, o.date_caught
FROM trainers t, ownership o, codemon c
WHERE t.trainer_id = o.trainer_id
AND o.codemon_id = c.codemon_id
GROUP BY t.name, c.name, o.date_caught
HAVING o.date_caught = MAX(o.date_caught);
Hint : Think about how to sort data by date in descending order to get the most recent first, and how to limit results to just one row.
Exercise 18 — SQL Functions with JOINs
Match each SQL function with what it calculates in a joined query:
Items to match
• COUNT(*)
• AVG(level)
• MAX(badges)
• COUNT(DISTINCT type)
Definitions
• Total number of rows
• Average Codémon level
• Highest badge count
• Number of unique Codémon types
Exercise 19 — Counting Codémons by Type
Complete the query to count how many Codémons of each type are owned by trainers, sorted by count in descending order.
SELECT c.____ , COUNT(*) AS count
FROM codemon c
INNER JOIN ownership o ON c.codemon_id = o.codemon_id
____ BY c.type
ORDER BY count ____ ;
Options: type name GROUP HAVING DESC ASC
Hint : Think about which column we want to group by (the type), how to specify grouping in SQL, and how to sort the results to show the most common types first.
Exercise 20 — Most Diverse Trainer
Which query would find the trainer who owns the most diverse collection of Codémon types?
Options
• SELECT t.name, COUNT(DISTINCT c.type) AS type_count
FROM trainers t
INNER JOIN ownership o ON t.trainer_id = o.trainer_id
INNER JOIN codemon c ON o.codemon_id = c.codemon_id
GROUP BY t.name
ORDER BY type_count DESC
LIMIT 1;
• SELECT t.name, COUNT(c.type) AS type_count
FROM trainers t
INNER JOIN ownership o ON t.trainer_id = o.trainer_id
INNER JOIN codemon c ON o.codemon_id = c.codemon_id
GROUP BY t.name
ORDER BY type_count DESC
LIMIT 1;
• SELECT t.name, MAX(c.type) AS types
FROM trainers t
INNER JOIN ownership o ON t.trainer_id = o.trainer_id
INNER JOIN codemon c ON o.codemon_id = c.codemon_id
GROUP BY t.name;
• SELECT t.name
FROM trainers t
INNER JOIN ownership o ON t.trainer_id = o.trainer_id
INNER JOIN codemon c ON o.codemon_id = c.codemon_id
ORDER BY c.type
LIMIT 1;
Hint : To find diversity, we need to count the distinct types owned by each trainer, not just the total number of Codémons. Which query correctly groups, counts distinct types, and limits to the top result?
Exercise 21 — Finding Trainers with Same Hometown
Complete the query to find pairs of trainers who come from the same hometown.
SELECT t1.name AS trainer1, t2.name AS trainer2, t1.hometown
FROM trainers t1
INNER JOIN trainers ____ ON t1.hometown = t2.hometown
WHERE t1.trainer_id < t2.trainer_id
ORDER BY t1.____ ;
Options: t2 t3 hometown name
Hint : This is a self-join where we join the trainers table to itself. The condition t1.trainer_id < t2.trainer_id ensures we don't get duplicate pairs in reverse order.
Exercise 22 — Optimizing JOIN Queries
Which of the following techniques would NOT typically improve the performance of a JOIN query?
Options
• Always using ORDER BY in every JOIN query to ensure consistent results
• Filtering data before joining (applying WHERE clauses early)
• Using table aliases to make queries more readable
• Selecting only the specific columns needed rather than using SELECT *
Hint : Think about which of these techniques might add overhead rather than reduce it. Which ones are about clarity vs. performance?
Exercise 23 — SQL Keywords in JOIN Queries
Match each SQL keyword with its order of appearance in a typical JOIN query:
Items to match
• SELECT
• FROM
• JOIN
• WHERE
Exercise 24 — Recent Codémon Acquisitions
Complete the query to find all Codémons caught in 2023, showing the trainer name, Codémon name, and date caught, ordered by date (most recent first).
SELECT t.name AS trainer, c.name AS codemon, o.date_caught
FROM trainers t
INNER JOIN ownership o ON t.trainer_id = o.____
INNER JOIN codemon c ON o.codemon_id = c.____
WHERE o.date_caught ____ '2023%'
ORDER BY o.date_caught ____ ;
Options: trainer_id name codemon_id LIKE = DESC ASC
Hint : Think about which columns link the tables together, how to filter dates by year using LIKE with a wildcard, and how to sort in descending order to get the most recent first.
Exercise 25 — Finding Specific Trainers
Which query would find trainers who own Water-type Codémons with a level higher than 10?
Options
• SELECT t.name
FROM trainers t
INNER JOIN ownership o ON t.trainer_id = o.trainer_id
INNER JOIN codemon c ON o.codemon_id = c.codemon_id
WHERE c.type = 'water' AND c.level > 10;
• SELECT t.name
FROM trainers t
INNER JOIN ownership o ON t.trainer_id = o.trainer_id
INNER JOIN codemon c ON o.codemon_id = c.codemon_id
WHERE c.type = 'water' OR c.level > 10;
• SELECT t.name
FROM trainers t
INNER JOIN ownership o ON t.trainer_id = o.trainer_id
WHERE o.codemon_id IN (SELECT codemon_id FROM codemon WHERE type = 'water');
• SELECT t.name
FROM trainers t, ownership o, codemon c
WHERE t.trainer_id = o.trainer_id
AND c.type = 'water';
Hint : We need to join all three tables and filter on both the type and level conditions.