Movies Database

movies.sql

create table movies (
  movie_id integer primary key,
  title text
);

create table actors (
  actor_id integer primary key,
  name text
);

create table movies_actors (
  movie_id integer,
  actor_id integer
);

create table reviews (
  review_id integer primary key,
  movie_id integer,
  score integer
);

insert into movies (movie_id, title)
values
(1, 'Spider-Man: No Way Home'),
(2, 'Dune'),
(3, 'Avengers: Endgame');

insert into actors (actor_id, name)
values
(11, 'Zendaya'),
(12, 'Tom Holland');

insert into movies_actors (movie_id, actor_id)
values (1, 11), (1, 12), (2, 11), (3, 12);

insert into reviews (review_id, movie_id, score)
values
(101, 1, 10),
(102, 1, 10),
(103, 1, 10),
(104, 2, 10),
(105, 2, 8),
(106, 2, 8),
(107, 3, 10),
(108, 3, 9),
(109, 3, 9);

This is a bit more elegant in my opinion:

select
    title,
    name,
    avg(score)
from movies m
    join movies_actors using(movie_id)
    join actors a using(actor_id)
    join (
        select movie_id
        from actors join movies_actors using(actor_id)
        where actors.name = 'Zendaya'
    ) sub using(movie_id)
    left outer join reviews using(movie_id)
group by 
    title,
    name