-- Database 2: IMDB

-- Query 1: Roles in Pi
-- Show all roles played in the movie named Pi. To achieve this, join the movies table with the
-- roles table and filter out all records except those in Pi. You shouldn't need to know the movie
-- ID of Pi ahead of time to do the query.
SELECT role
FROM roles, movies
WHERE movie_id = movies.id AND name = 'Pi';

-- Query 2: Actors in Pi
-- Show the first/last names of all actors who appeared in Pi, 
-- along with their roles. Order the results by last name ascending,
-- breaking ties by first name (also ascending).
SELECT first_name, last_name, role
FROM roles, actors, movies
WHERE roles.movie_id = movies.id
AND roles.actor_id = actors.id
AND movies.name = 'Pi'
ORDER BY last_name, first_name;
    
-- Query 3: Actors and Genres
-- List all roles in a Horror or Sci-Fi movie with the first and last name of the actor who played
-- the role, as well as the movie name. Order the results by last name alphabetically, breaking ties
-- by first name alphabetically, further breaking ties by movie name alphabetically.
SELECT DISTINCT a.first_name, a.last_name, r.role, m.name
FROM roles r, actors a, movies m, genres g
WHERE r.movie_id = m.id
AND r.actor_i = a.id
AND (g.genre = 'Sci-Fi' OR g.genre = 'Horror')
ORDER BY last_name, first_name, m.name;