Set 7 in the SQL Programming Language

Movies

• SQL queries to answer questions about a database of movies.
• SQL query that outputs the results specified by each problem.

Specification

1. SQL query that lists the titles of all movies released in 2008.

2. Query should output a table with a single column and a single row (plus optional header) containing Emma Stone’s birth year.

3. SQL query to list the titles of all movies with a release date on or after 2018, in alphabetical order

4. SQL query to determine the number of movies with an IMDb rating of 10.0. Query should output a table with a single column and a single row (plus optional header) containing the number of movies with a 10.0 rating.

5. SQL query to list the titles and release years of all Harry Potter movies, in chronological order. Query should output a table with two columns, one for the title of each movie and one for the release year of each movie. The title of all Harry Potter movies will begin with the words “Harry Potter”, and that if a movie title begins with the words “Harry Potter”, it is a Harry Potter movie.

6. SQL query to determine the average rating of all movies released in 2012. Query should output a table with a single column and a single row (plus optional header) containing the average rating.

7. SQL query to list all movies released in 2010 and their ratings, in descending order by rating. For movies with the same rating, order them alphabetically by title. Query should output a table with two columns, one for the title of each movie and one for the rating of each movie. Movies that do not have ratings should not be included in the result.

8. SQL query to list the names of all people who starred in Toy Story. Query should output a table with a single column for the name of each person. There is only one movie in the database with the title Toy Story.

9. SQL query to list the names of all people who starred in a movie released in 2004, ordered by birth year. Query should output a table with a single column for the name of each person. People with the same birth year may be listed in any order. No need to worry about people who have no birth year listed, so long as those who do have a birth year are listed in order. If a person appeared in more than one movie in 2004, they should only appear in your results once.

10. SQL query to list the names of all people who have directed a movie that received a rating of at least 9.0. Query should output a table with a single column for the name of each person.

11. SQL query to list the titles of the five highest rated movies (in order) that Chadwick Boseman starred in, starting with the highest rated. Query should output a table with a single column for the title of each movie. There is only one person in the database with the name Chadwick Boseman.

12. SQL query to list the titles of all movies in which both Johnny Depp and Helena Bonham Carter starred. Query should output a table with a single column for the title of each movie. There is only one person in the database with the name Johnny Depp. There is only one person in the database with the name Helena Bonham Carter.

13. SQL query to list the names of all people who starred in a movie in which Kevin Bacon also starred. Query should output a table with a single column for the name of each person. There may be multiple people named Kevin Bacon in the database. Be sure to only select the Kevin Bacon born in 1958. Kevin Bacon himself should not be included in the resulting list.