movie_queries.txt

1) What are the top 50 worst rated movies? The results should include the movie title and rating and be sorted by the worst rating first.

SELECT movies.title, movies.rating FROM movies
WHERE movies.rating < 5
ORDER BY movies.rating LIMIT 50;

2) What movies do not have a rating? The results should include just the
movie titles in sorted order.

SELECT movies.title FROM movies
WHERE movies.rating IS NULL;

3) What movies have the word "thrilling" in their synopsis? The results
should just include the movie title.

SELECT movies.title FROM movies
WHERE movies.synopsis ILIKE '%thrilling%

4) What were the highest rated 'Science Fiction & Fantasy' movies
released in the 80's? The results should include the movie title, the
year released, and rating sorted by highest rating first.

SELECT movies.title, movies.year, movi
JOIN genres ON movies.genre_id = genres.id
WHERE genres = 'Science Fiction & Fantasy' AND movies.year > 1979 AND movies.year < 1990
ORDER BY movies.rating DESC;

5) What actors have starred as James Bond? The results should include
the actor name, movie title, year released, and be sorted by year in
ascending order (earliest year appears first).

SELECT movies.title, movies.year, actors.name FROM movies
JOIN cast_members ON cast_members.movie_id = movies.id
JOIN actors ON actors.id = cast_members.actor_id
WHERE cast_members.character = 'James Bond'
ORDER BY movies.year;

6) What movies has Julianne Moore starred in? The results should include
the movie title, year released, and name of the genre, sorted by genre
first and then movie title.

SELECT movies.title, movies.year, genres.name FROM movies
  JOIN cast_members ON movies.id = cast_members.movie_id
  JOIN actors ON actors.id = cast_members.actor_id
  JOIN genres ON genres.id = movies.genre_id
WHERE actors.name = 'Julianne Moore'
ORDER BY genres.name;

7) What were the five earliest horror movies and what studios produced
them? Include the movie title, year released, and studio name (if any)
in the results sorted by year.

SELECT movies.title, movies.year, studios.name FROM movies
JOIN studios ON studios.id = movies.studio_id
JOIN genres ON genres.id = movies.genre_id
WHERE genres.name ILIKE '%horror%'
ORDER BY movies.year LIMIT 5;