How can I generate SQL query with average score for movie from different tables?

Question

I would very much appreciate your help with this query. My database (Oracle) has a table called MOVIES with fields like id and title. Then there is another one, SCORES which has fields like movie_id and score. It is a one-to-many relation.

Now, I'm looking for a way to get a SELECT that will give me a result like one below:

movie-title, avg(score where movie.id=scores.movie_id)

It looks simple at first glance, but I cannot pass through it. Any suggestions?

Answer 1

First off, you need to join the tables on the pk/fk

Then, in order to get the avg for each movie, you need to use group and specify the column (score) that you want.

In this case we are grouping by the id because it's unique and the movie title since it is in the select clause

select m.title, avg(s.score)
from movie m
inner join scores s
on m.id = s.movie_id
group by m.id, m.title