Exercise 1.c solution notes

One possible solution:

SELECT P1.name AS name1, P2.name AS name2, title
FROM movies
JOIN credits AS C1 ON C1.position = 1
JOIN credits AS C2 ON C1.movie_id = C2.movie_id
JOIN people  AS P1 ON P1.id = C1.person_id
JOIN people  AS P2 ON P2.id = C2.person_id AND C2.position = 1
WHERE P2.id <> P1.id
      AND movies.id = C1.movie_id
      AND P1.name < P2.name
ORDER BY name1, name2, title ;

This query produces the output:

NAME1            NAME2                  TITLE
---------------  ---------------------  ----------------------
Owen, Clive (I)  Yacuzzi, Juan Gabriel  Children of Men (2006)

Common mistakes: Attempting this with only one instance of credits or people tables. Forgetting one of the clauses that constrain the result.