Welcome back! In this lesson, we're going to dive into SQL aggregate functions on multiple tables. This lesson will combine everything you have learned so far about SQL queries.

Let's get started putting everything you've learned together.

For convenience, the tables and columns in our Marvel movies dataset are:

Movies Table

Movie Details Table

Characters Table

Let's begin with our first example, where we will aggregate total box office earnings by phase. We will use an INNER JOIN on the movies and movie_details tables. We will then use the SUM function and the GROUP BY clause to find the total box office earnings per phase. This advanced query is:

There's a lot going on in this query. Let's break it down step by step.

• We first use SELECT to obtain the movies.phase column and the sum of box office sales. We use the alias Total Box Office for the result of the SUM.
• FROM movies selects the primary table
• INNER JOIN movie_details specifies the table to join with movies
• ON movies.movie_id = movie_details.movie_id matches the rows of the movies and movie_details column based on the

In this example, we want to find the average IMDb score for the movies in each phase that feature the character Thor. The steps are:

• Use INNER JOIN to join all 3 tables based on the movie_id column.
• Find the average IMDb rating
• Filter the movies by only using movies that feature Thor.
• Group the rows by movies.phase
• Sort the average IMDb ratings in descending order.

Let's take a look at the most advanced query yet:

Let's look at each line individually:

SELECT movies.phase, AVG(movie_details.imdb_rating) AS "Average Rating"

• This line specifies that our result should have a phase column from the movies table and a column called Average Rating with the average IMDb rating

FROM movies

• Specifies the movies table as the primary table from which data will be fetched.

INNER JOIN movie_details ON movies.movie_id = movie_details.movie_id

Congratulations on finishing the last lesson of this course. In this lesson, we covered how to use SQL aggregate functions such as SUM() and AVG() alongside the GROUP BY clause in PostgreSQL. We also demonstrated how to join multiple tables and filter data using the WHERE clause with conditions like LIKE.

Now it's your turn to practice these concepts. The upcoming exercises will help you solidify your understanding and build confidence in writing SQL queries using aggregate functions.

Great work, and happy querying!