Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
 CREATE TABLE Cinema (
   ID     integer PRIMARY KEY NOT NULL,
   Title  varchar2(100) NOT NULL
 );
 CREATE TABLE Film (
   ID   integer PRIMARY KEY NOT NULL,
   Name varchar2(100) NOT NULL,
   Cinema_ID integer  
     CONSTRAINT fk_Cinema_ID REFERENCES Cinema(ID)
 );
  CREATE TABLE Performance (
   ID      integer PRIMARY KEY NOT NULL,
   ID_Film integer
        CONSTRAINT fk_Film_ID REFERENCES Film(ID),
   Takings integer
 );
 INSERT ALL 
       INTO Cinema(ID,Title) VALUES(1,'NiteHawk')
       INTO Cinema(ID,Title) VALUES(2,'Symphony Space')
       INTO Cinema(ID,Title) VALUES(3,'The Ziegfeld')
       INTO Cinema(ID,Title) VALUES(4,'Cinema Village')
     SELECT * FROM dual; 
4 rows affected

 INSERT ALL 
       INTO Film(ID,Name,Cinema_ID) VALUES(1,'Citizen Kane',1)
       INTO Film(ID,Name,Cinema_ID) VALUES(2,'Titanic',2)
       INTO Film(ID,Name,Cinema_ID) VALUES(3,'Brave Heart',4)
       INTO Film(ID,Name,Cinema_ID) VALUES(4,'Dumb and Dummer',3)
       INTO Film(ID,Name,Cinema_ID) VALUES(5,'How To Train Your Dragon',2)
       INTO Film(ID,Name,Cinema_ID) VALUES(6,'Beetle Juice',3)       
     SELECT * FROM dual;         
6 rows affected

 INSERT ALL 
       INTO Performance VALUES(1,1,15)
       INTO Performance VALUES(2,1,4)
       INTO Performance VALUES(3,2,10)
       INTO Performance VALUES(4,3,1)
       INTO Performance VALUES(5,4,5)
       INTO Performance VALUES(6,3,3)
       INTO Performance VALUES(7,2,7)	   
       INTO Performance VALUES(8,5,7)       
       INTO Performance VALUES(9,6,6)       
     SELECT * FROM dual;  
9 rows affected

SELECT Name, Sum_Takings 
  FROM
  (
   SELECT f.Name, sum(p.Takings) Sum_Takings,
          row_number() over (ORDER BY sum(p.Takings)) as rn
     FROM Film f 
     LEFT JOIN Cinema c ON f.Cinema_ID = c.ID
     LEFT JOIN Performance p ON f.ID = p.id_film
    GROUP BY f.Name
   )
  WHERE rn = 1
 


NAME
SUM_TAKINGS


Brave Heart
4