In this lab we will practice writing JOIN statements to query across two tables. The tables will be associated through a "has many" and "belongs to" relationship.

1. Become comfortable writing various SQL JOIN statements
2. Select rows on both tables where certain values match using INNER JOIN
3. Write LEFT JOIN statements to get all the rows from the left table and the matching rows from the right table

Download the repo with dabase superheroes.db from this link

Open the database with SQLite Browser

Our database contains two tables: superheroes and squads. We have seeded the database with fifteen superheroes and three superhero teams. The superheroes table contains a column of foreign keys called squad_id. Each superhero is assigned to one of the squads depending upon the assigned squad_id. Therefore, a superhero "belongs to" the team with that matching id. The two tables are shown below:

Squads:

Superheroes:

Write your queries in the "Execute SQL" tab in SQLite broswer.

Write a query that returns superhero names and the squad name for only the superheroes that belongs to a squad.

The query's return dataset should like like the following:

Great! We selected all the superheroes belonging to a team. However, our formatting is messy. Let’s rewrite this query to group all the above superheroes according to their team name. Also, both columns are called name, so let’s alias the squads.name to team.

The query should return the following:

Cool! However, let's take another look at our superheroes table. Aren’t there some heroes belonging to no team? Write a query that returns the names and superpowers of all superheroes regardless of their affiliation to a team. Again, make sure to also include squads.name aliased as team.

The query should return the following:

Take another look at our squads table. Notice that there is also one squad with NO members! Write a JOIN statement that returns the name of each squad and a count for all of its members aliased as num_of_members. (Hint: You will need to use a GROUP BY clause here.)

Note that we are using sqlite3, which does not support RIGHT JOIN or FULL OUTER JOIN. We need to reformat our query so that we can use LEFT JOIN to get the result we want.

The query should return the following:

The Illuminati haven't existed since the late 18th Century, so perhaps their lack of members isn't such a surprise!

Great job! In this lab, we practiced writing JOIN statements that return to us information from two tables instead of just one table, like when we were writing SELECT statements.