18.6. Chapter Exercises

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:

Column Name

Description

id

A unique number for each record

imdb_id

The id of the movie in the Internet Movie DataBase (IMDB)

title

Title fo the movie

director

Name of the director

year

Year (number) the movie was released

rating

Rating (R, PG, etc…)

genre

Comma separated list of genres

runtime

Length in minutes

country

Comma separated list of countries it was released in

language

Comma separated list of languages it was released in

imdb_score

Score of movie (1-10) in IMDB

imdb_votes

Number of ratings for the movie in the IMDB

metacritic_score

Score of movie (1-100) on the Metacritic website

Write a query to find the average IMDB score for all the movies.

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 display the total number of 'PG-13' movies in the database.

Use GROUP BY to write a query to display the total number of movies in each rating category. Make sure to select the rating and the total count. Your output should look like:

... PG | 43 PG-13 | 32 ...

Write a query to get the highest rated movie according to IMDB score for each director. (It is OK to treat a list of directors as a “separate director”). Display the director’s name and the IMDB score achieved. Order the output so the directors with the highest scores come first.

Write a query to get the total number of films on the list made by each director. But only retrieve the data for directors with at least 2 films.

Display the director’s name and the number of films. Don’t sort the results.

Hint: Remember that you have to use HAVING to filter grouped results.

Write a query to get the total number of films on the list made by each director that have an IMDB rating of 8.5 or higher.

Display the director’s name and the number of films. Sort the results so the directors with the most high rated files are first.

Hint: Here you want to filter the movies by IMDB rating before they are aggregated, so you need a WHERE not a HAVING.

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.

Column Name

Description

id

A unique number for each record

movie_id

The id of the movie the actor appears in. This will match the id field in the movies table.

imdb_id

ID of the actor on the IMDB website

name

Name of the actor

We would like to know the title of all the movies Marlon Brando acted 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'.

Display the highest Metacritic score for the movies each actor appeared in.

Display the actor’s name and the highest score (we won’t know what movie it is for).

You will have to join actors and movies so that you have access to all the needed data. You will also have to GROUP BY. The GROUP BY should come after the JOIN.

You have attempted of activities on this page