
-- Q1 Titles of movies made between 1980 and 1989 (inclusive)

SELECT Title
FROM nza.Movie
WHERE Year >= 1980 AND Year <= 1989;

-- note that natural join would not work because name in director and actor
-- are called the same which will result in join condition 
-- Director.Name = Actor.Name

-- Q2 Names of directors who made a movie between 1980 and 1989 (inclusive)

SELECT Name
FROM nza.Director, nza.Movie
WHERE nza.Movie.DIRECTOR_ID = nza.Director.DIRECTOR_ID AND 
      Year >= 1980 AND Year <= 1989;

-- Q3 The same query again, but using a join

SELECT Name
FROM nza.Director NATURAL JOIN nza.Movie
WHERE Year >= 1980 AND Year <= 1989;

-- Q4 The same query again, but using a subquery.

SELECT Name
FROM nza.Director
WHERE nza.Director.DIRECTOR_ID IN (SELECT DIRECTOR_ID
                                   FROM nza.Movie
                                   WHERE Year >= 1980 AND Year <= 1989);

-- Q5  Names of actors who acted in some movie directed by the director of 'Fame'.

-- director of Fame
-- SELECT nza.Movie.DIRECTOR_ID AS FAME_Director
-- FROM nza.Movie
-- WHERE nza.Movie.Title = 'Fame';

SELECT nza.Actor.Name
FROM nza.Actor, nza.Director, nza.Movie, nza.Part
WHERE nza.Director.Director_ID = nza.Movie.Director_ID AND
      nza.Movie.Movie_ID = nza.Part.Movie_ID AND
      nza.Part.Actor_ID = nza.Actor.Actor_ID AND
      nza.Movie.DIRECTOR_ID = (SELECT nza.Movie.DIRECTOR_ID 
                               FROM nza.Movie
                               WHERE nza.Movie.Title = 'Fame');


-- note that natural join would not work because name in director and actor
-- are called the same which will result in join condition 
-- Director.Name = Actor.Name










