WITH SCORSESE AS(
  SELECT R.ACTOR_ID
  FROM ROLES R
  INNER JOIN MOVIES_DIRECTORS MD
      ON MD.MOVIE_ID = R.MOVIE_ID
  INNER JOIN DIRECTORS D ON D.ID = MD.DIRECTOR_ID
  WHERE D.LAST_NAME = 'Scorsese'
),

PAIR AS(
  SELECT DISTINCT B.ID AS BID, A.ACTOR_ID AS AID, M.RANK AS RANK
  FROM ACTORS B, SCORSESE A, MOVIES M, ROLES RB, ROLES RA
  WHERE RA.ACTOR_ID <> RB.ACTOR_ID
    AND RB.MOVIE_ID = RA.MOVIE_ID
    AND RB.ACTOR_ID = B.ID
    AND RA.ACTOR_ID = A.ACTOR_ID
    AND RA.MOVIE_ID = M.ID
)

SELECT DISTINCT A.FIRST_NAME, A.LAST_NAME,
B.FIRST_NAME, B.LAST_NAME
FROM ACTORS B, ACTORS A, PAIR P
WHERE B.id = P.BID
  AND A.id = P.AID
  GROUP BY A.FIRST_NAME, A.LAST_NAME,
  P.RANK, B.FIRST_NAME, B.LAST_NAME
  HAVING AVG(P.RANK) > 7.0;