/*
   The queries for Online Session 11's opening exercise.
*/

-- Produce a list of all actors in alphabetical order
-- along with the movies they star in.

SELECT actor, movie
FROM Actor NATURAL LEFT OUTER JOIN StarsIn
ORDER BY actor;

-- Produce a list of all directors in alphabetical order
-- along with the McDormand they've directed.

SELECT director, title
FROM Director
       NATURAL LEFT OUTER JOIN
     (SELECT director, title                      -- this subquery creates
      FROM Movie                                  -- something like StarsIn:
      WHERE title IN (SELECT movie FROM StarsIn   -- DirectsMcDormand
                      WHERE actor='McDormand'))
ORDER BY director;
