Oracle sql - referencing tables -

my school task names movie database actors play in movies highest ratings made way , works :

select name,surname    actor   actorid in(   select actorid      actor_movie    movieid in (     select movieid       movie      ratingid in (       select ratingid         rating        percent_csfd = (         select max(percent_csfd)           rating       )     )   ) ); 

the output :

gary oldman sigourney weaver 

...but i'd add select mentioned movie , rating. accessible in inner selects don't know how join outer select in can work rows found in actor table. thank answers.

you need join tables properly. afterwards can add columns you´d select. final select looking this.

select ac.name, ac.surname, -- go on selecting different tables actor ac inner join actor_movie amo on amo.actorid = ac.actorid inner join movie mo on amo.movieid = mo.movieid inner join rating ra on ra.ratingid = mo.ratingid  ra.percent_csfd =    (select max(percent_csfd)    rating)