The exercises for this chapter use a different database than the rest of the chapter. The movies database has two tables, movies and actors. We will be using the movie table (so queries will look like SELECT * from movies). It features the following columns of data:
Write a query to find the highest IMDB score for any movie that was directed at least partially by Quentin Tarantino. (Hint: A movie can have multiple directors, so you will need to use director LIKE '%Quentin Tarantino%' to identify movies where Quentin Tarantino is in the list of directors.)
Write a query to list each director with the highest (MAX) imdb_score that director’s movies have earned. Order the output so the directors with the highest imdb_score come first.
Write a query to list each director with the COUNT of the number of films they have made. But only retrieve the data for directors with at least 2 films.
The following problems will also make use of the actors table. It consists of records that match a movie to a particular actor. If an actor appears in multiple movies, they will appear in one record for each movie they appear in.
Write a query that gets just the movies.title from the results of joining the actors table with the movies table (so that actors.movie_id matches with movies.id) and selecting rows in which the actors.name is "Marlon Brando".
Write a query that displays each actor.name with the highest metacritic_score of any movie that actor has been in (we won’t know what movie the score is from).
You will have to join actors and movies so that you have access to all the needed data. You will also have to use GROUP BY. The GROUP BY should come after the JOIN.