CSE 154
Section 10: Multi-Table SQL Queries
Section Agenda
In this week's section, we will wrap up our review of SQL with some multi-table (join) query practice!
Part I: Multi-table Query Review
When you need to return information from more than one table in a database, you can join multiple tables in a single query by specifying criteria to join on (e.g. all actor names in the actors table for which the actor starred a role in the roles table, based on matching the actor's id with the role's actor_id column).
Multi-table queries are powerful features of SQL, but figuring out the optimal way to join multiple tables for one query takes plenty of practice.
Template of a Multi-table Query (Join)
SELECT col(s)
FROM table1, table2, ...
WHERE table1.a = table2.b
AND table2.c > '42';
SQL (template)
SELECT s.name, g.*
FROM students s, grades g
WHERE s.id = g.student_id
AND g.grade > 'C';
SQL (example with WHERE Join)
SELECT s.name, g.*
FROM students s
JOIN grades g ON s.id = g.student_id
WHERE g.grade > 'C';
SQL (equivalent example with JOIN keyword)
Designing a Query
Figure out the proper SQL queries in the following way:
- Which table(s) contain the critical data? (
FROM) - Which columns to I need in the result set? (
SELECT) - How are tables connected (
JOINand/orWHERE) and values filtered (WHERE)? - Do I need to return only
DISTINCTrecords? - Do I care about the order of records returned? If so, which columns do I need to sort by and in what precedence?
Database Options for Multi-Table Query Practice
We have provided a number of practice queries for you in this lab. You may choose which datasets you'd like to work with from a few different options and we recommend you practice the rest at home. The first two are included on the CSE 154 Query Tester website, and the third has a provided setup.sql file which you can import on phpMyAdmin.
Option I: World Database
Option II: IMDB Movies (Small) Database
Option III: Zelda Temple Database
SQL Option I: World Database
We will use the CSE 154 Query Tester for this dataset.
Warm-up Query: Countries in Asia
List all the full names, population, and gnp of all countries in Asia, ordered in descending order by gnp, breaking ties ordering by population in descending order. Expected results:
+---------------------------------------+
| name | population | gnp |
+---------------------------------------+
| Japan | 126714000 | 3787042.00 |
| China | 1277558000 | 982268.00 |
| India | 1013662000 | 447114.00 |
| South Korea | 46844000 | 320749.00 |
| Taiwan | 22256000 | 256254.00 |
| ... | ... | ... |
+---------------------------------------+
51 rows in set
Expected results (first 5 rows)
Query 1: English- and French-Speaking Countries
List the full names of all countries and their continent where either English or French is spoken
as an official language. Order the results by name alphabetically (ascending). Do this
as a single multi-table query. Hint: AND has a higher precedence than OR in MySQL. You
can use () similar to other languages to help specify precedence. For example,
X AND Y OR Z is
different than X AND (Y OR Z).
+----------------------+----------------+
| name | continent |
+---------------------------------------+
| American Samoa | Oceania |
| Anguilla | North America |
| Antigua and Barbuda | North America |
| Australia | Oceania |
| Barbados | North America |
| ... | ... |
+---------------------------------------+
62 rows in set (0.02 sec)
Expected results (first 5 rows)
Query 2: Languages in France
List all of the languages that are spoken in France, as well as the expected number of speakers for that language (population * percentage / 100.0). Order the results in descending order by percentage, breaking ties by language in ascending order. Round your values for estimated speakers to the nearest integer. Do this as a single multi-table query. Do not hard code the country code into your query.
+--------------+-----------+--------------+--------------------+
| language | official | percentage | estimated_speakers |
+--------------+-----------+--------------+--------------------+
| French | T | 93.6 | 55435254 |
| Arabic | F | 2.5 | 1480642 |
| Portuguese | F | 1.2 | 710708 |
| Italian | F | 0.4 | 236903 |
| Spanish | F | 0.4 | 236903 |
| Turkish | F | 0.4 | 236903 |
+--------------+-----------+--------------+--------------------+
Expected results (6 rows)
Query 3: Cities in China
List all of the cities in China, whose population is more than 0.2% of China's population. The results should include the name of the city, the district, the population, and the percentage of the city's population based on the total population of China. Organize results by percentage where the city with the highest population ratio comes first in the result. Do this as a single query. Do not hard code China's country code.
+-----------+-----------+-----------------+-------------+
| name | district | population | percentage |
+-----------+-----------+-----------------+-------------+
| Shangai | Shangai | 9696300 | 0.0076 |
| Peking | Peking | 7472000 | 0.0058 |
| Chongqing | Chongqing | 6351600 | 0.0050 |
| Tianjin | Tianjin | 5286800 | 0.0041 |
| Harbin | Heilongjiang | 4289800 | 0.0034 |
| ... | ... | ... | ... |
+-----------+--------------+--------------+-------------+
14 total rows returned
Expected results (first 5 rows)
Query 4: High Population Countries
List the full names of all countries that contain at least 2 cities of at least 5,000,000 people. Do this as a single multi-table query.
+----------+
| name |
+----------+
| Brazil |
| India |
| China |
| Pakistan |
+----------+
4 rows in set
Expected results (4 rows)
SQL Option I: Query Solutions
SQL Option II: Movie (IMDB-Small) Database
We will use the CSE 154 Query Tester for this dataset.
Query 1: Roles in Pi
List all roles played in the movie named Pi. To achieve this, join the movies table with the roles table and filter out all records except those in Pi. You shouldn't need to know the movie ID of Pi ahead of time to do the query.
+-------------------------+
| role |
+-------------------------+
| Man Delivering Suitcase |
| Brad |
| ... |
| Devi |
+-------------------------+
28 rows returned
Expected results
Query 2: Actors in Pi
List the first/last names of all actors who appeared in Pi, along with their roles. Order by last name ascending, breaking ties by first name (also ascending). Hint: You will need to join all three tables.
+------------------+------------+-------------------------+
| first_name | last_name | role |
+------------------+------------+-------------------------+
| Abraham | Aronofsky | Man Delivering Suitcase |
| Peter | Cheyenne | Brad |
| ... | ... | ... |
| Tom (I) | Tumminello | Ephraim |
+------------------+------------+-------------------------+
28 rows returned
Expected results
Query 3: Actors and Genres
List all roles in a Horror or Sci-Fi movie with the first and last name of the actor who played the role, as well as the movie name. Order the results by last name alphabetically, breaking ties by first name alphabetically, further breaking ties by movie name alphabetically.
+-------------+-----------+-------------------------+-------------+
| first_name | last_name | role | movie_name |
+-------------+-----------+-------------------------+-------------+
| Steve | Altes | Dad | Hollow Man |
| Julian | Arahanga | Apoc | Matrix, The |
| William (I) | Armstrong | Lydecker | Aliens |
| Abraham | Aronofsky | Man Delivering Suitcase | Pi |
| Graham | Ashley | Gold Five | Star Wars |
| Jennifer | Aspen | Nina | Vanilla Sky |
|... | ... | ... | ... |
| Bill(I) | Young | Lieutenant | Matrix, The |
+-------------+-----------+-------------------------+-------------+
333 rows returned
Expected results
SQL Option II: Query Solutions
SQL Option III: Zelda Temples Database
For this dataset, we have provided a
setup-zelda.sql file containing code
for populating 4 tables related to the Zelda video game. The main table
(Temple) has data for 11 temples ("dungeons") and the other three
tables contain information shared by different temples.
Overview: The Legend of Zelda, Ocarina of Time is an action-adventure video game first developed for the Nintendo 64, released in 1998.
In the game, the player will play as Link (NOT ZELDA), on a quest to stop Ganondorf from acquiring the ever-power triforce, a wish-granting relic which would give the evil king incredible power. Link must traverse multiple different temples (dungeons), defeat multiple different enemies, and acquire multiple different items in order to reach this goal and save his kingdom.
Temple Table
This table stores data for the 11 temples (dungeons) Link explores in his quests. Each temple has a unique ID (1 through 11), a name, completion time representing the fastest time the temple has been completed (in seconds), and the name of a "sage" associated with the temple. If a temple does not have a sage, the value will be NULL.
+-----+--------------------+-----------------+---------+
| tid | name | completion_time | sage |
+-----+--------------------+-----------------+---------+
| 1 | Deku Tree | 72 | NULL |
| 2 | Dodongo's Cavern | 132 | NULL |
| 3 | Jabu Jabu's Belly | 116 | NULL |
| 4 | Forest Temple | 168 | Saria |
| 5 | Fire Temple | 130 | Darunia |
| 6 | Ice Cavern | 88 | NULL |
| 7 | Water Temple | 106 | Ruto |
| 8 | Bottom of the Well | 59 | NULL |
| 9 | Shadow Temple | 99 | Impa |
| 10 | Spirit Temple | 476 | Nabooru |
| 11 | Ganondorf's Castle | 219 | NULL |
+-----+--------------------+-----------------+---------+
Temple table data (11 rows total)
Enemies Table
This table stores information about the different enemies in each temple.
first_seen is a reference to the primary key tid of the
Temple table and location is the location of the corresponding
enemy in that temple.
+--------------------+------------+-------------+
| name | first_seen | location |
+--------------------+------------+-------------+
| Anubis | 10 | floor |
| Armos | 2 | floor |
| Baby Dodongo | 2 | underground |
| Bari | 3 | ceiling |
| Beamos | 2 | floor |
| Big Deku Baba | 0 | floor |
| Big Poe | 0 | flies |
| ... | ... | ... |
+--------------------+------------+-------------+
Enemies table data (66 rows total)
Bosses Table
This table stores information about the different bosses in each temple.
temple_id is a reference to the primary key of the Temple
table, name is the name of the boss, dungeon_floor is the
floor of the boss (e.g. "2F" stands for "2nd floor"), final is the boolean
for whether the boss is a final boss in that temple (1 for true, else 0 represents a
"mini boss") and weakspot is the spot for which the boss is weak.
+-----------+---------------+-----------------+-------+----------+
| temple_id | name | dungeon_floor | final | weakspot |
+-----------+---------------+-----------------+-------+----------+
| 3 | Barinade | 2F | 1 | torso |
| 3 | Bigocto | 1F | 0 | back |
| 9 | Bongo Bongo | 5B | 1 | hands |
| 7 | Dark Link | 3F | 0 | torso |
| 8 | Dead Hand | 1F | 0 | face |
| 5 | Flare Dancer | multiple floors | 0 | torso |
| 11 | Ganon | 1F | 1 | tail |
| 11 | Ganondorf | 6F | 1 | body |
| ... | ... | ... | ... | ... |
+-----------+---------------+-----------------+-------+----------+
Bosses table data (15 rows total)
Item Table
This table stores information about the different items in each temple.
temple_id is a reference to the primary key of the Temple
table, name is the name of the item, and upgrades is the type
of upgrades for each item (NULL if the item doesn't have any available upgrades)
+----------------+-------------+------------------------------+
| name | temple_id | upgrades |
+----------------+-------------+------------------------------+
| Bombchus | 10 | NULL |
| Bombs | 2 | 2 Bomb Bag Capacity Upgrades |
| Boomerang | 3 | NULL |
| Boss Key | 12 | NULL |
| Compass | 12 | NULL |
| ... | ... | ... |
+----------------+-------------+------------------------------+
Item table schema (19 rows total)
Warmup Queries
First, we'll try a few single-table queries to get familiar with the datasets before practicing harder multi-table queries. Feel free to move on to the multi-table queries if you would rather skip these.
Warmup Query 1: First Floor Bosses
Find the name of all bosses found on the first floor of their temple.
+--------------+
| name |
+--------------+
| Bigocto |
| Dead Hand |
| Ganon |
| King Dodongo |
| Poe Sisters |
| Volvagia |
+--------------+
6 rows in set (0.01 sec)
Expected results
Warmup Query 2: First Floor Bosses
Find the name of all enemies which can be found underground.
+----------------+
| name |
+----------------+
| Baby Dodongo |
| Business Scrub |
| Deku Scrub |
| Leever |
| Mad Scrub |
| Stalchild |
+----------------+
6 rows in set (0.00 sec)
Expected results
Warmup Query 3: Speedruns
Find all temples which have a speed run in less than 2 minutes. Your output should have two columns: the name of the temple, and the speed it was ran in. Order the resuts by completion time in ascending order.
+--------------------+-----------------+
| name | completion_time |
+--------------------+-----------------+
| Bottom of the Well | 59 |
| Deku Tree | 72 |
| Ice Cavern | 88 |
| Shadow Temple | 99 |
| Jabu Jabu's Belly | 116 |
| Water Temple | 106 |
+--------------------+-----------------+
6 rows in set (0.00 sec)
Expected results
Query 1 (Items in Forest Temple or Deku Tree)
Find all items which can be obtained from the Forest Temple or the Deku Tree temples.
+-----------+
| name |
+-----------+
| Fairy Bow |
+-----------+
1 row in set (0.01 sec)
Expected results
Query 2 (Shadow temple enemies)
Find the temple name and enemy name for all enemies who appear in the Shadow Temple. Alias the column names as shown in the expected output below to distinguish between the identical 'name' attribute shared by the different tables you'll need to join.
+---------------+-------------+
| temple_name | enemy_name |
+---------------+-------------+
| Shadow Temple | Eye Switch |
| Shadow Temple | Floormaster |
+---------------+-------------+
2 rows in set (0.00 sec)
Expected results (x rows total)
Query 3 (Upgradable item from Donongo's Cavern)
Find the item from the Dodongo's Cavern which has an upgrade. What is it?
Query 4 (Enemies preceding Twinrova)
Find all lesser enemies and their locations of the temple where Twinrova is the final boss. Sort these by enemy name in reverse alphabetical order.
+--------------+-------------+
| name | location |
+--------------+-------------+
| White Bubble | floor |
| Leever | underground |
| Iron Knuckle | floor |
| Guay | flies |
| Anubis | floor |
+--------------+-------------+
5 rows in set (0.00 sec)
Expected results
Query 5 (Enemies living around upgradable items)
Find enemies which reside in a temple where an upgradable item can be found. output the name of the temple, the name of the enemy, and the item name. Use aliases for each column name as shown below to distinguish between the shared 'name' attribute name between the different tables. Order your results by 'temple_name' ascending, breaking ties by 'item_name' also ascending.
+------------------+---------------+-----------+
| temple_name | enemy_name | item_name |
+------------------+---------------+-----------+
| Dodongo's Cavern | Armos | Bombs |
| Dodongo's Cavern | Baby Dodongo | Bombs |
| Dodongo's Cavern | Beamos | Bombs |
| ... | ... | ... |
| Forest Temple | Wolfos | Fairy Bow |
+------------------+---------------+-----------+
12 rows in set (0.00 sec)
Expected results (4 rows shown)
Query 6 (Bonus Query)
What is the name of the most dangerous enemy in the Legend of Zelda, Ocarina of Time?