CS50 2022 psets 7 movies solution

movies.sql

-- In 1.sql, write a SQL query to list the titles of all movies released in 2008

select title from movies where year=2008;

-- In 2.sql, write a SQL query to determine the birth year of Emma Stone

select birth from people where name="Emma Stone";

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

select title from movies where year >= 2018 order by title asc;

-- In 4.sql, write a SQL query to determine the number of movies with an IMDb rating of 10.0

select count(movie_id) from ratings where rating=10;

-- In 5.sql, write a SQL query to list the titles and release years of all Harry Potter movies, in chronological order

select title, year from movies where title like 'Harry Potter%' order by year;

-- In 6.sql, write a SQL query to determine the average rating of all movies released in 2012

select avg(rating) from ratings join movies on movies.id=ratings.movie_id where movies.year=2012;

-- In 7.sql, write a 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.

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;


-- In 8.sql, write a SQL query to list the names of all people who starred in 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";

-- In 9.sql, write a SQL query to list the names of all people who starred in a movie released in 2004, ordered by birth year.

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;

-- In 10.sql, write a SQL query to list the names of all people who have directed a movie that received a rating of at least 9.0.

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;

-- In 11.sql, write a SQL query to list the titles of the five highest rated movies (in order) that Chadwick Boseman starred in, starting with the highest rated.

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;

-- In 12.sql, write a SQL query to list the titles of all movies in which both Johnny Depp and Helena Bonham Carter starred.

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");

-- In 13.sql, write a SQL query to list the names of all people who starred in a movie in which Kevin Bacon also starred.

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";