Disclaimer: These answers are only for educational purposes only. Please do not use them for cheating. Cheating doesn’t do any good for you!
In this post, I’ll give you the solution to the CS50 psets 7 movies problem. In this problem, we have to write SQL queries to answer questions about a database of movies. SQLite database called movies.db is provided to you. This database stores data from IMDb about movies, the people who directed, and starred in them, and their ratings.
There are 13 problems. We have to write SQL queries to solve each one of them. Here are my solutions to those questions.
SQL query to list the titles of all movies released in 2008.
This query should output a table with a single column for the title of each movie.
select title from movies where year=2008;
SQL query to determine the birth year of Emma Stone.
This query should output a table with a single column and a single row (not counting the header) containing Emma Stone’s birth year. For this, you may assume that there is only one person in the database with the name Emma Stone.
select birth from people where name="Emma Stone";
SQL query to list the titles of all movies with a release date on or after 2018, in alphabetical order.
This query should output a table with a single column for the title of each movie. Movies released in 2018 should be included, as should movies with release dates in the future.
select title from movies where year >= 2018 order by title asc;
SQL query to determine the number of movies with an IMDb rating of 10.0.
This query should output a table with a single column and a single row (not counting the header) containing the number of movies with a 10.0 rating.
select count(movie_id) from ratings where rating=10;
SQL query to list the titles and release years of all Harry Potter movies, in chronological order.
This query should output a table with two columns, one for the title of each movie and one for the release year of each movie. You may assume that 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.
select title, year from movies where title like 'Harry Potter%' order by year;
SQL query to determine the average rating of all movies released in 2012.
This query should output a table with a single column and a single row (not counting the header) containing the average rating.
select avg(rating) from ratings join movies on movies.id=ratings.movie_id where movies.year=2012;
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.
This query should output a table with two columns, one for the title of each movie and one for the rating of each movie.
select ratings.rating, movies.title from ratings join movies on ratings.movie_id=movies.id where movies.year=2010 order by ratings.rating desc, movies.title asc;
SQL query to list the names of all people who starred in Toy Story.
This query should output a table with a single column for the name of each person. You may assume that there is only one movie in the database with the title Toy Story.
select name from people join stars on stars.person_id=people.id join movies on stars.movie_id=movies.id where movies.title="Toy Story";
SQL query to list the names of all people who starred in a movie released in 2004, ordered by birth year.
This query should output a table with a single column for the name of each person. People with the same birth year can be listed in any order. If a person appeared in more than one movie in 2004, they should appear in your results once.
select distinct name from people join stars on stars.person_id=people.id join movies on stars.movie_id=movies.id join ratings on ratings.movie_id=movies.id where movies.year=2004 order by people.birth;
SQL query to list the names of all people who have directed a movie that received a rating of at least 9.0.
This query should output a table with a single column for the name of each person. If a person directed more than one movie that received a rating of at least 9.0, they should only appear in your results once.
select name from people join directors on directors.person_id=people.id join movies on directors.movie_id=movies.id join ratings on ratings.movie_id=movies.id where ratings.rating >= 9;
SQL query to list the titles of the five highest-rated movies (in order) that Chadwick Boseman starred in, starting with the highest-rated.
This query should output a table with a single column for the title of each movie. You may assume that there is only one person in the database with the name Chadwick Boseman.
select title from movies join stars on stars.movie_id=movies.id join people on stars.person_id=people.id join ratings on ratings.movie_id=movies.id where people.name = "Chadwick Boseman" order by ratings.rating desc limit 5;
SQL query to list the titles of all movies in which both Johnny Depp and Helena Bonham Carter starred.
This query should output a table with a single column for the title of each movie. You may assume that there is only one person in the database with the name Johnny Depp. You may assume that there is only one person in the database with the name Helena Bonham Carter.
select title from movies join stars on stars.movie_id = movies.id join people on stars.person_id = people.id where people.name = "Johnny Depp" and title in (select title from movies join stars on stars.movie_id = movies.id join people on stars.person_id = people.id where people.name = "Helena Bonham Carter");
SQL query to list the names of all people who starred in a movie in which Kevin Bacon also starred.
This 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 Kevin Bacon born in 1958. Kevin Bacon himself should not be included in the resulting list.
select name from people join stars on stars.person_id = people.id join movies on stars.movie_id = movies.id where movies.id in (select movies.id from movies join people on stars.person_id = people.id join stars on stars.movie_id = movies.id where people.name = "Kevin Bacon" and people.birth = 1958 ) and people.name != "Kevin Bacon";
Hope these solutions will help you. If it did, consider sharing with your friends which also need these answers.