Write a SQL query to list the titles and release years of all Harry Potter movies, in chronological order.
- SELECT m.title FROM movies AS m WHERE m.title LIKE '%Harry Potter%' ORDER BY year ASC;
write a SQL query to determine the average rating of all movies released in 2012.
- SELECT AVG(r.rating) AS 'Average rating from movies released in 2012' FROM ratings As r JOIN movies as m ON r.movie_id = m.id WHERE m.year = 2012;
write a SQL query to list all movies released in 2019 and their ratings, in descending order by rating. For movies with the same rating, order them alphabetically by title.
- SELECT m.title AS 'Movies released in 2019', r.rating FROM movies AS m JOIN ratings AS r ON r.movie_id = m.id WHERE m.year = 2019 ORDER BY r.rating DESC, m.title ASC;
write a SQL query to list the names of all people who starred in Spider-man. (I don't know if the question means only Spider-Man or all the Spider-Man titles, therefore I went with all the Spider-Man titles);
- SELECT p.name AS 'Names of all the people who starred in Spider-Man' FROM people AS p JOIN stars AS s ON s.person_id = p.id JOIN movies AS m ON s.movie_id = m.id WHERE m.title LIKE '%Spider-Man%';
write a SQL query to list the names of all people who starred in a movie released in 2004, ordered by birth year.
- SELECT p.name AS 'People who starred in movies released in 2004' FROM people AS p JOIN stars AS s ON s.person_id = p.id JOIN movies AS m ON s.movie_id = m.id WHERE m.year = 2004 ORDER BY p.birth ASC;
write a SQL query to list the names of all people who have directed a movie that received a rating of at least 8.0
- SELECT p.name AS 'People who have directed a movie that received a rating of at least 8.0' FROM people AS p JOIN directors AS d ON p.id = d.person_id JOiN movies AS m ON d.movie_id = m.id JOIN ratings AS r ON r.movie_id = d.movie_id WHERE r.rating >= 8.0;
write a SQL query to list the titles of the five highest rated movies (in order) that Zendaya starred in, starting with the highest rated.
- SELECT m.title AS 'Titles of the five highest rated movies Zendaya starred in' FROM movies AS m JOIN stars AS s ON m.id = s.movie_id JOIN people AS p ON s.person_id = p.id JOIN ratings AS r ON r.movie_id = m.id WHERE p.name = 'Zendaya' ORDER BY r.rating DESC LIMIT 5;
write a SQL query to list the titles of all movies in which both Tom Holland and Andrew Garfield starred.
- SELECT m.title AS 'Titles of movies starring both Tom Holland and Andrew Garfield' FROM movies AS m JOIN stars AS s1 ON m.id = s1.movie_id JOIN people AS p1 ON s1.person_id = p1.id JOIN stars AS s2 ON m.id = s2.movie_id JOIN people AS p2 ON s2.person_id = p2.id WHERE p1.name = 'Tom Holland' AND p2.name = 'Andrew Garfield';
write a SQL query to list the names of all people who starred in a movie in which Tobey Maguire also starred.
- SELECT DISTINCT p.name AS 'Names of people who starred in a movie with Tobey Maguire' FROM people AS p JOIN stars AS s1 ON p.id = s1.person_id JOIN movies AS m ON s1.movie_id = m.id JOIN stars AS s2 ON m.id = s2.movie_id JOIN people AS p2 ON s2.person_id = p2.id WHERE p2.name = 'Tobey Maguire' AND p.name != 'Tobey Maguire';
write a SQL query to find the actor who has the highest difference between their best and their worst rated movie
- SELECT p.name AS 'Actor with the highest difference between best and worst rated movie' FROM people AS p JOIN stars AS s ON p.id = s.person_id JOIN movies AS m ON s.movie_id = m.id JOIN ratings AS r ON r.movie_id = m.id GROUP BY p.name ORDER BY MAX(r.rating) - MIN(r.rating) DESC LIMIT 1;