<<<  Challengers  >>>

Scenario Movies

See details on the model

In this scenario, we have three tables representing movies and the actors appearing in movies.

The table person contains the list of people who are actors or directors in movies; this table has 2 attributes:

  • id a unique identifier for this person
  • name the name of that person

The table movie that has 6 attributes:

  • id a unique identifier for the movie
  • title the name of the movie
  • yr the year the movie was released
  • director an integer (referring to the id of someone in the person table)
  • budget the budget of the movie (in dollars)
  • gross the amount of money generated by the movie (in dollars)

The table casting describes which actor playing in which movie and has 3 attributes:

  • actorid, an integer referring to a person (who played in the movie)
  • movieid, an integer referring to a movie (in which movie the actor played in)
  • ord, an integer that describes the importance of the character the actor plays in the movie (1 means main character, 2 is the second role, etc.).

Question

We say that two actors X and Y are challengers if X was the leading actor in a movie where Y appeared and Y was the leading actor in a movie where X appeared. Compute all pairs X, Y of challengers where the name of X comes before Y in alphabetical order.

Query


...
Run query first